Django complex query from relational model

Question

Using django-rest-framework, I have simple model

There is a Book which is getting translated, This book has chapters (each chapter has a name), each chapter has paragraphs. The Translation table contains foreign key to the Chapter(for chapter name) and paragraph range (from-to) because each translator can translate set of paragraphs in one translation, and the translation set can be out of order, but no overlap, for example a Translator1 can translate Chapter 3 paragraph 4 to 10, Translator2 can translate Chapter3 paragraph 20 to 30 but Translator3 cannot translate Chapter3 paragraph 9 to 20 because it leads to duplicate translation. for now this is done manually, I don't know how to set a constraint to prevent it.

class Chapters(models.Model):
    id = models.PositiveIntegerField(primary_key=True)
    name_fr = models.CharField(max_length=45, db_collation='utf8_unicode_ci')
    name_en = models.CharField(max_length=50, db_collation='utf8_unicode_ci', blank=True, null=True)

    def __str__(self):
        return self.name_fr


class TheBook(models.Model):
    id = models.AutoField(primary_key=True)  # Field name made lowercase.
    chapterid = models.OneToOneField(Chapters,on_delete=models.PROTECT)  # Field name made lowercase.
    paragraphID = models.IntegerField()  # Field name made lowercase.
    paragraphText = models.TextField(db_collation='utf8_unicode_ci')  # Field name made lowercase.
    
    def __str__(self):
        return self.paragraphText


class Translate(models.Model):
    id = models.AutoField(primary_key=True)
    chapterid = models.ForeignKey(Chapters, to_field='id',on_delete=models.SET_NULL,null=True)
    parag_from = models.IntegerField(blank=True, null=True)
    parag_to = models.IntegerField(blank=True, null=True)
    translate = models.TextField(db_collation='utf8_unicode_ci', blank=True, null=True)

    def __str__(self):
        return ("{}: {} - {}").format(self.chapter, self.parag_from ,self.parag_to)

Now my problem is selecting all the paragraphs that have translation, then the user clicks on the group of the translated paragraphs and reads the translation of that group


class ParagraphList(APIView):
    def get(self, request, chapterName):

        # Get a list of paragraphs that has translation
        ParagraphList = Translate.objects.filter(
            chapter__name_en=chapterName
        ).values_list(
            'parag_from', 'parag_to', named=True
        ).order_by('parag_from')

        # variable to store the paragraph text
        ParagraphText = []

        # Loop through the List, and query the Book for the paragraphs in the 
        # range of parag_from to parag_to

        for i in range(len(ParagraphList)):
            ParagraphText.extend(list(
                Book.objects.filter(chapter__name_en=chapterName).filter(
                    paragraphID__range=list(ParagraphList[i]))
            ))

        serialized = BookParagSerializer(ParagraphText, many=True)
        return Response(serialized.data)

There is two problems with this code:

it's not efficient. I have to send multiple queries to the database to get the data
the returned data is like a list:

[
 {
   "paragraphID": 1,
   "paragraphText":"Lorem ipsum dolor sit amet consectetur."
 },
 {
   "paragraphID": 2,
   "paragraphText":"Lorem ipsum dolor sit amet consectetur."
 },
 {
   "paragraphID": 3,
   "paragraphText":"Lorem ipsum dolor sit amet consectetur."
 },
....
]

I can't group them by translation. something like this:

    [
    {
      "group":1,
      "parag_from":1,
      "parag_to":3,
      "translation_id":124,
      "context":[
                {
                  "paragraphID": 1,
                  "paragraphText":"Lorem ipsum dolor sit amet consectetur."
                },
                {
                  "paragraphID": 2,
                  "paragraphText":"Lorem ipsum dolor sit amet consectetur."
                },
                {
                  "paragraphID": 3,
                  "paragraphText":"Lorem ipsum dolor sit amet consectetur."
                },
      ]
    },
    {
      "group":2,
      "parag_from":9,
      "parag_to":11,
      "translation_id":321,
      "context":[
                {
                  "paragraphID": 9,
                  "paragraphText":"Lorem ipsum dolor sit amet consectetur."
                },
                {
                  "paragraphID": 10,
                  "paragraphText":"Lorem ipsum dolor sit amet consectetur."
                },
                {
                  "paragraphID": 11,
                  "paragraphText":"Lorem ipsum dolor sit amet consectetur."
                },
      ]
    },
    ...
]

In the serializer, I can't have multiple models, and this "group"ing I'm not sure how to do it.

I appreciate any suggestion.

Django complex query from relational model

0 Answers