Displaying multiple categories for multiple posts

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
GeertDD
Forum Contributor
Posts: 274
Joined: Sun Oct 22, 2006 1:47 am
Location: Belgium

Displaying multiple categories for multiple posts

Post 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.
mentor
Forum Contributor
Posts: 100
Joined: Sun Mar 11, 2007 11:10 am
Location: Pakistan

Post by mentor »

Take a look at GROUP_CONCAT().
User avatar
GeertDD
Forum Contributor
Posts: 274
Joined: Sun Oct 22, 2006 1:47 am
Location: Belgium

Post 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.
mentor
Forum Contributor
Posts: 100
Joined: Sun Mar 11, 2007 11:10 am
Location: Pakistan

Post 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
User avatar
GeertDD
Forum Contributor
Posts: 274
Joined: Sun Oct 22, 2006 1:47 am
Location: Belgium

Post by GeertDD »

That would be an option as well. However, I need at least the category title and slug in order to create links.
Post Reply