[Dbix-class] Subquery question

[Matt S Trout]

Dan Horne wrote:
>  Nigel Metheringham wrote:
>> Here's an example I have it returns all items in one table (TaskLog) old
>> than $fromdate and not referenced by another (task_last_run)
>>
>>     my $rs     = $schema->resultset('TaskLog');
>>     my $subsel = '(SELECT tl_id FROM task_last_run)';
>>     my $qry    = $rs->search(
>>         {
>>             tl_timestamp => { '<=',   $fromdate },
>>             tl_id        => { -not_in => \$subsel }
>>         }
>>     );
>>
>> The magic trick is the reference to a string containing raw SQL - this
>> is documented in the SQL::Abstract documentation.
>>
> 
> Thanks, Nigel. I couldn't get this to work for me, but the following did
> get me there in the end:
> 
> my @avail_roles =
>           $schema->resultset('AppRole')->search_literal(
>             'role_id not in
>                 (select role_id
>                  from   user_role u
>                  where  u.user_id = ?)', $user_id
>         );
> 
> I'd rather have used the "not exists" clause, as it's more efficient
> than "not in" in the context of what I wanted to do, but I couldn't find
> a way to express it.

exists and not exists support natively should turn up along with 
subqueries. But I'm still waiting for somebody to volunteer to do a 
patch that adds the SQL::Abstract tests (hint hint :)