[Dbix-class] join ON *AND*

[Darren Duncan]

On 2014-02-21, 1:22 AM, Hartmaier Alexander wrote:
> On 2014-02-21 10:16, Carl Franks wrote:
>> 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',
> }, {
> ...
> });

That doesn't work in general because we're dealing with an OUTER JOIN here and 
not an INNER JOIN.

The join result still needs to have a row for each "query" row, and the effect 
of putting the filter in ON rather than WHERE is meant to make it as if an 
otherwise-matching "response_comment" row doesn't exist, in which case the join 
result would still supposed to have a row but columns from response_comment 
would be null.

Your proposed solution would eliminate "query" rows too because it is applied 
after the join, not before as expected.

Note that an alternative SQL syntax to using ON is to replace the 
"response_comment" with a FROM subquery that does the filtering.

-- Darren Duncan