[Dbix-class] join ON *AND*

[Hartmaier Alexander]

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.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*