Doing select_related() twice - using the output queryset-CodePudding

I am trying to join two models together.

Project has an FK to org (orgid) People has an FK to org (orgid)

But, I want the result of org project people,

So I tried to do select_related() and then select_related on the outcome to join the new queryset to the other model.

It didn't work :(

a = people.objects.filter(personid=user, status='approved').select_related('orgid')
b = a.select_related('projectid')

I need the join of these two essentially

a = people.objects.filter(personid=user, status='approved').select_related('orgid')
b = project.objects.filter(orgid__in=t, orgid=orgid).select_related('orgid')

In SQL this would be simply

SELECT * from(
SELECT app_org.orgid as orgid, orgname, username, role from app_org
LEFT JOIN app_people on app_people.orgid = app_org.orgid)x
LEFT JOIN app_project in x.orgid = app_project.orgid

Can anyone please help me?

I also tried

a = people.objects.filter(personid=user, status='approved').select_related('orgid__projectid')

CodePudding user response：

I don't know your model, but i think it should works:

query = people.objects.filter(personid=user, status='approved').select_related('org','org__project')

CodePudding user response：

The relationship you're following, from org_id to a Project, is a reverse foreign key, so you'll need to use prefetch_related, with the suffix _set unless you have a related name on the relationship.

a = (people.objects
    .filter(personid=user, status='approved')
    .select_related('orgid')
    .prefetech_related('orgid__project_set')
    )