Django duplicate queries with manager related

Question

I'm working on optimizing my ORM queries. I have two apps, 'app1' and 'app2'. One class of 'app2' has a foreign key to a class of app1 as follows:

#app1/models.py
class C1App1(WithDateAndOwner):
  def get_c2_app2(self):
    res = self.c2app2_set.all()
    if res.count() > 0:
      return res[0]
    else:
      return None

#app2/models.py
class C2App2(WithDateAndOwner):
  c1app1 = models.ForeignKey("app1.C1App1")
  is_ok = models.BooleanField(default=False)

Now I display the C2App2 for all instances of C1App1 in the admin page:

#app1/admin.py
@admin.register(C1App1)
class C1App1Admin(admin.MyAdmin):
  list_display = ("get_c2_app2")
  list_select_related = ()
  list_prefetch_related = ("c2app2_set",)
  list_per_page = 10

prefetch_related reduces this query:

SELECT ••• FROM `app2_c2app2` WHERE `app2_c2app2`.`c1app1_id` = 711
  Duplicated 19 times.

to:

SELECT ••• FROM `app2_c2app2` WHERE `app2_c2app2`.`c1app1_id` IN (704, 705, 706, 707, 708, 709, 710, 711, 702, 703) ORDER BY `app2_c2app2`.`id` DESC

And it's fine. Now if I want to filter the query on C2App2's attribute 'is_ok':

#app1/models.py
class C1App1(WithDateAndOwner):
  def get_c2_app2(self):
    res = self.c2app2_set.filter(is_ok=False)
    if res.count() > 0:
      return res[0]
    else:
      return None

I still have this prefetched query:

SELECT ••• FROM `c2app2_set` WHERE `app2_c2app2`.`c1app1_id` IN (704, 705, 706, 707, 708, 709, 710, 711, 702, 703) ORDER BY `app2_c2app2`.`id` DESC

but with that one duplicated for each displayed instance of C1App1 (10). :

SELECT ••• FROM `app2_c2app2` WHERE (`app2_c2app2`.`c1app1_id` = 711 AND `app2_c2app2`.`is_ok` = 1)
  Duplicated 13 times.

Actually the query is also duplicated again for 3 ids out of the 10 displayed, which results in those 13 duplicated queries. What can I do in order to not duplicate those queries? It seems the prefetch_related doesn't help anymore here.

Answer 1

prefetch_related only works when you use .all(). If you apply any other transformations like .filter(), the new DB query will be made. This is because prefetch_related simply caches ALL related instances in list, so Django cannot perform filter() on list. To solve your issue you should use Prefetch object. You can pass queryset parametr to it. So, instead of using list_prefetch_related, override get_queryset method in your admin class.

def get_queryset(*args, **kwargs):
     qs = super().get_queryset(*args, **kwargs)
     qs = qs.prefetch_related(Prefetch('c2app2_set', queryset=C2App2.objects.filter(is_ok=False)))
     return qs

And

class C1App1(WithDateAndOwner):
   def get_c2_app2(self):
      res = self.c2app2_set.all()
      if res.count() > 0:
         return res[0]
      else:
         return None