django models filter() and extra()

Question

Hi all,

I have a problem with the extra() method of queryset.

So, i retrieve my objects with :

invoices = Invoice.objects.select_related().filter(quantity__gt=0,begin__gte=values['start_day'],end__lte=values['end_day'])

So it works, I have my invoices. After i use another time filter() :

invoices = invoices.filter(max__gte=duration)

It works too. But, after, i need to use extra() because of my request, so i have that :

cond = 'discount="YES" AND priceeuro*(%d%%fixe)<=%d'

invoices = invoices.extra(where=[cond],params=[duration,price])

Well, it works but my invoices variable contains more elements that before. It's like the two filter() weren't used.

If you know why,

thanks.

EDIT:

This is the SQL associated with the query:

WHERE 
("invoice"."product_id" IN (
    SELECT U0."id" 
    FROM "product" U0 
    WHERE U0."accommodation_id" IN (
        SELECT U0."id" 
        FROM "accommodation" U0 
        WHERE U0."resort_id" IN (
            SELECT U0."id" 
            FROM "resort" U0 
            WHERE U0."area_id" IN (
                SELECT U0."id" 
                FROM "area" U0 
                WHERE U0."country_id" = 9 
)))) 
AND "invoice"."quantity" > 0  
AND "invoice"."end" <= 2010-12-31  
AND "invoice"."begin" >= 2010-12-01  
AND fixe % 7 = 0 
AND (discount="YES" AND pricediscountincludedeuro*(7% fixe)<=250)OR(discount="NO" AND     priceeuro*(7% fixe)<=250))

Answer 1

Dump the SQL from the query set object:

print invoices.query

If the cause of your bug is not obvious from looking at the generated SQL, update your question and post the SQL for us to see.

EDIT 1 based on seeing the SQL

I'm questioning the very last line in your SQL (re-formatted):

...
 AND (discount="YES" AND pricediscountincludedeuro*(7% fixe)<=250)
 OR (discount="NO" AND priceeuro*(7% fixe)<=250)
)

It seems to me like you want those two 'discount' checks wrapped in another set of parentheses to form their own logical check:

...
 AND (
   (discount="YES" AND pricediscountincludedeuro*(7% fixe)<=250)
   OR 
   (discount="NO" AND priceeuro*(7% fixe)<=250)
 )
)

Without that explicit grouping, the OR is going to be compared independently of the other 'discount' check and that will cause it to include things that you've already excluded in the above predicates.