[Dbix-class] Left join on multiple columns

[Matt S Trout]

On Thu, May 17, 2007 at 09:23:15AM -0400, Ronald J Kimball wrote:
> I have a query where I need to do a left outer join with multiple 
> columns in the ON clause, e.g.:
> 
> SELECT me.game_id, game_stats.game_plays as game_plays_yesterday
> FROM   game me
> LEFT JOIN game_stats game_stats ON
>           (me.game_id = game_stats.game_id AND
>            game_stats.activity_date = CURDATE() - 1)
> 
> This query selects the game_id and yesterday's game plays for all games. 
>  The activity_date condition must be in the ON clause to include games 
> that don't have stats for yesterday but do have stats for other days.

The from approach I've snipped from the quoted block is basically your best
WTDI for the moment.

Adding subselects and more flexible join conditions is one of the main
things I intend to be focused on for 09, but 08 has taken so long I don't
want to delay it further - plus figuring out how to get all our stuff into
SQL::Abstract without (a) breaking it for SQL::Abstract users, (b) breaking
it for older DBICs is going to be fun and games.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director    Want a managed development or deployment platform?
 Shadowcat Systems Ltd.  Contact mst (at) shadowcatsystems.co.uk for a quote
http://chainsawblues.vox.com/             http://www.shadowcatsystems.co.uk/