[thelist] Multiple columns instead of rows w/ LEFT JOIN

Max Schwanekamp lists at neptunewebworks.com
Sat Jan 14 14:17:35 CST 2006


Matt Warden wrote:
> Well, it depends. If every color can come in every size, then there is
> no need to get all size-color combinations. However, if that rule does
> not hold, then you have to do it like you are suggesting, because you
> must maintain a relationship between sizes and colors. (Although, you
> could always allow the user to select all sizes and all colors, and then
> check later whether that combo is available; but that can be frustrating
> for the user.)

Thanks for the reply Matt.

In most cases, there is no set number.  To expand my example, red 
widgets might only be available in size small, blue widgets in 
small-med-large, etc -- a common scenario on retail sites.  My actual 
code is slightly more abstract, where I have educational courses which 
are assigned to one or more categories, are delivered via one or more 
media, have zero or more author names etc.

Rudy L. (r397.com) replied offlist with my solution (btw he says "matt 
warden is doing an absolutely fantastic job handling all the database 
questions" :).  Turns out MySQL and PostgreSQL have an aggregating 
function I was unaware of, GROUP_CONCAT()[0,1] which allows 
concatenating those multiple left-join matches into a comma-delimited 
string, which can then be exploded into an array in PHP.  This allows me 
to use a single query to retrieve the data, and simplify the PHP code as 
well.

Of course now MySQL is working harder to aggregate those rows. But in 
combination with caching, GROUP_CONCAT() is a godsend!  The biggest 
caveat is that it's specific to MySQL 4.1+ and PostgreSQL.  In my case 
that's acceptable.

[0] http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
[1] http://db4free.blogspot.com/2006/01/hail-to-groupconcat.html

-- 
Max Schwanekamp
http://www.neptunewebworks.com/



More information about the thelist mailing list