[Dbix-class] "where not exists"

Dagfinn Ilmari Mannsåker ilmari at ilmari.org
Tue Apr 4 09:59:11 GMT 2017

Alex Povolotsky <tarkhil at over.ru> 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:

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

> Alex

Hope this helps,


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

More information about the DBIx-Class mailing list