Most recent items from two tables

[James_43]

Hi all,

I'm using the Laravel framework to create an application, and have a database question.

I have a table called posts. I have been pulling the 10 most recent rows from here and making use of an offset variable for pagaination.

Now however I also have a table media that has a completely different table structure. What I want to be able to do it to pull the 10 most recent posts and media rows (10 in total, not 10 of each).

I can't merge two LIMIT 5 queries together, because it is possible that the first media row is older than the 6th posts row, and in that case it should take precedence and there would only be 4 items from the media table.

Does anyone know of anything I can try here>

[pritaeas]

Pull the 10 most recent of both tables (id, date and table), then order by date and get the 10 most recent ones, then do a left join on the result to get the rest of the data.

[James_43]

Yes, but what happens when I want to view "page 2" of this query? Will having an offset of 10 on the original queries be enough here?

[pritaeas]

No. you need the offset on the intermediate query (union of the two with the dates and id's).

[James_43]

I'm confused here. Queries are not my strength...

So I do two queries, each getting 10 items from each table. Then I sort that table by date, giving me the most recent events. I can follow this much. Doing a left join will give me the rest of the data I want, no problem.

However, when it comes to offsetting the intermediate query, not sure what you mean. Effectively, I have an array of ids from different tables:

[] => 'item',
[] => 'item',
[] => 'item',
[] => 'item',
...

I can do a left join to get the data I need, but I cannot offset that query to get the 10 next recent queries. The only way I can think of is to find out how many items I took from table 1 and how many from table 2, and then passing that as the offset for the next "page two" query.

That would work, but really messy and means I would have to pass more than just the page number to a function. But are you saying there is an easier method?