How to use GROUP_CONCAT to grab data for row ID?

Question

The query will retrieve IDs and all Attachment IDs as inline values like this

ID | Attachments 
26| 299,300,301,10922,11286,45927,45939
63| 299,300,301,10922,11286,45927,45939
73| 299,300,301,10922,11286,45927,45939
91| 299,300,301,10922,11286,45927,45939
103| 299,300,301,10922,11286,45927,45939

As you can see, the list for attachments repeats itself since I hardcoded it to fetch WHERE pl_posts.post_parent = 26. How can I change this hardcoded value so that it finds all attachment for that row post ID? If I replace that line to WHERE pl_posts.post_parent = pl_posts.ID I get NULL for everything so I am not sure if this is right?

SELECT 
SQL_CALC_FOUND_ROWS pl_posts.ID,
    (
        SELECT GROUP_CONCAT(pl_posts.ID)
        FROM pl_posts
        WHERE pl_posts.post_parent = 26
        AND pl_posts.post_type = 'attachment' 
        ORDER BY pl_posts.post_date DESC
    ) AS Attachments
FROM pl_posts 
GROUP BY pl_posts.ID
LIMIT 0, 25

Offtopic: Stop using `SQL_CALC_FOUND_ROWS ` it has become deprecated since MySQL 8.0.17 and will be removed from MySQL.. — Raymond Nijland, Aug 18 '19 at 19:40
Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Raymond Nijland, Aug 18 '19 at 19:41
If you are using the output of group_concat for anything other than providing a list to be read by a human being, then you probably should not be using group_concat. A list of surrogate ids is meaningless - it looks like you are trying to solve the problem in the wrong way. Voting to close. — symcbean, Aug 18 '19 at 20:54

score 1 · Accepted Answer · answered Aug 18 '19 at 20:48

1

Try aliasing the outer query's table to 'pl' so you can refer to it in the subquery's where clause:

SELECT pl.ID,
   (
        SELECT GROUP_CONCAT(pl_posts.ID)
        FROM pl_posts
        WHERE pl_posts.post_parent = pl.ID
        AND pl_posts.post_type = 'attachment' 
        ORDER BY pl_posts.post_date DESC
    ) AS Attachments
FROM pl_posts pl
LIMIT 0, 25;

This is not an aggregate query so don't you need the group by clause.

answered Aug 18 '19 at 20:48

James

392
4
9

1

Note that LIMIT without ORDER BY is rather meaningless – Strawberry Aug 18 '19 at 22:40

score 0 · Answer 2 · answered Aug 18 '19 at 19:47

0

You can write another select instead of the hardcoded value to select the field value with which you want to compare

answered Aug 18 '19 at 19:47

Suyash Krishna

241
2
8

How to use GROUP_CONCAT to grab data for row ID?

2 Answers2