Filter query through an ID in Django

Question

I know this is a common to ask but I'm just confused, what is the best way to filter data from one table to another table, the case is I want to filter the name of category1 and category2 table through an ID based on the image shown below, It's very helpful for me to use this kind of implementation.

It same like this

Select title ,description, category1_tbl.name, category2_tbl.name from main_tbl....... JOIN TWO TABLES 

views.py

task_tbl.objects.get(category1_id=category1_tbl.objects.get(id))

models

class main_tbl(models.Model):
  category1 = models.ForeignKey(category1_tbl, models.DO_NOTHING, blank=True, null=True)
  category2 = models.ForeignKey(category2_tbl, models.DO_NOTHING, blank=True, null=True)
  title = models.CharField(max_length=200,blank=True, null=True)
  description = models.CharField(max_length=1500,blank=True, null=True)
  requested_by = models.CharField(max_length=200,blank=True, null=True)
  path = models.CharField(max_length=1000,blank=True, null=True)
  date_requested = models.DateField(null=True, blank=True)
  status = models.IntegerField(max_length=60, default=0, null=True)

class category1_tbl(models.Model):
  name = models.CharField(max_length=200,blank=True, null=True)
  isactive = models.CharField(max_length=200,blank=True, null=True)
  created =  models.DateField(null=True, blank=True)
  path = models.CharField(max_length=1000,blank=True, null=True)
  modify = models.DateField(null=True, blank=True)
  created_by = models.CharField(max_length=1000,blank=True, null=True)

class category2_tbl(models.Model):
  category1 = models.ForeignKey(category1_tbl, models.DO_NOTHING, blank=True, null=True)
  priority = models.ForeignKey(priority_tbl, models.DO_NOTHING, blank=True, null=True)
  name = models.CharField(max_length=200,blank=True, null=True)
  isactive = models.CharField(max_length=200,blank=True, null=True)
  created =  models.DateField(null=True, blank=True)
  modify = models.DateField(null=True, blank=True)
  created_by = models.CharField(max_length=200,blank=True, null=True)

EDIT I already tried this before but the question is how can I filter this to another table? through getting ID from this query

id_to_filter = 7
main_objs = main_tbl.objects.filter(category1__id=id_to_filter) 

What I've tried which is returns me an error

id_to_filter = 7
main_objs = main_tbl.objects.filter(id=id_to_filter)

getId = main_objs.category1_id

second_table = category1_tbl.objects.filter(category1__id=getId)
result = second_table.name

Let say the output of the getId is 1 then I need to match again to the second table and finally getting the name of it through result variable

Answer 1

Django manages joining the tables for you and knows how to based on your ForeignKey fields.

If you want to filter on a value for name in each of the category tables, you could use an 'or' clause by using the Q function do make a more complex or lookup, something like the following. Note the use of the double underscore to get to the name field of the related table. As a side note, you should start each word of your class/model names with an upper case and remove the underscore. It is the generally agreed upon style, and in django will remove some confusion later when scanning your queries.

from django.db.models import Q

name_to_filter = "carl"
main_objs = main_tbl.objects.filter(Q(category1__name=name_to_filter) |
                            Q(category2__name=name_to_filter)) 

If you needed to only return records where the name was required to be in both category 1 and 2, there would be no need for the or clause and you can use the default method of filtering which 'ands' everything together.

name_to_filter = "carl"
main_objs = main_tbl.objects.filter(category1__name=name_to_filter,
                            category2__name=name_to_filter) 

EDIT If you know a category id, as suggested in your comment, you can filter by that. The filter creates the where clauses. The main_tbl has a field for the id in the categories so you can query that directly like:

id_to_filter = 7
main_objs = main_tbl.objects.filter(category1_id=id_to_filter) 

or if you want to force a join you can probably do the double underscore method (not sure why one would want that in this case though)

id_to_filter = 7
main_objs = main_tbl.objects.filter(category1__id=id_to_filter) 

Note that in the filters, you use the name of the field in the main_tbl, not the actual names of the tables they reference.

Edit 2 After your question was updated I think you need to think about things in a different way. You need to determine or describe if you want a list of names from one of those categories or if you need to display them on a template. If you want a JsonResponse, provide what the Json payload should look like. For example, if you want a flat list of the names in the view there are tools for that like values_list:

names = main_tbl.objects.filter(id=1).values_list(category1__name, flat=True)

but if you want to display things from the main_tbl and categories, pass the queryset to the template. You'd have something like this in the view. Let Django do the work of creating the joins between the tables, that's the strength of the Django ORM.

main_objs = main_tbl.objects.filter(id=1)
context = {'main_objs': main_objs}
return render(request, "your_template.html", context) 

and in the your_template.html something like:

{% for cat1 in main_objs.category1_set.all %}
    {{ cat1.name }}<br />
{% endfor %}

You need to define what you are trying to do. I have a feeling you may be trying to make things simpler with names like "main_tbl" but you should include in these posts exactly what is is you are trying to do. There may be a better way someone can suggest to achieve what you are after. Based on your post, main_tbl might be a task list; if so, leave named as such so people can better conceptualize the problem in real terms.