[Dbix-class] Incorrect SQL being generated after DBIC library upgrade

Anthony Gladdish a.j.gladdish at newcastle.ac.uk
Thu Mar 19 15:04:05 GMT 2009


Hi,

Upgrading from:

DBIC v0.08010 
SQL:Abstract v1.24 

to:

DBIC v0.08012 
SQL:Abstract v1.5

My ResultSet method contains following search:

my $orders_current = $schema->resultset('Order')->search(
        {
            -and => [
                -or => [
                    "module_access.expires" => { ">", DateTime->now() },
                    "scheme_access.expires" => { ">", DateTime->now() },
                ],
                "me.person" => $self->id(),
            ],  
        },        
        {
            distinct => 1,
            join => ['module_access','scheme_access'],
        }
    );

... produces following (incorrect?) SQL:

SELECT COUNT( DISTINCT( me.id ) ) 
FROM Orders me 
LEFT JOIN Module_Access module_access ON module_access.order_id = me.id 
LEFT JOIN Scheme_Access scheme_access ON scheme_access.order_id = me.id 
WHERE ( ( ( module_access.expires > ? OR scheme_access.expires > ? ) OR me.person = ? ) ): '2009-03-19T14:38:06', '2009-03-19T14:38:06', '2'

I'm expecting:

WHERE ( ( ( module_access.expires > ? OR scheme_access.expires > ? ) AND me.person = ? ) ): '2009-03-19T14:38:06', '2009-03-19T14:38:06', '2'

... notice the "AND". This expected SQL was getting generated prior to upgrading my libraries.

>From the DBIC 0.08012 cookbook "Complex WHERE clauses" documentation, it looks like my original search() should work?!

Placing {} braces around the "-or => []" bit, or doing "-and => {}" instead, appears to fix this, but I'm not sure if this is correct or not?

Also, using "-nest" operator is a possible workaround, but again, I'm pretty sure the resultset search above should be working?

Any ideas or suggestions from anyone would be great.

Many thanks,
Anthony



More information about the DBIx-Class mailing list