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 :)
_______________________________________________
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/
