I need to retrieve data from multiple tables, with a dynamically built filter that might or might not use data from any of the tables.
So say I have this:
class Solution(models.Model): name = models.CharField(max_length=MAX, unique=True) # Other data class ExportTrackingRecord(models.Model): tracked_id = models.IntegerField() solution = models.ForeignKey(Solution) # Other data
Then elsewhere I need to do:
def get_data(user_provided_criteria): etr = ExportTrackingRecord.objects.filter(make_Q_object(user_provided_criteria)).select_related() for data in etr: s = data.solution # do things with data from both tables
As far as I can tell, if I happen to filter on a field in Solution, django will do the join, and
select_related get both objects. If I only filter on fields in
ExportTrackingRecord then there will be no join, and django will generate a new query for each
ExportTrackingRecord in the QuerySet (which could be thousands...)
I am fairly new to django, but is there a reasonable way to force the join?
select_related() is the key to your problem. If you don't use it and don't filter on fields of the related model Django will not do a join and cause an extra query for every row in the result if you are accessing data of the related model.
If you do something like
ExportTrackingRecord.objects.filter(...).select_related('solution') you force Django to always do a join with the
If you need to do the same in the other direction, through the reverse foreign key relation ship you need
prefetch_related(), same for many-to-many relations