Django filter records by field from another table-CodePudding

I have a "to do list" website with three tables (TDLists, Tasks, Users) and I want to display all the to do lists for a specific user with all of the tasks that are linked to that specific list. I am having trouble filtering these items in my views.py file. I have been able to get my desired results using SQL but cannot translate this into django.

models.py

class Users(models.Model):
    username = models.CharField(max_length=20)
    email = models.CharField(max_length=40)
    password = models.CharField(max_length=16)
    user_id = models.AutoField(primary_key=True)

class TDLists(models.Model):
    title = models.CharField(max_length=50)
    date_created = models.DateField(auto_now_add=True)
    completed = models.BooleanField(default=False)
    deadline_date = models.DateField(null=True)
    user_id = models.ForeignKey(Users, on_delete=models.CASCADE)
    list_id = models.AutoField(primary_key=True)

class Tasks(models.Model):
    description = models.CharField(max_length=120)
    priority = models.CharField(choices=(
        ("High", "high"),
        ("Medium", "medium"),
        ("Low", "low"),
    ),max_length=10)
    list_id = models.ForeignKey(TDLists, on_delete=models.CASCADE)
    task_id = models.AutoField(primary_key=True)

SQL code that works:

SELECT myApp_tasks.*
FROM myApp_tasks, myApp_tdlists, myApp_users
WHERE myApp_tdlists.user_id_id = myApp_users.user_id
AND myApp_tasks.list_id_id = myApp_tdlists.list_id
AND myApp_users.user_id = 1

output:

code tried in views.py

user_id = 1

user = Users.objects.filter(user_id=user_id)
lists = TDLists.objects.filter(user_id=user.get(user_id=user_id))
tasks = Tasks.objects.filter(list_id=lists.get(list_id=lists.list_id))

CodePudding user response：

You don't need to use filter() for most of what you're trying to do.

Firstly, you shouldn't use filter if you want to get a single item by ID, instead use get() - although be aware that this will raise an exception if Django doesn't find a match, and you need to handle it (either through try/except or the get_object_or_404 Django shortcut):

user = Users.objects.get(user_id=user_id)

Secondly, because of your foreign key relations, you can access all lists with the _set accessor:

lists = user.tdlists_set.all()

Unfortunately, you can't then add tasks_set to this without using a for-loop to access each list individually. You will need to user a filter, but you can still benefit from the accessor to make the query quite simple:

tasks = Task.objects.filter(list_id__user_id=user) # note the double underscore

As an aside, the recommended model naming convention in Django is singular rather than plural - for example, Task instead of Tasks.