CODEWITHSUNDEEP
pythondjangodjango-queryset
coding
coding

Reputation: 191

Queryset with values filtered from another queryset

If I have a queryset qs1 like this:

<QuerySet [{u'name': u'John', u'birthdate': u'1980-01-01'}, 
           {u'name': u'Larry', u'birthdate': u'1976-12-28'}, 
            .....']}

I need to use all values associated with key 'name' from qs1 to query another model and get qs2 like this:

<QuerySet [{u'student_name': u'John', u'grade': u'A'}, 
           {u'student_name': u'Larry', u'grade': u'B'}, 
            .....']}

After this, I have to combine qs1 and qs2 so the final_qs like this:

[{u'name': u'John',u'birthdate': u'1980-01-01', u'grade': u'A'}, 
 {u'student_name': u'Larry', u'birthdate': u'1976-12-28', u'grade': u'B'}, 
  .....']}

How would I achieve this? I have code like this:

qs1 = Person.objects.values('name', 'birthdate')
for t in qs1:
     qs2 = Grades.objects.filter(student_name=t['name'])
                               .values('student_name', 'grade')

My qs1 looks OK. However, my qs2 becomes this:

<QuerySet [{u'student_name': u'John', u'grade': u'A'}]>
<QuerySet [{u'student_name': u'Larry', u'grade': u'B'}]>

Because of qs2, I am not able use zip(qs1, qs2) to construct final_qs the way I want.

Upvotes: 0

Views: 283

Answers (2)

Stefan Collier
Stefan Collier

Reputation: 4682

I assume that your Grade model contains the ForeignKey(Person) which means that there is a reverse relation created. Which means that you can "follow the relationship backwards" and all sorts of other django magic.

So given you have a queryset qs1 of Persons:

>>> qs1.values('student_name', 'birthdate', 'grade_set__grade')

The aforementioned magic can be observed by this mysterious 'grade_set__grade'.

Django has actually added another field to you Person model: .grade_set. This would be all the grades that have the belong to that person instance. We can then access all those grades via djangos double underscore notation that we use in filters.

You can quickly try that out by doing User.objects.last().grade_set

Bonus

Don't like the term 'grade_set', prefer something else? You can change this in the Grade model. 

   class Grade(models.Model):
       # some fields
       ForeignKey(Person, related_name='all_their_grades')
       # other fields

You can now do User.objects.last().all_their_grades to do the same as before. Please ntoe that once you change it, the .grade_set is no longer available.

Upvotes: 0

Laurynas Tamulevičius
Laurynas Tamulevičius

Reputation: 1589

I would aggregate all qs1 names to a list:

names = [t['name'] for t in qs1]

And then execute the following query:

qs2 = Grades.objects.filter(student_name__in=names)
                           .values('student_name', 'grade')

The latter would select only the rows where student_name is in the names from the qs1 and should return a single QuerySet with all matching rows.

Upvotes: 1

Related Questions