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`
)
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:
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:
SELECT
a matching number of columns from each table (remember the names don't have to match, just the count)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.