[Dbix-class] Left join on multiple columns

[Ronald J Kimball]

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.


I found this thread in the archives:
http://www.mail-archive.com/dbix-class@lists.rawmode.org/msg02304.html
It mentions using the from attribute (and also that placeholders aren't 
supported there).  I came up with this:

$schema->resultset('Game')->search(
   undef,
   { from => [
              { me => 'game' },
              [ { game_stats => 'game_stats',
                  -join_type => 'left' },
                { 'game_stats.game_id' => 'me.game_id',
                  'game_stats.activity_date' =>
                  $schema->storage->dbh->quote($yesterday) },
              ],
     ],
     '+select' => [
                    'game_stats.game_plays',
                  ],
     '+as'     => [
                    'game_plays_yesterday',
                  ],
   },
);


I'm curious if there's another way to do this in DBIx::Class, 
specifically without using the from attribute and having to specify all 
the relationships myself.  (The actual query I'm working involves five 
tables, rather than two.)


thanks,
Ronald