Django: Retrieve a list of one model property for all distinct values of another property-CodePudding

I’d like to use the Django ORM to give me a list values of a model property for the subset of objects that have a distinct value of another property. Consider a simple model like:

class Result(models.Model):
    color = models.CharField()
    score = models.IntegerField()

And imagine that I have four results:

results = [
    Result(color="blue", score=5), 
    Result(color="blue", score=3), 
    Result(color="red", score=10), 
    Result(color="red", score=8),
]

What I'd like to get is something like the following:

{
    {"color”: "blue", "scores": [5, 3]}, 
    {"color": "red", "scores": [10, 8]},
}

I think it's possible to get there in one shot using Django's aggregation and annotation features, but I'm struggling to figure it out. I know I can get the average score with the following:

results.values("color").annotate(avg_score=Avg("score"))

If that's possible, then getting the list used to generate the average must also be possible, right?

CodePudding user response：

Unfortunatly, this will depend on you DB.
If you're using Postgresql you have access to ArrayAgg, so the following will work :

results.values("color").annotate(scores=ArrayAgg("score"))

You're using MySQL or MariaDB you can use GroupConcat.
But this won't yield an array, it will yield a string with values separated by commas.
If this field will be manipulated using Python, you can do the following to get an array:

my_result.scores.split(",")