Page 1 of 1

Displaying multiple categories for multiple posts

Posted: Sun May 20, 2007 2:11 pm
by GeertDD
Currently working on a basic blog system. I want to show a list of posts like this:

- 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 
I've been experimenting with all kinds of joins, but SQL is one of my weak points. I've also tried to dig into some wordpress code. However, until now I have not found a way to tackle this situation in an efficient way.


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.

Posted: Wed May 23, 2007 7:06 am
by mentor
Take a look at GROUP_CONCAT().

Posted: Wed May 23, 2007 12:00 pm
by GeertDD
Alright, thanks mentor! This is definitely a step forward. After playing with the SQL a bit, I came up with a query that seems to work fine:

Code: Select all

SELECT
    p.id, p.title, p.text, p.author, p.date,
    COUNT(DISTINCT c.id) comment_count,
    GROUP_CONCAT(DISTINCT cat.id) categories

FROM posts p
    LEFT JOIN comments c ON c.post_id = p.id
    LEFT JOIN posts_categories p_cat ON p_cat.post_id = p.id
    LEFT JOIN categories cat ON cat.id = p_cat.category_id

GROUP BY p.id
The 'categories' data then returns a string with all the category IDs the post was posted in. Add one more query to select all the categories and then it is only a matter of looping and adding the relevant category data to the result.

Posted: Thu May 24, 2007 1:19 am
by mentor
What about using this if you don't have duplicate categories name and don't need category id.

Code: Select all

GROUP_CONCAT(DISTINCT cat.title) categories

Posted: Fri May 25, 2007 4:57 pm
by GeertDD
That would be an option as well. However, I need at least the category title and slug in order to create links.