[Dbix-class] Left join on multiple columns

Ronald J Kimball rkimball+dbixclass at pangeamedia.com
Thu May 17 14:23:15 GMT 2007


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



More information about the Dbix-class mailing list