CODEWITHSUNDEEP
pythondjangodjango-models
Desiigner
Desiigner

Reputation: 2316

Django ORM fetch data by 2 levels of foreign key relation

I have the following models:

class Project(models.Model):
     name = models.CharField(max_length=300, unique=True)
     description = models.CharField(max_length=2000)

class TemporaryUser(models.Model):
    username = models.CharField(max_length=400)
    project = models.ForeignKey(
        Project,
        on_delete=models.CASCADE,
        related_name='users'
    )

class QuestionSession(models.Model):
    project = models.ForeignKey(
        Project,
        on_delete=models.CASCADE,
        related_name='sessions',
        blank=True,
        null=True,
        default=None
    )

class Question(models.Model):
    # stores the main json object with all required information
    description = JSONField(
        max_length=10000, blank=True, null=True, default=None
    )

    question_session = models.ForeignKey(
        QuestionSession,
        on_delete=models.CASCADE,
        related_name='questions',
        blank=True,
        null=True,
        default=None
    )

class Answer(models.Model):
    question = models.ForeignKey(
        Question,
        related_name='answers_list',
        on_delete=models.CASCADE)

    answer = models.CharField(max_length=500)
    answered_by = models.ForeignKey(
        TemporaryUser,
        on_delete=models.CASCADE,
        related_name='answers',
        blank=True,
        null=True,
        default=None
    )

In a nutshell, my app contains questions, session is a collection of questions, and a project is a collection of sessions. All users are unique per project.

I can fetch all users and all answers within a specific project with the following:

TemporaryUser.objects.all().filter(project__id=project_id)

How can I do the same within a session? I don't really know how to do it, I need to filter users by session, is there a way how to do it with my relations?

Upvotes: 0

Views: 770

Answers (3)

Netizen29
Netizen29

Reputation: 226

If you want to fetch the users with only one query, the way to go is:

users = TemporaryUser.objects.filter(project__sessions=id)

However, if you want to fetch more data related to that session, maybe you should consider to fetch from the session itself (see Following relationships backward). Be cautious, as the number of queries to the database is not optimized.

session = Session.objects.get(pk=id)
users = session.project.users.all()
questions = session.questions.all()

You can use select_related and prefetch_related if you want to make less queries. This could be very important if you are interested in fetching data for a list of sessions and not only one.

Upvotes: 0

ruddra
ruddra

Reputation: 51988

I think its cleaner to use reverse relation here:

session = QuestionSession.objects.first()
session.project.users.all()
# as User model has a FK with Project , and it has related_name="users"

You can use this in your template as well:

{% for qs in questionsessions %}  // questionsessions is the queryset of QuestionSession
     {% for user in qs.project.users.all %}
            {{ user.username }}
     {% endfor %}
{% endfor %}

Upvotes: 0

Nidal
Nidal

Reputation: 415

Do you mean like: 

TemporaryUser.objects.filter(project__sessions__id=id)

Upvotes: 2

Related Questions