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)

CodePudding user response：

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)

CodePudding user response：

I would advise to span a ManyToManyField over the ProjectTeam, this will make queries simpler:

from django.conf import settings


class TeamMember(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE)
    updated_at = models.DateTimeField(auto_now=True)
    # …

class Team(models.Model):
    projects = models.ManyToManyField(Project, through='ProjectTeam')
    # …


class ProjectTeam(models.Model):
    # …
    updated_at = models.DateTimeField(auto_now=True)

Then you can easily filter with:

TeamMember.objects.filter(team__projects=project_id)

Note: It is normally better to make use of the settings.AUTH_USER_MODEL ^[Django-doc] to refer to the user model, than to use the User model ^[Django-doc] directly. For more information you can see the referencing the User model section of the documentation.

Note: Django's DateTimeField ^[Django-doc] has a auto_now=… parameter ^[Django-doc] to work with timestamps. This will automatically assign the current datetime when updating the object, and mark it as non-editable (editable=False), such that it does not appear in ModelForms by default.

CodePudding user response：

I think it's goes like:

TeamMember.objects.filter(team__projectteam__project__id=1)

Django orm allow reverse foreginkey lookup