12

I am working on an open source project called PHP-Bouncer, and I'm having issues with a MySQL Query I am writing for it. Basically we have three tables: BouncerRoles, PageInRole, and BouncerPageOverrides. BouncerRoles contains access levels, and the other two tables link back to BouncerRoles via Foreign Key and provide multiple entries of additional data. I have written the following query to attempt to pull all of the role data I need all at once:

select BouncerRoles.RoleID, BouncerRoles.RoleName, 
GROUP_CONCAT(PageInRole.PageName separator '|') as ProvidedPages, 
GROUP_CONCAT(CONCAT(BouncerPageOverrides.OverriddenPage,'&',BouncerPageOverrides.OverridingPage) separator '|') as OverriddenPages 
from BouncerRoles join PageInRole on BouncerRoles.RoleID = PageInRole.RoleID 
join BouncerPageOverrides on BouncerRoles.RoleID = BouncerPageOverrides.RoleID
group by BouncerRoles.RoleID;

The Goal of this query is to provide the RoleID, RoleName, a pipe delimited list of provided pages, and a pipe delimited list of overrides (in the form of overriddenpage&overridingpage). Everything works except the last column of the query, which repeats the entries it finds over and over like this (output in CSV format):

RoleID,RoleName,ProvidedPages,OverriddenPages
2,Exchange,exchange-how.php|exchange-support.php|exchange.php|premium-promo.php|exchange-resorts.php|premiumplus-promo.php|exchange-deposit.php|exchange-requestdestination.php,whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php|whyexchange.php&exhange.php
3,Premium,premiumplus-promo.php|premium-cruises.php|premium-resorts.php|premium-condohome.php|premium-hotelaircar.php|premium.php|premium-restaurants.php|premium-overview.php,premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php|premium-promo.php&premium.php
4,"Premium Plus",premiumplus-exclusiveescapes.php|premiumplus.php|premiumplus-overview.php|premiumplus-concierge.php|premiumplus-airportlounge.php,premiumplus-promo.php&premiumplus.php|premiumplus-promo.php&premiumplus.php|premiumplus-promo.php&premiumplus.php|premiumplus-promo.php&premiumplus.php|premiumplus-promo.php&premiumplus.php

Is there something I've done wrong in my query to cause this?

Brendon Dugan
  • 2,138
  • 7
  • 31
  • 65
  • 1
    Mind that `GROUP_CONCAT` can be a pain in the arse if you have a big result -> it will only return a limited size result (I think 1024 bytes, but am not sure), so if your resultset is bigger it will get cut off. – Nanne Jul 14 '12 at 18:27
  • 3
    Are you missing a `DISTINCT` inside your `GROUP_CONCAT`? It seems like your join is returning multiple rows. – lc. Jul 14 '12 at 18:28
  • 1
    @nanne: That limit (1024) depends on a setting. It can be lengthened. – ypercubeᵀᴹ Jul 14 '12 at 18:34
  • 1
    True, but you still need to be aware if it existing :). Also, but that's just my opinion, it might be tricky to ask your users to change their settings.... – Nanne Jul 14 '12 at 18:42
  • I previously did yet received no answers :( http://dba.stackexchange.com/questions/40266/how-can-i-join-one-to-one-to-many-to-one-without-using-group-concat – Thomas Clowes Apr 22 '13 at 12:13
  • @ypercube how to change settings to extend the limit in android sqlite? – Muhammad Babar Feb 10 '15 at 16:30

1 Answers1

32

You are probably joining a table with two tables on 1..n relationships, producing duplicate results.

  • Use either GROUP_CONCAT( DISTINCT ...) or

  • Use two subqueries: in each one use GROUP_CONCAT() with group by on each of the 2 tables. Then join the two subqueries and the main table.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • Adding Distinct to the second group_concat did the trick... Thanks! – Brendon Dugan Jul 14 '12 at 20:25
  • 1
    Is there no other option? As with a large dataset (in my case) the subqueries are extremely slow yet I cannot use distinct as i need each value returned even if it is the same.. – Thomas Clowes Apr 22 '13 at 10:47
  • @ThomasClowes You can post a question then. Don't forget to include the actual query, the tables' definitions (including indexes) and the execution plan. You could also post the question at [http://dba.stackexchange.com/](http://dba.stackexchange.com/) where it may get better attention. – ypercubeᵀᴹ Apr 22 '13 at 10:52
  • I previously did but no-one could help :( http://dba.stackexchange.com/questions/40266/how-can-i-join-one-to-one-to-many-to-one-without-using-group-concat – Thomas Clowes Apr 22 '13 at 12:14
  • @ThomasClowes I don't see a query there. It's hard to understand what exactly you want. Acn you add it? – ypercubeᵀᴹ Apr 22 '13 at 12:32