how to efficiently use _set.all() in django?
Solution 1
You should use prefetch_related
entrys = Entry.objects.all().prefetch_related('color_set')
for e in entrys:
print e.color_set.all()
Instead of doing n, queries it will do 2, one for the entries, and one for the foreign key lookups
Solution 2
As I commented earlier, if you just need all the colors of an Entry
together, you can select all the Color
objects and order them on entry
:
colors = Color.objects.order_by('entry')
Now, you can loop over these objects and print them the way you want:
for color in colors:
print(color.entry.user, color.color)
# john, blue
# john, orange
# bob, green
Or, you can extract this information as values_list
color_entries = list(colors.values_list('entry__user', 'color'))
# [('john', 'blue'), ('john', 'orange'), ('bob', 'green'), ...]
Related videos on Youtube
John Waller
PhD student in Evolution. author of EasyMARK R package
Updated on September 16, 2022Comments
-
John Waller over 1 year
Is there a way in django to do the following more efficiently when the number Entry objects is greater than 5000 entries?
models.py
class Entry(models.Model): user = models.TextField(db_column='User', blank=True) date = models.DateTimeField(blank=True) class Color(models.Model): color = models.TextField(blank=True) entry = models.ForeignKey(Entry)
And let's say that I wanted to get all the colors for each of these entries...
entrys = Entry.objects.all() for e in entrys: print e.color_set.all()
I want to be able to relate each object to a specific entry. For example, in a csv table like this.
user, color john, blue john, orange bob, green bob, red bob, purple
It takes several seconds to look through all of my entries. Is there a better way?
-
AKS about 8 yearsYou can go other way around. Get all the Color objects and order them by
entry
and then iterate. -
Sayse about 8 yearsYou can use
prefetch_related
as per an answer I just made on a different question
-
-
Sayse about 8 yearsThis will still create n (5000) queries since it will retrieve the
entry
on every iteration. -
AKS about 8 yearsBut if you use
values_list
it will be efficient. Or, am I missing something withvalues_list
? -
Sayse about 8 yearsIt will be more efficient for a different reason, since it will be selecting the certain values instead of
*
-
AKS about 8 yearsAnd, isn't that what exactly the OP asked for :)
-
Sayse about 8 yearsIt results in a different query though since you no longer have the objects
-
AKS about 8 yearsI agree. And, I like your answer better. I Was just trying to provide an alternative way and limiting it to the example provided in the question.
-
John Waller about 8 yearsThis does not seem to run much faster... Am I missing something?
-
Sayse about 8 years@JohnWaller - I'm not sure, its possible that the time issue is no longer on a database side, but maybe just taking a while to print out the values or whatever the csv part of your question was referring to
-
Sayse about 8 years@JohnWaller - Of course, you are still trying to return quite a large number of records from your database so there must be some time involved there