1

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`
)
user2531518
  • 39
  • 1
  • 2
  • 6
  • Do both tables have the same number of columns, or can you select an equal subset of columns from each table? I'm thinking you can do a `UNION`/`UNION ALL` on your two tables. If you use that result set as a subquery then you can apply `ORDER BY` on it, and you don't have to do any stitching together or ordering of your result set in PHP at all. – Darragh Enright Mar 23 '14 at 12:36
  • If you want to update your answer to provide a list of the columns in each table I should be able to post a more concrete answer. – Darragh Enright Mar 23 '14 at 12:39
  • Hi. Thanks for the update. I just posted an answer that makes some assumptions. I can see now that your column count is different - I can update my answer if you can clarify this question: table `diary` has more columns than `topic` so, do you want to select all columns from `diary`? – Darragh Enright Mar 23 '14 at 12:52

2 Answers2

0

I think that most easier way to do it is to use array sorting functions in php, it should also be the most fastest way (server time) to do it

Tomáš Tibenský
  • 811
  • 11
  • 20
0

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.

Community
  • 1
  • 1
Darragh Enright
  • 13,676
  • 7
  • 41
  • 48