On 2014-02-21 10:16, Carl Franks wrote: > Hi, > I can't figure out the DBIx::Class syntax for a JOIN ON x AND y statement. > > I have a table: > > query > ===== > id > title > > with a has_many() relationship to the table: > > response_comment > ================ > id > query_id > alert > posted > > I want to retrieve all rows from `query`, and a count of the related > `response_comment` rows where status = 'posted' and alert = 1 > The traditional SQL for this would be: > > SELECT me.id, me.title, count(response_comment.id) > FROM `query` me > LEFT JOIN `response_comment` > ON me.id = response_comment.query_id > AND response_comment.alert = 1 > AND response_comment.status = 'posted' > GROUP BY me.id, me.title; > > This returns results such as: > > id | title | count(response_comment.id) > --------------------------------------- > 1 | one | 0 > 2 | two | 1 > 3 | three | 0 > > So far I have: > my $rs = $schema->resultset('Query')->search( > undef, > { > join => ['response_comments'], > distinct => 1, > '+select' => [ { count => 'response_comments.id' } ], > '+as' => ['alerts'], > }, > ); > Which gives me the count of the related response_comments - but I > can't figure out how to define the 'AND' clauses. > Any help? Just restrict the resultset: ->search({ 'response_comments.alert' => 1, 'response_comments.status => 'posted', }, { ... });
> Cheers, > Carl > > _______________________________________________ > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class > IRC: irc.perl.org#dbix-class > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ > Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"* T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien Handelsgericht Wien, FN 79340b *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"* Notice: This e-mail contains information that is confidential and may be privileged. If you are not the intended recipient, please notify the sender and then delete this e-mail immediately. *"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"* _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk