CODEWITHSUNDEEP
djangodjango-modelsdjango-database
Simen Russnes
Simen Russnes

Reputation: 2260

Django filter queryset on list of "lists"

Excuse the phrasing of the title, perhaps it's not the correct way of saying it but I can't come up with a better one.

I have a model called People, and through some means of identifying a bunch of people I want to fetch from the database by knowing their Town, Street, and House, and then query the database for them.

class People(models.Model):
    town = models.ForeignKey(Town, on_delete=models.CASCADE)
    street = models.ForeignKey(Street, on_delete=models.CASCADE)
    house = models.ForeignKey(House, on_delete=models.CASCADE)

I could fetch them one by one as below using People.objects.get(...), however when I need to access many it puts a lag on due to the opening and closing of db connections.

result = People.objects.get(town_id=tid, street_id=sid, house_id=hid)

If I had a list of queries as given below, representing tid sid and hid, could I somehow do this all in one single database transaction? This list could easily grow to be 1000 entries or more.

queries = [
    [1, 1, 1]
    [1, 1, 2]
    [2, 1, 1]
    [5, 9, 1]
    [13, 40, 2]
]

Upvotes: 0

Views: 1233

Answers (1)

dirkgroten
dirkgroten

Reputation: 20672

If you need to query for the exact combinations, you would need to first annotate your queryset to construct a concatenation of the three ids:

from django.db.models.functions import Cast, Concat
from django.db.models import CharField, Value

queries = [",".join(elem) for elem in queries]
People.objects.annotate(address=Concat(
        Cast('town_id', output_field=CharField()), Value(','),
        Cast('street_id', output_field=CharField()), Value(','),
        Cast('house_id', output_field=CharField()
    )).filter(address__in=queries)

Upvotes: 2

Related Questions