[Dbix-class] Subquery question

[Matt S Trout]

A. Pagaltzis wrote:
> * Nigel Metheringham <Nigel.Metheringham at dev.intechnology.co.uk> [2006-05-16 15:00]:
>> 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.
> 
> Are you sure that this works? Has SQL::Abstract had an API
> upgrade while I wasn’t looking? A year ago, I spent several
> evenings trying to coerce it to do subqueries sanely but failed.
> 
> (At the time, my conclusion was that using pure anonymous data
> structures as an abstraction is broken and that a sane API would
> have to use objects to represent aspects of a query in order to
> facilitate proper composition. I sighed, stuck it into my project
> idea list along with a few conceptual notes, and found other
> things to do.)

Passing it a scalarref causes it to pass the SQL through intact without 
touching it. Or at least it does on our custom subclass (I've given up trying 
to sending patches upstream after deafening silence from the author during 
every previous attempt).

I'm pretty sure I can persuade it to do subqueries in an at least *relatively* 
sane fashion; we'll find out whether I'm right once somebody imports the tests.

-- 
      Matt S Trout       Offering custom development, consultancy and support
   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

+ Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +