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)

0sVoid · Accepted Answer · 2022-08-17 17:02:17Z

3

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)

answered Aug 17, 2022 at 17:02

0sVoid

2,7881 gold badge13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

willeM_ Van Onsem · Accepted Answer · 2022-08-17 17:16:26Z

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.

Waldemar Podsiadło · Accepted Answer · 2022-08-17 17:02:25Z

2

I think it's goes like:

TeamMember.objects.filter(team__projectteam__project__id=1)

Django orm allow reverse foreginkey lookup

answered Aug 17, 2022 at 17:02

Waldemar Podsiadło

1,4122 gold badges7 silver badges13 bronze badges

Collectives™ on Stack Overflow

Django convert raw SQL query to Django ORM

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related