CODEWITHSUNDEEP
pythonsqldatabasedjango
Matthew Scouten
Matthew Scouten

Reputation: 15561

How can I retrieve data from multiple tables

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?

Upvotes: 1

Views: 1473

Answers (2)

ykaganovich
ykaganovich

Reputation: 14964

select_related controls what gets loaded into the results when the QuerySet is evaluated. it will force the join regardless of filtering.

If you don't specify select_related, then even if your filter produces a sql query with a join, the parent model's fields won't be loaded in the results, and accessing them will still require additional queries.

Upvotes: 0

Bernhard Vallant
Bernhard Vallant

Reputation: 50776

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

Upvotes: 1

Related Questions