- Post 1: title + author + date + comment_count + categories: apple, orange, melon
- Post 2: title + author + date + comment_count + categories: orange, mango
- Etc.
The only way I manage to do this is by doing separate queries for each post that look for the categories. So this is what I end up with 6 queries just to show 5 posts:
Code: Select all
SELECT p.id, p.title, p.text, p.author, p.date, COUNT(c.id) comment_count
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id
LIMIT 0, 5
SELECT cat.id, cat.title FROM categories cat JOIN posts_categories p_cat ON p_cat.post_id = 27 WHERE p_cat.category_id = cat.id
SELECT cat.id, cat.title FROM categories cat JOIN posts_categories p_cat ON p_cat.post_id = 13 WHERE p_cat.category_id = cat.id
SELECT cat.id, cat.title FROM categories cat JOIN posts_categories p_cat ON p_cat.post_id = 14 WHERE p_cat.category_id = cat.id
SELECT cat.id, cat.title FROM categories cat JOIN posts_categories p_cat ON p_cat.post_id = 15 WHERE p_cat.category_id = cat.id
SELECT cat.id, cat.title FROM categories cat JOIN posts_categories p_cat ON p_cat.post_id = 16 WHERE p_cat.category_id = cat.id feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
@feyd: hadn't noticed there were specific [syntax="sql"] tags. Thanks.