[Dbix-class] Subquery question

[Nigel Metheringham]

On Thu, 2006-05-18 at 10:28 +0100, Nigel Metheringham wrote:
> On Wed, 2006-05-17 at 10:47 +1200, 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.

> Well I was working on that about a month ago, and it worked then...
> Today I finally managed to get back to this project, and it completely
> fails to work :-)

I can't make that form work now, nor can I make it work with reverted
versions of DBIC, and I have no SQL::Abstract updates in recent
memory... so if it worked (and I am *sure* I had code using that form
working fine) then its a mystery.

However *this* does work:-
    my $rs     = $schema->resultset('TaskLog');
    my $subsel = 'IN (SELECT tl_id FROM task_last_run)';
    my $qry    = $rs->search(
        {
            tl_timestamp => { '<=',   $fromdate },
            tl_id        => \$subsel,
        }
    );

ie you put the raw SQL as a direct (referenced) value of the column,
rather than in a further layer of indirection (the -in in my case).

AT least that works at the moment.... my paranoia says it will stop
working as soon as I look away.

	Nigel.
-- 
[ Nigel Metheringham           Nigel.Metheringham at InTechnology.co.uk ]
[ - Comments in this message are my own and not ITO opinion/policy - ]