Boggled by GROUP_CONCAT in query...

[Antivirus]

Working on a query and having a difficult time with this. If anyone can help i'll be very happy

My data:
For Table things...
=================
| thingid | stringcodeids |
=================
| 1 | 1 |
| 2 | 1,3,4 |
| 3 | |
| 4 | 2 |
=================

For Table stuff...
=================
| stuffid | code |
=================
| 1 | AAA |
| 2 | BBB |
| 3 | CCC |
| 4 | DDD |
| 5 | EEE |
| 6 | FFF |
=================


My query:

PHP Code:

$sql = $db->query_read("
     SELECT things*, GROUP_CONCAT(stuff.code ORDER BY code SEPARATOR ', ') AS thingcodes
     FROM things
     LEFT JOIN stuff ON stuff.stuffid IN ( things.stringcodeids )
     WHERE things.stringcodeids = 2
     GROUP BY things.thingid
"); 


Problem is that I can't get a concatenated list of related fields stuff.code based upon the string of code ids within things.stringcodeids

What I want specifically is for alias thingcodes to return 'AAA, CCC, DDD', however thingcodes is returning as empty

Thanks!

[Eikinskjaldi]

whilst you could probably get this to work if you split the stringcodeids, this is a horribly inefficient query which will bog down as soon as you get a decent number of rows in either table, since the primary join will require an ALL join (it has to look in every row to see if its ID is present)


You should restructure table things so that it has 1 row per association. Either add an increment as a primary key or make both columns dual primary key

For Table things...
=================
| thingid | stuffid |
=================
| 1 | 1 |
| 2 | 1 |
| 2 | 3 |
| 2 | 4 |
| 4 | 2 |
=================

and the sql
[sql]
SELECT things.*, GROUP_CONCAT(stuff.code ORDER BY code SEPARATOR ', ') AS thingcodes
FROM things
LEFT JOIN stuff USING (stuffid)
WHERE things.stuffid = 2
GROUP BY things.thingid
[/sql]

note your where clause makes the group_concat irrelevant, as you are only selecting 1 row from that column.

[Antivirus]

eikin,
Thanks for the help. I can get it to work your way, but I really wanted to avoid using an additional table for the joins. I was however able to get the query to work using the following:

[SQL]
SELECT things.*, GROUP_CONCAT(stuff.code ORDER BY stuff.code SEPARATOR ', ') AS thingcodes
FROM things
LEFT JOIN stuff ON FIND_IN_SET(stuff.stuffid, things.stringcodeids) > 0
WHERE things.thingid = 2
GROUP BY things.thingid
[/SQL]

Is this really that much more inefficient than using an additional table to handle the stringcodeids -> stuffcode relationship?
Thanks again

[Eikinskjaldi]

Firstly, I wasn't suggesting an extra table, but a replacement table.

Secondly, "FIND_IN_SET"...nice, I didn't know that existed.

Anyway, my guess is yes, the find in set method will be horribly inefficient. Run it through explain, see what the join looks like.

[Antivirus]

well, i see by using explain that it takes almost twice as long to do the query with the FIND_IN_SET then with the way you suggested. I can imagine that as the db grows it will certainly take longer. I really don't want to have to re-write all the different queries that access this however, is there any other way such as creating a special type of index for the JOIN using the things.stringcodeids column? I see different types, but have to say that the mysql manual doesn't really give many examples of when to use which types of indexes and why. t's somewhat confusing...

[Adrian Schneider]

Relationship tables are the way to go.

[Eikinskjaldi]

No AV, there isnt. You are scanning for a subset of the key, there is no way to index that.


I understand that you dont want to re structure your code, but my way is the high way (as opposed to the goat track you wandered down .

A bit of pain now versus a lot of pain in the future.

[Antivirus]

damn, but i guess you're right. Wondering then, for instance why jelsoft has coded membergroupids in this manner such as 11,13,22,35 etc...

[Eikinskjaldi]

Originally Posted by Antivirus

damn, but i guess you're right. Wondering then, for instance why jelsoft has coded membergroupids in this manner such as 11,13,22,35 etc...

Swings and roundabouts, I guess. The only time VB needs to do a find in set is when it is showing all the users for a particular usergroup. In all other instances you already have a user and just want to know if they are a member of an extra user group. I guess the makers decided a bit of inefficiency was worth not having an extra table.

Personally, I dont agree with their decision.