join ON *AND*

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

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

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: join ON *AND*

Alexander Hartmaier
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@...



*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
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@...
Reply | Threaded
Open this post in threaded view
|

Re: join ON *AND*

Carl Franks
On 21 February 2014 09:22, Hartmaier Alexander
<[hidden email]> wrote:
> Just restrict the resultset:
> ->search({
>     'response_comments.alert' => 1,
>     'response_comments.status => 'posted',
> }, {
> ...
> });
>

Hi,
Thanks for the response.
Unfortunately, that adds the clause after a "WHERE", which causes it
to only return queries which have a related response_comment - e.g.
id | title | count(response_comment.id)
---------------------------------------
 2 | two   | 1
- it doesn't return any rows where count=0.

I'm looking at the docs now for add_relationship() - I'm wondering
whether I need to create a new relationship which contains the AND
clauses.

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@...
Reply | Threaded
Open this post in threaded view
|

Re: join ON *AND*

Moritz Lenz
On 02/21/2014 10:28 AM, Carl Franks wrote:

> On 21 February 2014 09:22, Hartmaier Alexander
> <[hidden email]> wrote:
>> Just restrict the resultset:
>> ->search({
>>      'response_comments.alert' => 1,
>>      'response_comments.status => 'posted',
>> }, {
>> ...
>> });
>>
>
> Hi,
> Thanks for the response.
> Unfortunately, that adds the clause after a "WHERE", which causes it
> to only return queries which have a related response_comment - e.g.
> id | title | count(response_comment.id)
> ---------------------------------------
>   2 | two   | 1
> - it doesn't return any rows where count=0.
>
> I'm looking at the docs now for add_relationship() - I'm wondering
> whether I need to create a new relationship which contains the AND
> clauses.

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.

Cheers,
Moritz

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: join ON *AND*

Carl Franks
On 21 February 2014 09:40, Moritz Lenz <[hidden email]> 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

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: join ON *AND*

Darren Duncan
In reply to this post by Alexander Hartmaier
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


_______________________________________________
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@...