2

I'm refactoring some code, and looking at ways to try and improve both readability and performance.

One item that bugs me is situations where I need a join statement with multiple objects on one side. For example...

Foo Schema         Bar 2 Schema
--------------     ---------------
id                 id
data               fooId
                   data

Result from Search:
---------------------
id   barId    fooData
1    1        ...
1    2        ...
1    3        ...
2    4        ...
3    5        ...

My end result, when querying for object Foo, needs to be an object Foo containing the id's (or objects fetched based on the id's) that are related.

Currently, I wind up having to condense multiple rows at the PHP level, adding bar id's to Foo until the foo id changes. It's a bit ugly, but it does work. What'd I'd like to reduce my result set to would be:

Result from Search:
---------------------
id   barIds   fooData
1    [1,2,3]  ...
2    4        ...
3    5        ...

Is there any way to do this at the SQL level? (As a note, I'm not looking for the literal string 1, 2, 3, I want an array consisting of the id's 1, 2, and 3 -- but if I have to take a string and then transform, I can do)

As an aside, my intent is to combine this with the PDO::fetch_class to let me instantiate the class in a single line instead of spending time writing many lines of cookie-cutter code to load the properties of the class.

RonLugge
  • 5,086
  • 5
  • 33
  • 61

1 Answers1

4

Sounds like you're looking into using GROUP_CONCAT. This will combine all the bar ids together. Something like this:

SELECT F.Id, GROUP_CONCAT(B.Id) BarIds, F.data
FROM Foo F 
   INNER JOIN Bar B ON F.Id = B.FooId
GROUP BY F.Id

If you'd like to get your exact format, try using CONCAT:

CONCAT('[',GROUP_CONCAT(B.Id),']') BarIds

Here's a SQL Fiddle Demo.

--EDIT--

If there is a concern over the length of characters stored by default with GROUP_CONCAT (check this link out), another alternative approach would be to mimic the behavior of GROUP_CONCAT by doing something like this:

SELECT Id, BarIds, Data
FROM (
    SELECT F.Id,  
      MAX(@barIdsCombined:=IF(@prevFoodId=F.Id,
                          CONCAT(@barIdsCombined,',',B.Id),
                         B.Id)) BarIds, 
      F.data,
      @prevFoodId:=F.Id
    FROM Foo F 
       INNER JOIN Bar B ON F.Id = B.FooId
        JOIN (SELECT @barIdsCombined:='') t
    GROUP BY F.Id
  ) t
Community
  • 1
  • 1
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • Beware of using `GROUP_CONCAT` because it has a very low artificial limit. You can update it (`group_concat_max_len`). I have gone down the road of using MySQL for aggregation including `GROUP_CONCAT`. Experience tells me that it's generally much better to aggregate with the querying language as MySQL has very limited powers – Explosion Pills Feb 24 '13 at 02:26
  • I only used the [ and ] to indicate that it was probably going to hit PHP as an array, which -- IIRC -- is how PDO handles the SQL set datatype. – RonLugge Feb 24 '13 at 03:36
  • @ExplosionPills By 'aggregate with the querying language', I assume you mean PHP? Is it really that great an idea -- it seems like this would be a great place for SQL to be able to work some magic to reduce overhead with background query optimization and caching. – RonLugge Feb 24 '13 at 03:37
  • @RonLugge -- imho, unless you have to worry about having a long set of data returned (usually varchar based), group_concat is the way to go. I included another option which mimics that behavior, but I rarely run into an issue with group_concat. With that said, if you are using it on varchar fields with lots of text, then you could run into an issue if you don't up the max_len. Best of luck! – sgeddes Feb 24 '13 at 13:55