How do I retrieve data from multiple tables

advertisements

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 Solution table.

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