CODEWITHSUNDEEP
pythondjango
hadNoIdea
hadNoIdea

Reputation: 71

Django prefetch through table

I have a Django model which has a manyToMany relation with an additional column:

class Field(models.Model):
    table = models.ForeignKey(
        Table, on_delete=models.CASCADE,
        verbose_name=_(
            u"Table"), related_name="fields"
    )
    supportedLanguages = models.ManyToManyField(
        Language,
        through='FieldsLanguages',
        ('field', 'language'),
        verbose_name=_(
            u"Supportedlanguage"),
        help_text=_(
            u"The language that the field supports (must be a language supported by the table.")
    )

Here is my through table:

class FieldsLanguages(models.Model):
    field = models.ForeignKey(Field, on_delete=models.CASCADE)
    language = models.ForeignKey(Language, on_delete=models.CASCADE)
    defaultValue = models.CharField(
        max_length=255, blank=True, null=True, verbose_name=_(u"Default value"),
        help_text=_(
            u"The default value of this field in this language (Overrides the default value of the field).")
    )

I would like to retrieve each defaultValue from the through table and its language in one query.

I thought this would do the job:

table = Table.objects.get(pk=1)
fields = list(table.fields.prefetch_related("supportedLanguages").all().order_by("position"))

But unfortunately, it doesn't get me the defaultValue of each relation but only its Language. It could be done in the same query as the query goes already through FieldsLanguages to retrieve the Foreign Keys.

How can I accomplish this?

Upvotes: 4

Views: 3930

Answers (2)

hadNoIdea
hadNoIdea

Reputation: 71

It wasn't really what I was lookig for but it helped me to build my query:

table = Table.objects.get(pk=1)
fields = table.fields.prefetch_related("fieldslanguages_set", "fieldslanguages_set__language").order_by("position")
for field in fields:
    for fl in field.fieldslanguages_set.all():
        print(fl.defaultValue, fl.language)

The problem with Kevin Christopher Henry's answer is that it will query the database for each field in the for-loop. Here it will send only 4 queries.

Upvotes: 3

Kevin Christopher Henry
Kevin Christopher Henry

Reputation: 49052

The through table is a table like any other, which means you can reference it through the related names of its ForeignKeys.

table = Table.objects.get(pk=1)
fields = table.fields.prefetch_related("fieldslanguages__language")
                     .order_by("position"))

for field in fields:
    for fl in field.fieldslanguages_set.all():
        print(fl.default, fl.language)

Upvotes: 1

Related Questions