TW Blog :: Archives for January 2009

2009 Oscar Nominations!

Relates to Movies

The nominations are out for the 2009 Oscars to be held on February 22nd and it looks like Britain is in with a hope - perhaps! Unless of course we see a LOTR whitewash of the gongs by the Curious Case of Benjamin Button which is up for 13 awards. Danny Boyle's Slumdog Millionaire is snapping at its heels with 10 nominations and both will face off for the coveted best picture along with Milk, The Reader and Frost/Nixon.

Looks like it is time to catch up on a bit of movie watching and see a few of these shortlisted films!

Great to see Heath Ledger's nomination for his riveting portrayal of the Joker in The Dark Knight. Finally watching the Bayle Batman sequel last weekend I couldn't help but be impressed by his portrayal of a character showing sociopathic tendencies glossed over with an unnerving frailty and desire to self-murder. Yet despite this gloom in the dark world of Batman, Ledger's Joker entertained moments of unexpected comic genius almost dragging the character straight off the pages of the comic book - the hospital scene a laugh out loud example!

See the full list of Oscar nominations for 2009.

Posted on Jan 22, 2009 at 14:47:26.

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 Jan 21, 2009 at 11:19:48.