Recursive relationship traversing same table – Django – Raw Query

For Example ,
employee is reporting to manager and being a manger to other employees. To drill down and get all the employees under him including sub managers and their reporting employees to N-level at single query

employees table
emp_id | manager_id
1 |
2 | 1
3 | 1
4 | 2
5 | 2
6 | 3
7 | 3
8 | 4
9 | 1
10 | 6

Raw query :

select id,manger_id ,emp_id from (select * from employees order by manger_id, emp_id) products_sorted, (select @pv := '1') initialisation where find_in_set(manger_id, @pv) and length(@pv := concat(@pv, ',', emp_id))

where @pv := ‘1’ is beginning point to start the travers

when the starting point is “1”, u will get all the record , when the starting point is 3 , you will receive 3=> 6,7 and 6=>10 , so total three record

Implementing in Django by raw query

class [Classname](ModelViewSet):
queryset = [Modlename].objects.all()
#by default get the report for logged in users
def get_queryset(self):
report_to_emp_id = self.request.user.id
return Reporting.objects.raw("select id,manger_id ,emp_id from (select * from employees order by manger_id, emp_id) products_sorted, (select @pv := '"+str(report_to_emp_id)+"') initialisation where find_in_set(manger_id, @pv) and length(@pv := concat(@pv, ',', emp_id)")

serializer_class = [SerilalizerClass]

This will list all the employees and including the sub levels

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s