Using GROUP_CONCAT on subquery in MySQL

Question

Hello,

I have a MySQL query in which I want to include a list of ID's from another table. On the website, people are able to add certain items, and people can then add those items to their favourites. I basically want to get the list of ID's of people who have favourited that item (this is a bit simplified, but this is what it boils down to).

Basically, I do something like this:

SELECT *, GROUP_CONCAT((SELECT userid FROM favourites WHERE itemid = items.id) SEPARATOR ',') as idlist FROM items WHERE id = $someid

This way, I would be able to show who favourited some item, by splitting the idlist later on to an array in PHP further on in my code, however I am getting the following MySQL error:

1242 - Subquery returns more than 1 row

I thought that was kind of the point of using GROUP_CONCAT instead of, for example, CONCAT? Am I going about this the wrong way?

Answer 1

You can't access variables in the outer scope in such queries (can't use items.id there). You should rather try something like

SELECT items.name, items.color, CONCAT(favourites.userid) as idlist FROM items INNER JOIN favourites ON items.id = favourites.itemid WHERE items.id = $someid GROUP BY items.name, items.color;

Expand the list of fields as needed (name, color...).

Answer 2

The purpose of GROUP_CONCAT is correct but the subquery is unnecessary and causing the problem. Try this instead:

SELECT ITEMS.id,GROUP_CONCAT(FAVOURITES.UserId)
FROM FAVOURITES INNER JOIN ITEMS ON ITEMS.Id = FAVOURITES.ItemId
WHERE ITEMS.Id = $someid
GROUP BY ITEMS.ID

Answer 3

Ok, thanks for the answers so far, that seems to work. However, there is a catch. Items are also considered to be a favourite if it was added by that user. So I would need an additional check to check if creator = userid. Can someone help me come up with a smart (and hopefully efficient) way to do this?

Thank you!

Edit: I just tried to do this:

SELECT [...] LEFT JOIN favourites ON (userid = itemid OR creator = userid)

And idlist is empty. Note that if I use INNER JOIN instead of LEFT JOIN I get an empty result. Even though I am sure there are rows that meet the ON requirement.

Answer 4

I think you may have the "userid = itemid" wrong, shouldn't it be like this:

SELECT ITEMS.id,GROUP_CONCAT(FAVOURITES.UserId) AS IdList
FROM FAVOURITES 
INNER JOIN ITEMS ON (ITEMS.Id = FAVOURITES.ItemId OR FAVOURITES.UserId = ITEMS.Creator)
WHERE ITEMS.Id = $someid
GROUP BY ITEMS.ID