#29551: My use-case of QuerySet.extra(): Join two models without explicit
relation
--------------------------------+--------------------------------------
Reporter: Bowser | Owner: nobody
Type: Uncategorized | Status: closed
Component: Uncategorized | Version: 2.0
Severity: Normal | Resolution: invalid
Keywords: QuerySet.extra | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
--------------------------------+--------------------------------------
Comment (by jonathan-golorry):
`ForeignObject` requires that you join on a unique combination of fields.
I don't think there's a `ForeignObject` equivalent for a ManyToMany
relationship. For filtering functionality, you can get pretty much
whatever you need by annotating subqueries. I haven't looked into
prefetching.
In my case, I have customers and purchases. I want to see when a customer
has cancelled a purchase without later making another purchase in the same
category. Naively:
{{{
Purchase.objects.filter(cancelled=True).exclude(
customer__purchases__category=F("category"),
customer__purchases__id__gt=F("id"),
)
}}}
This doesn't work because django creates separate subqueries for each
exclude condition (this might be a django bug, since django normally
combines conditions in the same exclude call).
My working implementation:
{{{
later = Purchase.objects.filter(
category=OuterRef("category"),
customer_id=OuterRef("customer_id"),
id__gt=OuterRef("id"),
)
Purchase.objects.annotate(latest=~Exists(later)).filter(latest=True,
cancelled=True)
}}}
`ForeignObject` generates a correct sql query using an inner join instead
of a subquery, but the system check fails. Customers very rarely purchase
something in the same category multiple times, so the inner join on
`customer_id` and `category` is around as fast as the subquery. I actually
managed to get a faster sql query putting the ID comparison directly in a
left join, but I'm not sure why that worked.
{{{
same_category = models.ForeignObject(
"self",
on_delete=models.PROTECT,
from_fields=["customer", "category"],
to_fields=["customer", "category"],
)
Purchase.objects.filter(cancelled=True).exclude(
same_category__id__gt=F("id"),
)
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/29551#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/064.048d631b1d4b5ef3a34c34422d7ed3bd%40djangoproject.com.
