Symfony2 get and sort data from two different tables

Question

Can somebody help me, to get all data from two tables with different columns? for now it's work, but i want to sort all posts by field "created" order by DESC. Now i have sorting by id and then by "created" field. That's my repository method

public function getLatestPosts($limit = null)
{

    $queryTopics = $this->getEntityManager()
        ->getConnection()
        ->prepare('select * from topic order by  created  DESC');

    $queryTopics->execute();
    $topics = $queryTopics->fetchAll();

    $queryDiary = $this->getEntityManager()
        ->getConnection()
        ->prepare('select * from diary order by  created  DESC');

    $queryDiary->execute();
    $diaries = $queryDiary->fetchAll();

    $posts = array_merge($topics, $diaries);



    return $posts;
}

schema bd

TABLE  `topic` (
      `topic_id` 
      `topic_title`
      `created` 
    )

    TABLE `diary` (
  `id` 
  `title`
  `comment`
  `height` 
  `weight`
  `created`
)

Answer 1

Okay, I'll post an answer based on some assumptions, because I don't know the exact structure of your tables. You mentioned that the columns were different - this could mean one or all of the following:

• The column titles are different
• The column count is different; i.e: one table has more columns than the other

However, since you are merging these result sets, I assume you are treating them the same - for instance displaying them in a table. So let's assume that both tables either have the same column count, or you can manually SELECT a matching count of each table's columns.

In other words, if you can match the column count, the column names do not matter because you can do a UNION or a UNION ALL.

For example:

SELECT id, name, created FROM topics 
UNION 
SELECT id, title, created FROM diary

Note that the column titles do not have to match. If you want to apply ORDER BY on this result set just use a subquery, and order on that. For example:

SELECT posts.* FROM (
    SELECT id, name, created FROM topics 
    UNION 
    SELECT id, title, created FROM diary
) AS posts ORDER BY created DESC;

This should allow you to apply the ordering criteria you want, no need for array merging and sorting on the PHP level (which I assume will be a fair bit slower).

Hope this helps :)

EDIT

Since I posted this answer, you've edited your question to add some clarification. The number of columns in each table do not match.

In this case you have two options:

1. Explicitly SELECT a matching number of columns from each table (remember the names don't have to match, just the count)
2. 'Match' the column count by adding fake 'padding' columns to the SELECT query for the table with less columns.

An example of the second option, padding with NULL:

SELECT 
  topic_id, 
  topic_title, 
  NULL AS comment,
  NULL AS height,
  NULL AS weight,
  created 
FROM topic
UNION
SELECT 
  id, 
  title, 
  comment, 
  height, 
  weight, 
  created 
FROM diary

Admittedly, this way is sorta gnarly in my view but it should work for your use case.