I have a huge tree stored using the nested set storage method (primary id, left id, right id). In addition, I also have a type_id which indicates what type of item each node is. There is a certain type of node which can only be a child.
What I am trying to do with a single query is retrieve all of these children (easy since they all have the same type) but then I also want the name of one (or more) of the parents that are of a certain type (category if you will).
Think of a hierarchy of
-Country (type_id = 0)
—State (type_id = 1)
—-City (type_id = 2)
A city will always be a child node. I am trying to get a list of cities, with their associated state name (there may be other cases where I need additional parents, such as the Country. Basically, I want to retrieve the tree in “reverse order”.
City, State
City, State
...
This is my modified “getTreePreorder” function (which returns node level and depth info in a single query too…useful if you are using code to build html from a tree)
function getTreePreorder($node, $extra_cols = array(), $type = 6)
{
extract($this->_get_table_setup_data($node));
$extra_select = $this->prep_cols('node', $extra_cols);
$q = "SELECT node.`$pkcol`, node.`$leftcol`, node.`$rightcol`, $extra_select,
(COUNT(parent.`$leftcol`) - 1) AS depth,
CEIL((node.`$rightcol` - node.`$leftcol` - 1) / 2) AS child_quantity
FROM
`$table` AS node,
`$table` AS parent
WHERE node.`$leftcol` BETWEEN parent.`$leftcol` AND parent.`$rightcol`
GROUP BY node.`$leftcol`
HAVING node.`$leftcol` >= $leftval AND node.`$rightcol` <= $rightval
ORDER BY node.`$leftcol`";
$query = $this->db->query($q);
$treeArray = array();
foreach($query->result_array() AS $result)
{
if($result['type_id'] <= $type)
{
$treeArray[] = $result;
}
}
return array( "result_array" => $treeArray,
"prev_left" => $node[$leftcol],
"prev_right" => $node[$rightcol],
"level" => -2);
}
Here are 2 helper functions used above:
//helper function gets the default column names and protects identifiers, as well as any optional column names provided in $extra_cols
//$type can be 'node' or 'parent', depending on what is needed for the query
function prep_cols($type = 'node', $extra_cols = array())
{
$cols = array();
$extra_select = '';
foreach($this->default_col_names as $d)
{
$cols[] = "$type.`$d`";
}
foreach($extra_cols as $d)
{
$cols[] = "$type.`$d`";
}
$extra_select = implode(', ', $cols);
//if( ! empty($extra_select)) $extra_select = ', ' . $extra_select . ', ';
return $extra_select;
}
//helper function to get the needed table and column info for various db queries.
//returned array is meant to be extract()ed internally to use the variables in building SQL
function _get_table_setup_data($node)
{
return array(
'table' => $this->table_name,
'pkcol' => $this->primary_key_column_name,
'leftcol' => $this->left_column_name,
'rightcol' => $this->right_column_name,
'pkval' => (int) $node[$this->primary_key_column_name],
'leftval' => (int) $node[$this->left_column_name],
'rightval' => (int) $node[$this->right_column_name]
);
}
And this is a piece of code that will retrieve the siblings of a given node. This does what I need, but I need it incorporated into my original query or else this query will get executed several thousand times. (Also great for creating breadcrumbs).
//retrieve ancestor nodes (branch) of the hierarchy for a supplied node, or a specific type of node from the branch.
//if $hierarchy_type_id contains id of specific hierarchy type,
//it will return only that node instead of the entire branch.
//eg. enter the type id for state to find out what state a node is in.
function get_branch($node, $hierarchy_type_id = '', $extra_cols = array())
{
extract($this->_get_table_setup_data($node));
$extra_select = $this->prep_cols('parent', $extra_cols);
$having = (empty($hierarchy_type_id)) ? '' : " HAVING parent.`type_id` = $hierarchy_type_id ";
$q = "SELECT
$extra_select
FROM
`$table` AS node,
`$table` AS parent
WHERE
node.`$leftcol` BETWEEN parent.`$leftcol` AND parent.`$rightcol` AND
node.`$pkcol` = $pkval
$having
ORDER BY
parent.`$leftcol`";
return $this->db->query($q)->result_array();
}
Any guidance would be greatly appreciated. I’m not very good with these more complex queries.
