[Dbix-class] join ON *AND*

[Carl Franks]

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?
Cheers,
Carl