Get multiple rows with one query in django?

Question

How can I get build a QuerySet that gets multiple rows from django? I thought filter() would work, but it seems to be worse off.

For example, I have two rows in the model Car, with two text attributes (license and vin). Now say I want to print the licenses and vins from these cars. How can I do that with one database call?

Here's an answer that will make two database calls:

#using get(), two total queries a = Car.objects.get(id=1) #query here b = Car.objects.get(id=2) #query here print(a.license + a.vin) #no query print(b.license + b.vin) #no query

That obviously didn't work because I made two get() queries. So next I'll try filter():

#using filter(), four total queries c = Car.objects.filter(id__in=(1,2)) #no query print(c[0].license + c[0].vin) #two queries print(c[1].license + c[1].vin) #two queries

Hmmm, that's weird, why is making four database calls? Is there a way I can make it get the two in one database call?

bradley.ayers · Accepted Answer · 2011-06-14 01:32:15Z

43

It's seems weird because of how indexing into a queryset works.

c = list(Car.objects.filter(id__in=(1,2))) # query print(c[0].license + c[0].vin) #no query print(c[1].license + c[1].vin) #no query

If you do the following, you'll only have one query too:

for car in Car.objects.filter(id__in=(1,2)): print(car.license + car.vin)

As @Torsten said, in your situation it appears like you're simply trying to get all the cars you've created. This can be achieved via the all() method:

for car in Car.objects.all(): print(car.license + car.vin)

edited Jun 14, 2011 at 1:32

answered Jun 14, 2011 at 0:49

bradley.ayers

38.5k14 gold badges98 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user749618 Over a year ago

Well that is silly. All I needed to do was put a list() in front of it? Crazy. Thanks! For extra karma: Can anyone explain why list() is needed and why it doesn't make the query to start?

bradley.ayers Over a year ago

Well when you index into a queryset, it thinks you just want a single item from that queryset, so it doesn't perform a query for the entire thing, it just does a single query for the single item you want. But yes, I can see how it would seem pretty weird :(

Torsten Engelbrecht Over a year ago

Also you might consider Car.objects.all() instead of filter to get all the objects from the database.

Eray Erdin Over a year ago

The only unclear thing here is that if the result is sorted or not. I mean, if I give the id__ins in (3,1,2) order, do I get it in this exact order or standard ascending ids?

belkka Over a year ago

@ErayErdin no, order is not preserved. Django's "in" lookup transforms to SQL "WHERE ... IN ..." clause.

Streamweaver · Accepted Answer · 2011-06-14 01:30:30Z

Great example. A typo I think though in your last codeblock. Should be:

for car in Car.objects.filter(id__in=(1,2)): print(car.license + car.vin)

How does that method stack up

Collectives™ on Stack Overflow

Get multiple rows with one query in django?

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related