[Dbix-class] Left join on multiple columns

[John Goulah]

This leads to the question of whether you can write straight SQL in times
like these.  I suppose it breaks the design, but sometimes its just easier
to write SQL... Is it possible?

On 5/17/07, Ronald J Kimball <rkimball+dbixclass at pangeamedia.com> 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 =3D game_stats.game_id AND
>             game_stats.activity_date =3D 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 =3D> [
>               { me =3D> 'game' },
>               [ { game_stats =3D> 'game_stats',
>                   -join_type =3D> 'left' },
>                 { 'game_stats.game_id' =3D> 'me.game_id',
>                   'game_stats.activity_date' =3D>
>                   $schema->storage->dbh->quote($yesterday) },
>               ],
>      ],
>      '+select' =3D> [
>                     'game_stats.game_plays',
>                   ],
>      '+as'     =3D> [
>                     '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/dbix-class@lists.rawmode.org/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070517/6f6=
06ffd/attachment-0001.htm