Django: select multiple columns across different apps with the same foreign key-CodePudding

This is a follow-up question of this.

I have app1/models.py:

class A(models.Model):
    id = models.IntegerField(primary_key=True)
    text = models.CharField(max_length=20)

class B(models.Model):
    fid = models.ForeignKey(A, models.CASCADE)
    text = models.CharField(max_length=20)

class C(models.Model):
    fid = models.ForeignKey(A, models.CASCADE)
    text = models.CharField(max_length=20)

and app2/models.py:

from app1.models import A

class D(models.Model):
    fid = models.ForeignKey(A, models.CASCADE)
    text = models.CharField(max_length=20)

I wish to get A.text B.text C.text D.text where A.id == B.fid == C.fid == D.fid == 1. From the referred question, I was able to retrieve the first 3 columns using:

B.objects.filter(fid=1).values('text', 'fid__text', 'fid__c__text') # B.text, A.text, C.text

However I cannot get D.text using this query. I know I can do a filter on D and give it a manual calculation, but I'm looking forward to a more Djangoic way.

(In case of a multi-match, product the rows, so if there are 2 rows in B that matches the given fid and 3, 4 for C, D, a total of 24 rows in returned.)

For future visitors: both weAreStarsDust's and nigel222's answer are great and worth reading.

CodePudding user response：

I don't know why it doesn't work. Is it a database error? In which case it may be nothing to do with Django. Does it work if you replace fid__c__text with fid__d__text?

I would instinctively query on the A model instance, which is the 'parent' of the others. Having obtained the instance a, it offers b_set.all(), c_set.all(), and d_set.all(). For DB efficiency you can prefetch all the related objects with

a = A.objects.prefetch_related('b', 'c', 'd').get( pk=b.fid_id )

(Note, having prefetched, filter by iterating through a.b_set.all() not by applying a .filter(criteria) which will hit the DB again regardless of the prefetch).

CodePudding user response：

Try query from A model

A.objects.filter(id=1).values('text', 'b__text', 'c__text', 'd__text')

A.objects.filter(id=1).values('text', 'b_set__text', 'c_set__text', 'd_set__text')

More details about _set can be found in the RelatedManager docs