django query with manytomany as list-CodePudding

I'm working with django (4.1) and I have a problem with a QuerySet.

models.py

class Publication(models.Model):
    title = models.CharField(max_length=30)

    class Meta:
        ordering = ['title']

    def __str__(self):
        return self.title


class Article(models.Model):
    headline = models.CharField(max_length=100)
    publications = models.ManyToManyField(Publication)

    class Meta:
        ordering = ['headline']

    def __str__(self):
        return self.headline

python manage.py shell

>>> from many2many.models import Article, Publication
>>> qsA = Article.objects.values("id","headline","publications")
>>> qsP = Publication.objects.values("id","title")
>>> for a in qsA:
...     print(a)
... 
{'id': 1, 'headline': 'A', 'publications': 1}
{'id': 1, 'headline': 'A', 'publications': 2}
{'id': 2, 'headline': 'B', 'publications': 3}
{'id': 2, 'headline': 'B', 'publications': 4}
>>> for a in qsP:
...     print(a)
... 
{'id': 1, 'title': 'tA1'}
{'id': 2, 'title': 'tA2'}
{'id': 3, 'title': 'tB1'}
{'id': 4, 'title': 'tB2'}

>>>

I'd like to have a QuerySet that returns the "headline" of the Articles, and the list of the "title" of the Publication. Something like

{'headline': 'A', 'list_publications': 'tA1 tA2'}
{'headline': 'B', 'list_publications': 'tB1 tB2'}

CodePudding user response：

You can define related_name to easily access related model through the implicit relation.
To be clearer if you define related_name="articles" inside your M2M field publications, you'll be able to access articles related to publication by calling my_publication.articles.all()

To return titles associated with publications you can do:

Publication.objects.annotate(titles=___("articles__title"))

annotate() allows for a non table column to be added to your queryset (usually a related or calculated one).
titles is the name of this additional column.
___ is the name of the aggregation function in this case (we'll discuss it next).
"articles" is your related_name.
"title" is the column of the related model you want to aggregated on.

The above code is aggregating related titles for each publication.

So which aggregation function to use ? Here is the documentation https://docs.djangoproject.com/en/4.1/topics/db/aggregation/.
Nothing is useful for your issue inside, so you'll need to go over DB specific aggregation functions.

From the one I know:
If you're using Postgres there is ArrayAgg but this will output ["t1", "t2"].
https://docs.djangoproject.com/en/4.1/ref/contrib/postgres/aggregates/.
If you're using MariaDB or MySQL you can use GroupConcat.
https://django-mysql.readthedocs.io/en/latest/aggregates.html