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评论63阅读模式
英文:

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

问题

from django.db.models import F
from django.core import serializers

# Your models and fields definitions go here

# Use Django ORM to query the data and serialize it
publications_with_books = Publication.objects.annotate(
    book_id=F('book__id'),
    book_name=F('book__name')
).values('id', 'name', 'book_id', 'book_name').order_by('id')

# Serialize the data into JSON format
json_data = serializers.serialize('json', publications_with_books)

# Your JSON data is ready, you can use it as needed
json_data
英文:

Here is my model:

from django.db import models
from django.db.models.expressions import F


class Publication(models.Model):
    name = models.CharField('name of publication', max_length=1024, null=True, blank=True) 

    def __str__(self):

        return f'{self.name}' 

    class Meta:
        verbose_name = "Publication"
        verbose_name_plural = "Publications" 


class Book(models.Model):
    name = models.CharField('name of book', max_length=1024, null=True, blank=True) 
    publication = models.ForeignKey(Publication, on_delete=models.CASCADE, related_name='books_publications', null=True, blank=True)

    def __str__(self):

        return f'{self.name}//{self.publication.name}'

    class Meta:
        verbose_name = "Book"
        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:

[
    {
        "id": 1,
        "name": "publication 001",
        "book": [
            {
                "id": 1,
                "name": "Book 001"
            },
            {
                "id": 2,
                "name": "Book 002"
            }
        ]
    },
    {
        "id": 2,
        "name": "publication 002",
        "book": [
            {
                "id": 3,
                "name": "Book 003"
            },
            {
                "id": 4,
                "name": "Book 004"
            }
        ]
    }
]

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

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

class BookSerializer(serializers.Serializer):
    id = serializers.IntegerField()
    name = serializers.CharField()
   

class PublicationSerializer(serializers.Serializer):
    id = serializers.IntegerField()
    name = serializers.CharField()
    book = BookSerializer(source="books_publications.all", many=True)

qs = Publication.objects.all()
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

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

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

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

或者,您可以使用 ModelSerializer

class BookSerializer(serializers.ModelSerializer):
    class Meta:
        model = Book
        fields = ("id", "name")


class PublicationSerializer(serializers.ModelSerializer):
    book = BookSerializer(source="books_publications.all", many=True)

    class Meta:
        model = Publication
        fields = ("id", "name", "book")
英文:

The following should do the trick using simple serializers

class BookSerializer(serializers.Serializer):
    id = serializers.IntegerField()
    name = serializer.CharField()
   

class PublicationSerializer(serializers.Serializer):
    id = serializers.IntegerField()
    name = serializers.CharField()
    book = BookSerializer(source="books_publications.all", many=True)

qs = Publication.objects.all()
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.

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.

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

Alternatively you could use ModelSerializer

class BookSerializer(serializers.ModelSerializer):
    class Meta:
        model = Book
        fields = ("id", "name")


class PublicationSerializer(serializers.ModelSerializer):
    book = BookSerializer(source="books_publications.all", many=True)

    class Meta:
        model = Publication
        fields = ("id", "name", "book")

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

发表评论

匿名网友

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

确定