[Dbix-class] "where not exists"

[Dagfinn Ilmari Mannsåker]

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:

$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