[Django]-Django Queryset: Need help in optimizing this set of queries

by

2👍

✅

Unfortunately django doesn’t allow joining unless there’s a foreign key (or one to one) involved. You’re going to have to do it in code. I’ve found a way (totally untested) to do it with a single query which should improve execution time significantly.

from collections import Counter
from itertools import combinations

# Assuming Models
class Question(models.Model):
    ...

class Tag(models.Model):
    tag = models.CharField(..)
    question = models.ForeignKey(Question, related_name='tags')

c = Counter()
questions = Question.objects.all().prefetch_related('tags') # prefetch M2M
for q in questions:
    # sort them so 'point' + 'curve' == 'curve' + 'point'
    tags = sorted([tag.name for tag in q.tags.all()])
    c.update(combinations(tags,2)) # get all 2-pair combinations and update counter
c.most_common(5) # show the top 5

The above code uses Counters, itertools.combinations, and django prefetch_related which should cover most of the bits above that might be unknown. Look at those resources if the above code doesn’t work exactly, and modify accordingly.

If you’re not using a M2M field on your Question model you can still access tags as if it were a M2M field by using reverse relations. See my edit that changes the reverse relation from tag_set to tags. I’ve made a couple of other edits that should work with the way you’ve defined your models.

If you don’t specify related_name='tags', then just change tags in the filters and prefetch_related to tag_set and you’re good to go.

👤Josh Smeaton

2👍

If I understood your question correctly, I would keep things simpler and do something like this

relevant_tags = Tag.objects.filter(question_id__in=qnlist)
#Here relevant_tags has both a and b tags

unique_tags = set()
for tag_item in relevant_tags:
    unique_tags.add(tag_item.tag)

#unique_tags should have your A and B tags

a_tag = unique_tags.pop()
b_tag = unique_tags.pop() 

#Some logic to make sure what is A and what is B

a_tags = filter(lambda t : t.tag == a_tag, relevant_tags)
b_tags = filter(lambda t : t.tag == b_tag, relevant_tags)

#a_tags and b_tags contain A and B tags filtered from relevant_tags

same_question_tags = dict()

for q in qnlist:
  a_list = filter(lambda a: a.question_id == q.id, a_tags)
  b_list = filter(lambda a: a.question_id == q.id, b_tags)
  same_question_tags[q] = a_list+b_list

The good thing about this is you can extend it to N number of tags by iterating over the returned tags in a loop to get all unique ones and then iterating further to filter them out tag wise.

There are definitely more ways to do this too.

👤Pratik Mandrekar

Leave a comment