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.
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
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))
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