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
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
