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/[email protected]/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
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
