[Dbix-class] join ON *AND*

[Carl Franks]

On 21 February 2014 09:40, Moritz Lenz <moritz at faui2k3.org> wrote:
>
> When I recently asked the same question on IRC, that was the answer I got:
> you need to add new relationship and specify that in the join.


Hi,
Thanks - I finally figured out that I had to define the relationship
$cond as a sub-ref - to get around the usual restriction that keys and
values must start with "foreign." and "self."

Here's what I ended up with:
The new relationship:

__PACKAGE__->has_many(
    "response_comments_with_alert",
    "MSAS::Schema::Result::ResponseComment",
    sub {
        my $args = shift;
        return {
            "$args->{foreign_alias}.query_id" => { ident =>
"$args->{self_alias}.id" },
            "$args->{foreign_alias}.alert"    => 1,
            "$args->{foreign_alias}.status"   => "posted",
        },
    },
    { cascade_copy       => 0, cascade_delete => 0 },
);

And the calling code:

my $rs = $schema->resultset('Query')->search(
    undef,
    {
        join => ['response_comments_with_alert'],
        distinct => 1,
        '+select' => [ { count => 'response_comments_with_alert.id' } ],
        '+as' => ['alerts'],
    },
);

Many thanks,
Carl