[Dbix-class] Subquery question

[Dan Horne]

 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.

Cheers

Dan