MySQL Nested Selected with Multiple Columns

Question

I am currently trying to retrieve the latest posts along with their related posts (x number for each post). I have the following query in hand:

SELECT id, title, content
       (SELECT GROUP_CONCAT(title) FROM posts        -- Select title of related posts 
        WHERE id <> p.id AND id IN (                 
           SELECT p_id FROM tagsmap                  -- Select reletad post ids from tagsmap
           WHERE t_id IN (
              SELECT t_id FROM tagsmap               -- Select the tags of the current post 
              WHERE p_id = p.id) 
           ) ORDER BY id DESC LIMIT 0, 3) as related 
FROM posts as p ORDER BY id DESC LIMIT 5

My database structure is simple: A posts table. A tags table. And a tagsmap table where I associate posts with tags.

This query works fine (though I don't know its performance since I don't have many rows in the tables -- Maybe an explain could help me but that's not the case right now).

What I really need is to retrieve the ids of the related posts along with their titles.

So I'd like to do SELECT GROUP_CONCAT(title), GROUP_CONCAT(id), but I know that will result in an error. So what is the best way to retrieve the id along with the title in this case? I do not want to rewrite the whole subquery to just retrieve the id. There should be another way.

EDIT

SELECT p1.id, p1.title, p1.content,
    group_concat(DISTINCT p2.id) as 'P IDs',
    group_concat(DISTINCT p2.title) as 'P titles'
FROM posts as p1
LEFT JOIN tagsmap as tm1 on tm1.p_id = p1.id
LEFT JOIN tagsmap as tm2 on tm2.t_id = tm1.t_id and tm1.p_id <> tm2.p_id
LEFT JOIN posts as p2 on p2.id = tm2.p_id
GROUP BY p1.id
ORDER BY p1.id desc limit 5;

At the end this is the query that I've used. I removed the Where clause because it is unnecessary and used LEFT JOIN rather that JOIN because otherwise it would ignore the posts without tags. And finally added DISTINCT to group_concat because it was concatenating duplicate rows (If for example a post had multiple common tags with a related post it would result in a duplicate concatenation).

The query above works perfectly. Thanks for all.

Where you have WHERE post_id = p.id, did you mean WHERE p_id = p.id? I'm having to infer the structure of your tables, and so my guesses may be off. Also, you're not using table tags - right? — D Mac
– D Mac, Commented Mar 22, 2013 at 21:06

D Mac · Accepted Answer · 2013-03-22 21:31:28Z

Okay - this will work, and it has the added advantage of eliminating the sub queries (which can slow you down when you get lots of records):

SELECT p1.id, p1.title, p1.content,
    group_concat( p2.id) as 'P IDs',
    group_concat( p2.title) as 'P titles'
FROM posts as p1
JOIN tagsmap as tm1 on tm1.p_id = p1.id
JOIN tagsmap as tm2 on tm2.t_id = tm1.t_id and tm1.p_id <> tm2.p_id
JOIN posts as p2 on p2.id = tm2.p_id
WHERE p2.id <> p1.id
GROUP BY p1.id
ORDER BY p1.id desc limit 5;

What we're doing here is selecting what you want from the first version of posts, joining them to the tagsmap by their post.id, doing a self join to tagsmap by tag id to get all the related tags, and then joining back to another posts (p2) to get the posts that are pointed to by those related tags.

Use GROUP BY to discard the dups from all that joining, and you're there.

shibormot · Accepted Answer · 2013-03-22 21:08:58Z

2

like this?

SELECT id, title, content
       (SELECT GROUP_CONCAT(concat(cast(id as varchar(10)), ':', title)) FROM posts        -- Select title of related posts 
        WHERE id <> p.id AND id IN (                 
           SELECT p_id FROM tagsmap                  -- Select reletad post ids from tagsmap
           WHERE t_id IN (
              SELECT t_id FROM tagsmap               -- Select the tags of the current post 
              WHERE post_id = p.id) 
           ) ORDER BY id DESC LIMIT 0, 3) as related 
FROM posts as p ORDER BY id DESC LIMIT 5

answered Mar 22, 2013 at 21:08

shibormot

1,6382 gold badges12 silver badges24 bronze badges

Collectives™ on Stack Overflow

MySQL Nested Selected with Multiple Columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related