Django Self Join Query Using Aliases

am trying to use queryset to perform the following query without using raw SQL. any idea how can do that? select * from category_main a, category_list b, category_main c where b.ma

Solution 1:

This does basically what you want:

lists = List.objects.select_related('main', 'parent')

Note you have to explicitly state the relationships to follow in select_related here, because your parent relationship has null=True which isn't followed by default.

This will give you a set of List objects, but pre-fetch the related Main and List objects which you can reference as normal without hitting the db again.