Django convert raw SQL query to Django ORM

Question

I am using Django Rest Framework and I have this query in raw SQL but I want to do it in the Django ORM instead. I have tried using the different Django tools but so far it has not given me the expected result.

select tt.id, tt.team_id, tt.team_role_id, tt.user_id  from task_teammember tt 
inner join task_projectteam tp on tp.team_id = tt.team_id 
where tp.project_id = 1

models

class TeamMember(models.Model):
    user       = models.ForeignKey(User, on_delete=models.CASCADE)
    team       = models.ForeignKey(Team, on_delete=models.CASCADE)
    team_role  = models.ForeignKey(TeamRole,on_delete=models.CASCADE)
    state      = models.IntegerField(default=1)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(default=None, null=True)


class ProjectTeam(models.Model):
    project    = models.ForeignKey(Project, on_delete=models.CASCADE, blank=True, null=True)
    team       = models.ForeignKey(Team, on_delete=models.CASCADE, blank=True, null=True)
    state      = models.IntegerField(default=1)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(default=None, null=True)

Answer 1

If you have a project object already then this should get you what I believe you want. Your TeamMember model has access to Team, which links to ProjectTeam, and to Project - the double-underscore accessor navigates through the relationships.

TeamMember.objects.filter(team__projectteam__project=project)