MySQL Forums
Forum List  »  General

Re: Modified Preorder Tree Traversal - Advanced question
Posted by: Clive Galway
Date: August 30, 2005 05:48PM

I have a demo of the latest version up now !
See your lovely query in action :)

I made your query a subquery on the locations table like:

$query .= "SELECT * FROM xmb_gm_locations WHERE region_id IN (
"SELECT d.region_id ".
"FROM xmb_gm_regions r ".
"INNER JOIN xmb_gm_regions d ".
"ON r.lft <= d.lft ".
"AND r.rgt >= d.rgt ".
"WHERE r.region_id = '".$region."' ".
"AND d.zoom <= $zoom ".
"FROM xmb_gm_regions a ".
"WHERE a.lft < d.lft ".
"AND a.rgt > d.rgt ".
"AND r.lft <= a.lft ".
"AND r.rgt >= a.rgt ".
"AND a.zoom <= $zoom ".
"AND a.zoom >= d.zoom ))";

When a user is selecting regions, I don't actually use your code, as each time I only need to pull the children of the selected region, I just use the parent_id of the region (Is it bad to have an adjacency list parent_id AND nested set lft, rgt fields ?)

like so:
SELECT name, parent_id FROM xmb_gm_regions WHERE region_id='$curr_id'

I have another question...
How would I modify that query to add an extra field on the end of the returned data that included a COUNT of all locations in that region (the table xmb_gm_locations has a region_id field in it)
Ideally the field should contain the number of locations in that region and it's child regions. (ie for the row UK, it should contain a count of all locations with a region id that is a child of UK, not just in the UK region itself)

I tried solving this one for like 5 or 6 hours, fried my brain and gave up :(

Options: ReplyQuote

Written By
Re: Modified Preorder Tree Traversal - Advanced question
August 30, 2005 05:48PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.