[Dbix-class] Subquery question

Nigel Metheringham Nigel.Metheringham at dev.intechnology.co.uk
Tue May 16 14:57:56 CEST 2006


On Tue, 2006-05-16 at 08:35 -0400, Sean Davis wrote:
> On 5/16/06 8:32 AM, "Dan Horne" <dan.horne at redbone.co.nz> wrote:
> > I  know that the SQL below works with MySQL v4 so I'd like to be able to
> > issue it directly. Is it possible to somehow pass the raw SQL and get  a
> > DBIx result set returned?
> 
> A somewhat related question--will DBIC deal with a view?  If so, could you
> make a view and have it be treated as a table?

I have used earlier DBIC versions with views in PgSQL & SQLite (and have
no reason to expect the support to have broken since - I just haven't
tried it).

You need to define the row class data manually - loader won't work.  If
developing with SQLite there was a bug in DBD::SQLite that caused things
to go very pear shaped on startup if you had classes mapping to views -
There is a bug open about this - not been updated for recent release so
no idea if its still an issue
  http://rt.cpan.org/Public/Bug/Display.html?id=12347

Back to the original question about subselects... you can do subselects
- its a horrid bastardisation of DBIC and straight SQL, but it works.

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.

	Nigel.


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




More information about the Dbix-class mailing list