CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-orm
Theesh
Theesh

Reputation: 339

How to perform annotation in Django model with a many to many relationship?

I have two Django models called Restaurant and RestauranCategory. The Restaurant model has a many-to-many relationship to RestaurantCategory. Please refer to the attached sample model instances.

class RestaurantCategory(BaseModel):
   name = models.CharField(max_length=150)
   description = models.TextField()

class Restaurant(BaseModel):
    name = models.CharField(db_index=True, max_length=128)
    is_partner = models.BooleanField(default=True)
    category = models.ManyToManyField(
      RestaurantCategory,
      related_name="restaurant_categories",
      blank=True,
      db_index=True,
    )

Now what I need to do is to perform an annotation from RestaurantCategory to have a new column called restaurant_names and assign a single string that contains all the related restaurants' names. I have added a similar thing that I need to do,

new_data = RestaurantCategory.objects.all().annotate(
  restaurant_names=<should contain all the restaurant names in a single string>
)

Is that possible to achieve with annotating? If not what are the options that we can use? I have gone through this StackOverflow question and wasn't able to good idea regarding my issue.

Upvotes: 2

Views: 1073

Answers (1)

YellowShark
YellowShark

Reputation: 2269

As @Truegopnik stated, you need to use StringAgg, along with an F-expression. The StringAgg docs are a little weak though, and don't contain any examples. Here's how you would do it with your models:

from django.contrib.postgres.aggregates import StringAgg
from django.db.models import F

new_data = RestaurantCategory.objects.annotate(
    restaurant_names=StringAgg(F('restaurant_categories__name'), ', ')
)

Upvotes: 4

Related Questions