[Django] #29551: My use-case of QuerySet.extra(): Join two models without explicit relation
Django
-----------------------------------------+--------------------------------
Reporter: Bowser | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 2.0
Severity: Normal | 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 |
-----------------------------------------+--------------------------------
Hello,
I'm reporting here, as suggested in the docs, my use-case of
QuerySet.extra() method, looking if there is any way to make it in a
different way.
My django app is inter-operating with an independent third-party email
program which created and filled independently some tables in a separate
DB.
In order to obtain data from that independent tables in my django system I
needed to map those tables in a new django model using the command
inspectdb of manage.py. This worked very well with minimal corrections and
let me the opportunity to get successfully the data from that tables in my
Django system.
My need of QuerySet.extra() came out when I needed to join that special
model with other (regular) models of my django system with a field (the
email address the sender used) that can be put in relation with some of
the customers of my system (but of course it may not be too if there is no
relation if the sender email is unknown)
Obviuosly I counldn't put a normal ForeignKey on that model nor I can
change the structure of the tables because it could block my third party
email system from working properly so I couldn't put a direct regular
relation between those models.
This ended in the insertion of the .extra() method in the main queryset of
the ModelAdmin (I'm testing that system in the admin panels for now and is
working properly with the target of using it in my main app too):
{{{#!python
def get_queryset(self, request):
return super(InboxAdmin,self).get_queryset(request).extra(
select={'customer': 'customer_id'},
where=["SenderEmail = email"],
tables=['Customers_customer']
)
}}}
This is working well but I wonder if this can be done in a better way.
So my question practically is if there is any way in Django to join two
models even when there is no explicit relation between them (OneToOne,
ManyToMany or ForeignKey) that I can't insert.
Any idea?
Thank you
--
Ticket URL: <https://code.djangoproject.com/ticket/29551>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
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
Changes (by Simon Charette):
* status: new => closed
* resolution: => invalid
Comment:
Hey Bowser,
I think the best approach here would be to define a
`django.db.models.related.ForeignObject` on your model by making sure to
define appropriate `from_fields` and `to_fields`. Such objects will behave
just like `ForeignKey`s with regards to ORM interactions (`filter`,
`annotate`, `values`, ...) but aren't backed by a implicit concrete
column. Under the hood `ForeignKey` is a subclass of `ForeignKey` that
creates a concrete a column to use it as `from_fields` and defaults
`to_fields` to the referenced model's primary key.
For example on your `Inbox` model.
{{{#!python
class Inbox(models.Model):
...
sender_email = models.EmailField(db_column='SenderEmail')
customer = ForeignObject(Customer, models.CASCADE,
from_fields=['sender_email'], to_fields=['email'])
class Meta:
managed = False
...
}}}
I'll close this ticket as invalid for now but feel free to re-open it if
the provided alternative to your usage of `extra()` isn't appropriate.
--
Ticket URL: <https://code.djangoproject.com/ticket/29551#comment:1>
Django
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>