[Dbix-class] selecting records with no links-to

[Ash Berlin]

On 14 Jun 2013, at 13:52, Wolfgang Kinkeldei <wolfgang at kinkeldei.de> wrote:

> Hi,
> 
> Am 14.06.2013 um 13:31 schrieb Dave Howorth:
> 
>> My brain is feeling fried and is failing to solve a simple puzzle.
>> 
>> Using the standard table 'artist' which has a primary key 'artistid'.
>> 
>> And the other table 'cd' that has a link to artists, via a foreign key
>> 'artist'. There may be more than one CD associated with a single artistid.
>> 
>> I want a resultset containing all artists which have NO associated CD.
>> 
>> What's the appropriate way to select this resultset?
> 
> one way would be to shrink your resultset with '-not_in'. However, "NOT IN" in SQL is sometimes very inefficient.
> 
> my $artists_with_cd =
>    $schema->resultset('CD')->get_column('artist')->as_query;
> 
> my @artists_without_cd =
>    $schema->resultset('Artist')->search( { artistid => { -not_in => $artists_with_cd } } )->all;
> 
> 
> Best,
> 
> Wolfgang Kinkeldei

Another way of doing this is

SELECT * from artists LEFT JOIN cds ON (….) WHERE cds.id IS NULL

Untested but ISTR doing something like this in the past. I make no comments as to the efficiency or lack there of.

-ash