Select Objects related in third table-CodePudding

Say I have the models

class A(models.Model):

class B(models.Model):
   
class C(models.model):
    b = models.ForeignKey(B)

class D(models.Model):
    c = models.ForeignKey(C)
    a = models.ForeignKey(A)

What would a ORM query look like to select all Bs that are related to C's that are related to a specific A through table D?

CodePudding user response：

As mentioned by the comment on your post you can use the autogenerated related name. But it never hurts to set it yourself.

class C(models.model):
    b = models.ForeignKey(B, related_name="c")

class D(models.Model):
    c = models.ForeignKey(C, related_name="d")
    a = models.ForeignKey(A, related_name="d")

Then:

B.objects.filter(c__d__a=specific_a_obj)

CodePudding user response：

OK that's easy: you should use related_name on C table like this:

class C (models.Model):
    title = models.CharField(blank=True, null=True, max_length=255)
    b = models.ForeignKey(B, null=True, blank=True,
                      on_delete=models.CASCADE, related_name='c_related')

related_name: this able to reverse relationship.

Get C information through B table

after that you cas set B Model serialzier Like this to get related C info:

class B_Serilaizer(serializers.ModelSerializer):

    c_related = C_Serilaizer(many=True, read_only=True)

    class Meta:
        model = B
        fields = ('id', 'title', 'c_related')
        read_only_fields = ('id',)

First you should select all C table records Id who related with specific A object From Table D like this:

c_ids = D.objects.values_list(
            'c__id', flat=True).filter(a__id="specific A object id palced here")

Finally you can get all B records according with C table who related with specific A object like this:

records = B.objects.filter(
            c_related__id__in=c_ids).distinct()

final code:

    c_ids = D.objects.values_list(
            'c__id', flat=True).filter(a__id="specific A object id palced here")
    records = B.objects.filter(
            c_related__id__in=c_ids).distinct()

hope be useful.