Can I make a Django query that returns a list per item?

Question

I have 3 models like this:

class Person(models.Model):
    name = models.CharField(max_length=100)

class Place(models.Model):
    name = models.CharField(max_length=100)

# ManyToMany Through Table
class PersonPlace(models.Model):
    person = models.ForeignKey(Person, on_delete=models.CASCADE)
    place = models.ForeignKey(Place, on_delete=models.CASCADE)

PersonPlace links Person and Place in a ManyToMany relationship.

I want a database query that will give me a list of place id's per person (a list of places every person has visited).

Is it possible to make that aggregation through the ORM without having Python put this together?

Expected return is something like: {1: [4,5,6], 2: [1,2,5]}

The keys here are the user ids, and the values are the place ids each user has visited. Note: The result does NOT need to be a dict, but I would assume it would be something dict-like.

[python - How to query as GROUP BY in django? - Stack Overflow](https://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django) — furas, Jun 09 '22 at 13:27

score 2 · Accepted Answer · answered Jun 10 '22 at 00:42

If you are using postgres, you can make use of ArrayAgg..[Django-doc]:

from django.contrib.postgres.aggregates import ArrayAgg


for person in Person.objects.annotate(places=ArrayAgg("personplace__place")): 
    print(person.__dict__)

This gives an output of:

# ommitted unnecessary keys
{'id': 1, 'name': '1', 'places': ['2', '3']}
{'id': 2, 'name': '2', 'places': ['1', '2', '3']}

Under the hood, this is all done on your database with the following query:

SELECT 
    "person"."id", 
    "person"."name", 
    ARRAY_AGG("personplace"."place_id" ) AS "places" 
FROM 
    "person" 
LEFT OUTER JOIN 
    "personplace" 
ON 
    ("person"."id" = "personplace"."person_id") 
GROUP BY 
    "person"."id"

This looks perfect! I actually do use PostgreSQL, so this should work great! Out of curiosity - if we needed to do this on other databases, would this require writing some sort of long query manually? — John, Jun 10 '22 at 12:50
Haven't worked with other rdbms for a while so not entirely sure — Brian Destura, Jun 12 '22 at 06:48

score 0 · Answer 2 · edited Jun 09 '22 at 14:14

0

this will return a list of places for one person

person_object = Person.objects.get(name="admin")
place_objects_ids_query_set = PersonPlace.objects.filter(person=person_object).values_list('place', flat=True)
place_objects_ids_list = list(place_objects_ids_query_set)
print(place_objects_ids_list)

in case you need a list of places for many persons

persons_objects = Person.objects.all()
place_objects_ids_query_set = PersonPlace.objects.filter(person__in=persons_objects).values_list('place', flat=True)
place_objects_ids_list = list(place_objects_ids_query_set)
print(place_objects_ids_list)

edited Jun 09 '22 at 14:14

John

2,551
3
30
55

answered Jun 09 '22 at 13:27

oruchkin

1,145
1
10
21

The first part - a list of places for one person - is pretty simple. The second part is also pretty simple. What I'm looking for is a bit more complex. It's basically a dictionary of persons, and for each person, a list of places they've been to. – John Jun 09 '22 at 14:09
i think you may use django rest framework for this task, it may return dict, and it will be processed fast – oruchkin Jun 09 '22 at 14:22
1

Seems like you want DRF Serializers – Bartosz Stasiak Jun 09 '22 at 14:24
The return type isn't the point. It doesn't need to be a dict. I need the database query to return a list of users, and the places each user has visited. I'm sure I could do separate queries, and stitch them together with Python, but that's inefficient for large datasets. – John Jun 09 '22 at 14:45

Can I make a Django query that returns a list per item?

2 Answers2