django query annotate values get list from reverse foreign key

Question

I have a simple model like

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

    def __str__(self):
        return self.name



class Blog(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)

Here I want to queryall authors with the title of all the blogs they have written like

Author One : [Blog One, Blog Two, Blog Three]

I want this from query and not loop

Here my approach is to use subquery like

blogs =Blog.objects.filter(author=OuterRef('pk')).values("title")
authors = Author.objects.annotate(blogs=Subquery(blogs), output_field=CharField())

But here I am getting error like sub-select returns 2 columns - expected 1

How can I get all the authors with all the blogs they have written ? I do not want to use loop. I want this through query

Eventually it will go through a loop that will make a list. Usually it is not a good idea to do string processing in the database. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Feb 28, 2022 at 16:26
@WillemVanOnsem its ok if not string. I works for me if it is list. Updated the question — Green Panda
– Green Panda, Commented Feb 28, 2022 at 16:35
but the same comment holds for a list or any other collection. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Feb 28, 2022 at 16:36

Ersain · Accepted Answer · 2022-02-28 16:55:01Z

4

If you are using a postgres as a database, then you can use an ArrayAgg function:

from django.contrib.postgres.aggregates import ArrayAgg

authors = Author.objects.annotate(blogs=ArrayAgg('blog_set__title'))

answered Feb 28, 2022 at 16:55

Ersain

1,5001 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Green Panda Over a year ago

great.. just the thing is you can only do ArrayAgg('blog__title')).. you dont need set here.. thank you

Ersain Over a year ago

@GreenPanda hi, mark the post as resolved, if it really helped you, thanks

willeM_ Van Onsem · Accepted Answer · 2022-02-28 16:26:07Z

2

You can query with:

authors = Author.objects.prefetch_related('blog_set')

Then in the template, you can render with:

<ul>
  {% for author in authors %}
    <li>{{ author }}</li>
    <ul>
      {% for blog in author.blog_set.all %}
        <li>{{ blog }}</li>
      {% endfor %}
    </ul>
  {% endfor %}
</ul>

answered Feb 28, 2022 at 16:26

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

1 Comment

Green Panda Over a year ago

I dont need this in template.. I just want the result of query in list

Collectives™ on Stack Overflow

django query annotate values get list from reverse foreign key

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related