[Dbix-class] "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 <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
>
>