Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#15240 closed (worksforme)

when I combine two Q objects with | operator it ends up with AND being used in sql query.

Reported by: muaddib@… Owned by: nobody
Component: Database layer (models, ORM) Version: 1.2
Severity: Keywords:
Cc: alejandroalonsofernandez@… Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

Hello. I created two querysets which works fine.

Invoice.objects.annotate(mysum=Sum('payments__payment')).filter(Q(mysum__lte=F('invoice_total')) )
Invoice.objects.annotate(mysum=Sum('payments__payment')).filter(mysum=None)

queryset1 returns objects with mysum less then invoice_total. mysum sums payments for the particular invoice.

queryset2 catches non-numeric objects with 'None' as mysum value. This happens when there no payments for the particular invoice.

Fields used:
invoice_total is a decimal field.
payment is a related_set relation which holds a decimal, too.

Now to combine those two querysets into one i use Q logic.

Invoice.objects.annotate(mysum=Sum('payments__payment')).filter(Q(mysum=None) | Q(mysum__lte=F('invoice_total')) )

Unfortunatly it doesnt work (returned queryset is empty). In sql log I can see that those two conditions above are always being glued with default AND (should be ORed because I use Q | Q, not Q & Q).

Database is a postresql 8.3.

Change History (3)

comment:1 Changed 5 years ago by Alex

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to worksforme
  • Status changed from new to closed

This is fixed in trunk.

comment:2 follow-up: Changed 5 years ago by alejandroalonsofernandez@…

  • Cc alejandroalonsofernandez@… added

I have a similar problem in django 1.2.4 but not in 1.2.5. In my case I did the following test:

print Design.objects.annotate(tshitems_count=Count('items__tsh_items')).filter(Q(tshitems_count=0) | Q(id=0)).query

In 1.2.4:

SELECT "designs"."id", "designs"."sfid", "designs"."slug", "designs"."name", "designs"."brand_id", "designs"."code", "designs"."collection_id", "designs"."description", "designs"."photo1", "designs"."photo2", "designs"."photo3", "designs"."photo4", "designs"."type", "designs"."sex", "designs"."designer_id", COUNT("trunk_show_host_items"."id") AS "tshitems_count" FROM "designs" LEFT OUTER JOIN "items" ON ("designs"."id" = "items"."design_id") LEFT OUTER JOIN "trunk_show_host_items" ON ("items"."id" = "trunk_show_host_items"."item_id") WHERE ("designs"."id" = 0 ) GROUP BY "designs"."id", "designs"."sfid", "designs"."slug", "designs"."name", "designs"."brand_id", "designs"."code", "designs"."collection_id", "designs"."description", "designs"."photo1", "designs"."photo2", "designs"."photo3", "designs"."photo4", "designs"."type", "designs"."sex", "designs"."designer_id", "designs"."id", "designs"."sfid", "designs"."slug", "designs"."name", "designs"."brand_id", "designs"."code", "designs"."collection_id", "designs"."description", "designs"."photo1", "designs"."photo2", "designs"."photo3", "designs"."photo4", "designs"."type", "designs"."sex", "designs"."designer_id" HAVING COUNT("trunk_show_host_items"."id") = 0 ORDER BY "designs"."name" ASC

In 1.2.5:

SELECT "designs"."id", "designs"."sfid", "designs"."slug", "designs"."name", "designs"."brand_id", "designs"."code", "designs"."collection_id", "designs"."description", "designs"."photo1", "designs"."photo2", "designs"."photo3", "designs"."photo4", "designs"."type", "designs"."sex", "designs"."designer_id", COUNT("trunk_show_host_items"."id") AS "tshitems_count" FROM "designs" LEFT OUTER JOIN "items" ON ("designs"."id" = "items"."design_id") LEFT OUTER JOIN "trunk_show_host_items" ON ("items"."id" = "trunk_show_host_items"."item_id") GROUP BY "designs"."id", "designs"."sfid", "designs"."slug", "designs"."name", "designs"."brand_id", "designs"."code", "designs"."collection_id", "designs"."description", "designs"."photo1", "designs"."photo2", "designs"."photo3", "designs"."photo4", "designs"."type", "designs"."sex", "designs"."designer_id", "designs"."id", "designs"."sfid", "designs"."slug", "designs"."name", "designs"."brand_id", "designs"."code", "designs"."collection_id", "designs"."description", "designs"."photo1", "designs"."photo2", "designs"."photo3", "designs"."photo4", "designs"."type", "designs"."sex", "designs"."designer_id" HAVING (COUNT("trunk_show_host_items"."id") = 0 OR "designs"."id" = 0 ) ORDER BY "designs"."name" ASC

The OR condition only appears in 1.2.5

comment:3 in reply to: ↑ 2 Changed 5 years ago by ramiro

Replying to alejandroalonsofernandez@…:

I have a similar problem in django 1.2.4 but not in 1.2.5...

Yes, as Alex said this was fixed in trunk (in [15173]) and also in the 1.2.X branch after 1.2.4 and before 1.2.5 (in [15174])

Note: See TracTickets for help on using tickets.
Back to Top