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

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.

CodePudding user response：

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)

CodePudding user response：

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"