TW Blog :: Archives for January 2009

String Concatenation in MySQL Aggregate Queries

Relates to MySQL

For a minute I was thinking about building a stored procedure to handle this until I had a quick read up on the aggregate functions in the MySQL manual!

In brief I wanted to get a list of names from a one-to-many relationship - in this case multiple owners for a single property. These needed to be displayed as a single record in a form field and ideally the information should be drawn from the database in the same query as all the other information for the property. When dealing with numeric data, summation or unit count of the many side of a one-to-many relationship is quite simple using the aggregation functions (eg SUM() and COUNT()). As it turns out a similar function is available for strings (as of MySQL 4.1) and this is GROUP_CONCAT().

So for example I have:

====== data_properties ======
prop_id     10
prop_name   "This House"
=============================

======= data_owners =========
owner_id     12
owner_name   "Joe Blogs"
=============================
owner_id     13
owner_id     "Phil Blogs"
=============================

= data_properties_to_owners =
prop_id      10
owner_id     12
=============================
prop_id      10
owner_id     13
=============================

And I want to get a result set along the following lines:

Object {
  prop_id => 10
  prop_name => "The House"
  owners => "Joe Blogs, Phil Blogs"
}

In a single query we can do this using the following DML query:

SELECT 
  a.prop_id, a.prop_name, 
  GROUP_CONCAT(
    DISTINCT b.owner_name 
    ORDER BY ASC
    SEPARATOR ", "
  ) as owners
FROM 
  data_properties as a, 
  data_owners as b,
  data_properties_to_owners as c
WHERE 
  a.prop_id = c.prop_id and c.owner_id = b.owner_id
GROUP BY a.prop_id

So this query would take the following dataset:

=== prop_id ==  prop_name  ==  owner_name   ===
===    10   == "The House" ==  "Joe Blogs"  ===
===    10   == "The House" ==  "Phil Blogs" ===

And groups it on the last field to produce the required resultset. This reduces the demands on the PHP API to manipulate the data from the resultset prior to delivery to the templates.

GROUP_CONCAT() function has several parameters which can help further manipulate the resultset to get exactly the data required. See the aggregate functions in the MySQL manual for more information.

Posted on Wednesday, Jan 21, 2009 at 11:19:48.