I have a MySQL query that is supposed to select a number of hierarchical locations belonging to a certain company from a database table. Only one table is needed to be used.
The query works fine if all locations in the table belong to one company, but if I add any locations belonging to other companies, the calculated depth (level within a hierarchy calculated using the nested set model) is returned incorrect. The lastly created company locations still return correct results, but all previous companies return incorrect depth.
So I guess my query is somehow getting rows from companies other then the company in question and hence all results get messed up, however I just can't figure out why and where exactly its doing this.
I am using THIS article as my reference for hierarchical data (Nested Set Method) queries.
Here is the query:
//This query will return a result set with all 'locations' that are on and below the hierarchy level of //the specified location. It will also add a depth field to each row which //shows how deep each location is in relation to the named starting location. //Any location name can be supplied, even the root location. //This query uses three self-joins and a sub-query to determine the depth of each location in relation to the starting //location. " SELECT location.location_id, location.location_name, location.location_company_id, location.location_active, (COUNT(parent.location_name) - (sub_tree.depth + 1)) AS depth FROM locations AS location, locations AS parent, locations AS sub_parent, ( SELECT location.location_id, (COUNT(parent.location_name) - 1) AS depth FROM locations AS location, locations AS parent WHERE location.lft BETWEEN parent.lft AND parent.rgt AND location.location_id = 334 AND location.location_company_id = 1001 GROUP BY location.location_id ORDER BY location.lft ) AS sub_tree WHERE location.lft BETWEEN parent.lft AND parent.rgt AND location.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND sub_parent.location_id = sub_tree.location_id AND location.location_company_id = 1001 GROUP BY location.location_id ORDER BY location.lft; "
This query works great when the data in the 'locations' table is as follows (one company locations only):
location_id location_name location_company_id lft rgt location_active 334 Company 1 1001 1 6 1 335 Comp1 Loc1 1001 4 5 1 336 Comp1 Loc2 1001 2 3 1
In this case the depth gets calculated correctly.
However, if I add more companies with some locations to the table then problems start occurring. By the way, the more companies with locations in the table, the bigger the depth inaccuracy. The first company gets most inaccurate depth, the second one gets only slightly inaccurate depth, and the last company gets correct depth. Here is a table with three company locations:
location_id location_name location_company_id lft rgt location_active 334 Company 1 1001 1 14 1 335 Comp1 Loc1 1001 12 13 1 336 Comp1 Loc2 1001 10 11 1 337 Company 2 1002 1 10 1 338 Comp2 Loc1 1002 8 9 1 339 Comp2 Loc2 1002 6 7 1 340 Company 3 1003 1 6 1 341 Comp3 Loc1 1003 4 5 1 342 Comp3 Loc2 1003 2 3 1
What I fail to figure out is whether the data in the table is incorrect (lft and rgt) or the query itself is wrong and how to fix it.
Any help, tips or advice would be greatly appreciated.