CODEWITHSUNDEEP
phpmysqldatabasejoinsubquery
Matisse VerDuyn
Matisse VerDuyn

Reputation: 1138

MySQL JOIN + SUBQUERY

With the following query:

SELECT p.*,  i.*, GROUP_CONCAT(DISTINCT(pc.category_id)) AS post_categories, GROUP_CONCAT(DISTINCT(pt.tag_id)) AS post_tags 
FROM posts AS p 
LEFT JOIN images AS i ON p.post_image = i.image_id 
LEFT JOIN post_categories AS pc ON p.post_id = pc.post_id 
LEFT JOIN post_tags AS pt ON p.post_id = pt.post_id 
WHERE p.post_url="'.$id.'"

I'm returning a list of blog posts, retrieving:

  1. every column from the posts table
  2. every column from the images table (for the post image)
  3. a concatenation of all category_id's (for the post)
  4. a concatenation of all tag_id's (for the post)

What this leaves me with, are two fields that aren't really helpful:

I would really like to do a subquery within both of the LEFT JOINS that allows me to, instead, return a list of:

for each post.

I'm stuck right now on the subquery for both categories, and tags.

The table structures:

posts

post_id, post_title, etc.

categories

category_id, category_name

tags

tag_id, tag_name

post_categories

post_id, category_id

post_tags

post_id, tag_id

The question: is it possible to do such a thing effectively, if so, how?

If not, should I just store SELECT * FROM categories in a $categories array, then compare each category_id in the $categories array to each post's exploded category_id string in the $posts array each time?

Upvotes: 1

Views: 555

Answers (2)

McGarnagle
McGarnagle

Reputation: 102793

You should be able to join to the tags and categories table, then just replace your concatenation with the name fields. (Also have to add Distinct to the select so as to not return duplicated main rows.)

SELECT DISTINCT p.*,  i.*, 
    GROUP_CONCAT(DISTINCT(c.category_name)) AS post_categories,
    GROUP_CONCAT(DISTINCT(t.tag_name)) AS post_tags 
FROM posts AS p 
    LEFT JOIN images AS i ON p.post_image = i.image_id 
    LEFT JOIN post_categories AS pc ON p.post_id = pc.post_id 
    LEFT JOIN post_tags AS pt ON p.post_id = pt.post_id 
    LEFT JOIN categories AS c ON pc.category_id = c.category_id
    LEFT JOIN tags AS t ON pt.tag_id = t.tag_id
WHERE p.post_url="'.$_GET['id'].'"

Upvotes: 1

DRapp
DRapp

Reputation: 48179

Then why not just grab the respective category name and tags description instead of their IDs in your GROUP_CONCAT

Upvotes: 1

Related Questions