I have 2 tables named collections and posts. I need to display the collection name and 3 posts from each collection collected by users. Some of the collection has less than 3 posts and some have no posts at all. Also I need to count the posts (Not total number of posts but the posts produce by the query)
MySQL Tables
Collections
| collection_id | collection_name | uid |
| 1 | My collection 01 | 1 |
| 2 | My collection 02 | 1 |
| 3 | My collection 03 | 1 |
| 4 | My collection 04 | 2 |
| 5 | My collection 05 | 2 |
| 6 | My collection 06 | 1 |
Posts
| posts_id | post_title | cid |
| 1 | post title 1 | 1 |
| 2 | post title 2 | 1 |
| 3 | post title 3 | 1 |
| 4 | post title 4 | 3 |
| 5 | post title 5 | 2 |
| 6 | post title 6 | 3 |
cid is the collection id. So what I want to and uid is the user id. I want the results to be display
3 posts from My collection 01
post title 1
post title 2
post title 3
1 posts from My collection 02
post title 5
2 posts from My collection 02
post title 4
post title 6
Just made the example according to the dummy data I added in the table above.
I tried with left join with no luck
SELECT * FROM collections LEFT JOIN posts ON posts.cid= collections. collection_id WHERE posts ON posts.cid= collections. collection_id AND collections. uid=1 ORDER BY collections. collection_id DESC LIMIT 0, 16
With this query I can get the collection name and 1 post.
But if i run two queries it will work (1 inside the other)
SELECT * FROM collections WHERE uid=1 ORDER BY collection_id DESC LIMIT 0, 16
Then I get the collection id and run another query inside while loop of above query
SELECT * FROM posts WHERE cid=$collection_id ORDER BY post_id DESC LIMIT 0, 3
I really love to do it with a single query. Your help is greatly appreciated.
There is no easy way to do that. Maybe with very complex query, but it will be difficult to maintain and may be even less efficient than doing that with several simpler queries.
The solution described by you costs 1 + (number of categories) queries, not two, of course. You could union them easily, and then you would have two queries and less trips do database, but similar load for db (comparing to your solution).
Even if you would assume, that there is a way to fetch everything with single query, then db has to do almost the same work (fetch 3 newest posts from every category). So having 2 queries vs 1 hypothetical is not a big penalty in terms of performance. Moreover, I can imagine that DB engine could have some issues with finding most optimal execution plan, especially if you would add there functions etc.
And the last solution. There is a way for fetching up to 3 posts from each category, but that require modifying schema and some application-side work. You can add a boolean column "most_recent" and have always 3 posts per cat. with true and false for the rest. You would have to keep updating it every time when you are adding/deleting posts. That is achievable as well with db triggers. Then your problem is trivial to resolve, but only because you have done some precomputation.