"where not exists"

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

"where not exists"

Alex Povolotsky
Hello

I need to implement a query whith "where not exists", "negative join".

SELECT id,timestamp, value FROM asb_bill_tab WHERE NOT EXISTS ( SELECT
NULL FROM asb_sb_tab WHERE id_b = id)
and timestamp >= 201701 AND value != 0 ORDER BY timestamp

Do DBIx::Class have some syntax things for it or I'd better use literal SQL?

Alex

_______________________________________________
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: "where not exists"

Dagfinn Ilmari Mannsåker
Alex Povolotsky <[hidden email]> writes:

> Hello
>
> I need to implement a query whith "where not exists", "negative join".
>
> SELECT id,timestamp, value FROM asb_bill_tab WHERE NOT EXISTS ( SELECT
> NULL FROM asb_sb_tab WHERE id_b = id)
> and timestamp >= 201701 AND value != 0 ORDER BY timestamp
>
> Do DBIx::Class have some syntax things for it or I'd better use literal SQL?

You can use DBIC to generate the literal SQL for the anti-join with the
help of DBIx::Class::Helper::ResultSet::CorrelateRelationship.

Assuming you have a resultset for asb_bill_tab in $bill_rs, and it has a
relationship called 'sbs' to asb_sb_tab on id_b = id:

$bill_rs->search({
    -not_exists => $bill_rs->correlate('sbs')->as_query,
    timestamp => { '>=' => 201701 },
    value => { '!=' => 0},
})

> Alex

Hope this helps,

Ilmari


--
"A disappointingly low fraction of the human race is,
 at any given time, on fire." - Stig Sandbeck Mathisen


_______________________________________________
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: "where not exists"

Alex Povolotsky
How nice!

Thanks. A good alternative to create view

On 04.04.2017 12:59, Dagfinn Ilmari Mannsåker wrote:

> Alex Povolotsky <[hidden email]> writes:
>
>> Hello
>>
>> I need to implement a query whith "where not exists", "negative join".
>>
>> SELECT id,timestamp, value FROM asb_bill_tab WHERE NOT EXISTS ( SELECT
>> NULL FROM asb_sb_tab WHERE id_b = id)
>> and timestamp >= 201701 AND value != 0 ORDER BY timestamp
>>
>> Do DBIx::Class have some syntax things for it or I'd better use literal SQL?
>
> You can use DBIC to generate the literal SQL for the anti-join with the
> help of DBIx::Class::Helper::ResultSet::CorrelateRelationship.
>
> Assuming you have a resultset for asb_bill_tab in $bill_rs, and it has a
> relationship called 'sbs' to asb_sb_tab on id_b = id:
>
> $bill_rs->search({
>     -not_exists => $bill_rs->correlate('sbs')->as_query,
>     timestamp => { '>=' => 201701 },
>     value => { '!=' => 0},
> })
>
>> Alex
>
> Hope this helps,
>
> Ilmari
>
>

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