One to many relationship query in Django ORM, Where I want to query on One table and want to get output with many table data as a list

huangapple
go评论91阅读模式
英文:

One to many relationship query in Django ORM, Where I want to query on One table and want to get output with many table data as a list

问题

  1. from django.db.models import F
  2. from django.core import serializers
  4. # Your models and fields definitions go here
  6. # Use Django ORM to query the data and serialize it
  7. publications_with_books = Publication.objects.annotate(
  8.     book_id=F('book__id'),
  9.     book_name=F('book__name')
  10. ).values('id', 'name', 'book_id', 'book_name').order_by('id')
  12. # Serialize the data into JSON format
  13. json_data = serializers.serialize('json', publications_with_books)
  15. # Your JSON data is ready, you can use it as needed
  16. json_data
英文:

Here is my model:

  1. from django.db import models
  2. from django.db.models.expressions import F
  5. class Publication(models.Model):
  6.     name = models.CharField('name of publication', max_length=1024, null=True, blank=True) 
  8.     def __str__(self):
  10.         return f'{self.name}' 
  12.     class Meta:
  13.         verbose_name = "Publication"
  14.         verbose_name_plural = "Publications" 
  17. class Book(models.Model):
  18.     name = models.CharField('name of book', max_length=1024, null=True, blank=True) 
  19.     publication = models.ForeignKey(Publication, on_delete=models.CASCADE, related_name='books_publications', null=True, blank=True)
  21.     def __str__(self):
  23.         return f'{self.name}//{self.publication.name}'
  25.     class Meta:
  26.         verbose_name = "Book"
  27.         verbose_name_plural = "Book"

I want outcome json as single publication with list of book (Please show me using Django ORM query and Django Serializer OR using DRF):

Want Output JSON like below:

  1. [
  2.     {
  3.         "id": 1,
  4.         "name": "publication 001",
  5.         "book": [
  6.             {
  7.                 "id": 1,
  8.                 "name": "Book 001"
  9.             },
  10.             {
  11.                 "id": 2,
  12.                 "name": "Book 002"
  13.             }
  14.         ]
  15.     },
  16.     {
  17.         "id": 2,
  18.         "name": "publication 002",
  19.         "book": [
  20.             {
  21.                 "id": 3,
  22.                 "name": "Book 003"
  23.             },
  24.             {
  25.                 "id": 4,
  26.                 "name": "Book 004"
  27.             }
  28.         ]
  29.     }
  30. ]

I tried it through several way but unable to solve it using django orm query. I've done it through raw query. But I wantto implement it through Django ORM Query. SO please help me. TIA

答案1

得分: 0

以下是使用简单的序列化器完成此任务的代码部分：

  1. class BookSerializer(serializers.Serializer):
  2.     id = serializers.IntegerField()
  3.     name = serializers.CharField()
  4.    
  6. class PublicationSerializer(serializers.Serializer):
  7.     id = serializers.IntegerField()
  8.     name = serializers.CharField()
  9.     book = BookSerializer(source="books_publications.all", many=True)
  11. qs = Publication.objects.all()
  12. data = PublicationSerializer(qs, many=True).data

source="book_set" 允许将对象字段映射到不同的序列化器字段。
book_set 是Django提供的默认 related_name。这意味着 my_publication.book_set.all() 等同于 Book.objects.filter(publication=my_publication)。
您应该考虑定义自己的 related_name。

  1. publication = models.ForeignKey(Publication, on_delete=models.CASCADE, null=True, blank=True, related_name="books")

请注意，上述代码将导致对数据库的 N+1 查询。一个用于获取所有出版物，一个用于每个出版物获取相关书籍。
您可以使用 prefetch_related 来减少查询的数量至两次。

  1. qs = Publication.objects.all()
  2. qs = qs.prefetch_related("books_publications")
  3. data = PublicationSerializer(qs, many=True).data

或者，您可以使用 ModelSerializer：

  1. class BookSerializer(serializers.ModelSerializer):
  2.     class Meta:
  3.         model = Book
  4.         fields = ("id", "name")
  7. class PublicationSerializer(serializers.ModelSerializer):
  8.     book = BookSerializer(source="books_publications.all", many=True)
  10.     class Meta:
  11.         model = Publication
  12.         fields = ("id", "name", "book")
英文:

The following should do the trick using simple serializers

  1. class BookSerializer(serializers.Serializer):
  2.     id = serializers.IntegerField()
  3.     name = serializer.CharField()
  4.    
  6. class PublicationSerializer(serializers.Serializer):
  7.     id = serializers.IntegerField()
  8.     name = serializers.CharField()
  9.     book = BookSerializer(source="books_publications.all", many=True)
  11. qs = Publication.objects.all()
  12. data = PublicationSerializer(qs, many=True).data

source="book_set" allows for an object field to be mapped to a different serializer field.
book_set is the default related_name provided by Django. This means that
my_publication.book_set.all() is equivalent to Book.objects.filter(publication=my_publication).
You should consider defining your own related_name.

  1. publication = models.ForeignKey(Publication, on_delete=models.CASCADE, null=True, blank=True, related_name="books")

Note that the above code will result in N+1 queries to the database. A first one to get all publications and one for each publication to get related books.
You can and should use prefetch_related to reduce the number of query to two.

  1. qs = Publication.objects.all()
  2. qs = qs.prefetch_related("books_publications")
  3. data = PublicationSerializer(qs, many=True).data

Alternatively you could use ModelSerializer

  1. class BookSerializer(serializers.ModelSerializer):
  2.     class Meta:
  3.         model = Book
  4.         fields = ("id", "name")
  7. class PublicationSerializer(serializers.ModelSerializer):
  8.     book = BookSerializer(source="books_publications.all", many=True)
  10.     class Meta:
  11.         model = Publication
  12.         fields = ("id", "name", "book")

huangapple
  • 本文由 发表于 2023年5月25日 11:52:14
  • 转载请务必保留本文链接：https://go.coder-hub.com/76328814.html
  • django
  • django-orm
  • django-rest-framework
  • django-serializer
  • python-3.x
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定