[SOLVED] Complex Query

[completeamateur]

I'm trying to construct a fairly complex query...

 

I suppose firstly I should point out that I don't understand the difference between inserting the query as a string and constructing it in Zend (although what I would interpret as the same query gives differing ouputs)?

 

This is the query I'm trying to run;

 

		$sql = '
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM category AS node,
category AS parent,
category AS sub_parent,
(
	SELECT node.name, (COUNT(parent.name) - 1) AS depth
	FROM category AS node,
	category AS parent
	WHERE node.lft BETWEEN parent.lft AND parent.rgt
	AND node.lft = 1
	GROUP BY node.name
	ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth = 1
ORDER BY node.lft';

	$result = $this->fetchAll($sql);
	return $result;

 

...doesn't work (although I can run the query fine in mysql query browser),

 

		$sub = $this->select()
		->from(
			array('node' => 'category'),
			array('name', '(COUNT(parent.name) - 1) AS depth')
		)
		->from(
			array('parent' => 'category')
		)
		->where('node.lft BETWEEN parent.lft AND parent.rgt')
		->where('node.lft = 1')
		->group('node.name')
		->order('node.lft');

	$sql = $this->select()
		->from(
			array('node' => 'category'),
			array('name', '(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth')
		)
		->from(
			array('parent' => 'category')
		)
		->from(
			array('sub_parent' => 'category')
		)
		->from(
			array('sub_tree' => new Zend_Db_Expr('(' . $sub . ')'))
		)
		->where('node.lft BETWEEN parent.lft')
		->where('parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt')
		->where('sub_parent.name = sub_tree.name')
		->group('node.name')
		->having('depth = 1')
		->order('node.lft');

	$result = $this->fetchAll($sql);
	return $result;

 

...doesn't work.  I'm a bit stumped.

 

Any pointers much appreciated.

[completeamateur]

FYI, the error message I get when trying to run the first query is "Operand should contain 1 column(s)".  Strange that it should work fine MYSQL Query Browser.

[Xeoncross]

First, the second code block looks like the $sql string is overwritten. Second, did you check to make sure that the MySQL version is the same (you didn't mention wither you are on your machine or a server).

 

Also, it looks like this query is rather complex - I would think that you could simplify it. Have you tested the speed?

[completeamateur]

It's all running from my local machine so there shouldn't be a problem.

 

The query is supposed to resemble what is suggested in http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

I'm still not having any joy though.  This is my latest (failed) attempt...

 

		$sql = $this->select()
		->from('category AS node')
		->from('category AS parent')
		->from('category AS sub_parent')
		->from(
			array('sub_tree' =>
				$this->select()
					->from('category AS node')
					->from('category AS parent', '(COUNT(parent.name) - 1) AS depth')
					->where('node.lft BETWEEN parent.lft AND parent.rgt')
					->where('node.lft = 1')
					->group('node.name')
					->order('node.lft')
			), '(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth'
		)
		->where('node.lft BETWEEN parent.lft AND parent.rgt')
		->where('node.lft BETWEEN sub_parent.lft AND sub_parent.rgt')
		->where('sub_parent.name = sub_tree.name')
		->group('node.name')
		->having('depth = 1')
		->order('node.lft');

 

 

[completeamateur]

I have no finger nails or hair, but after what has felt like several years, I've managed to sort it!!

 

		$sql = $this->select()
		->from('category AS node')
		->from('category AS parent', '')
		->from('category AS sub_parent', '(COUNT(parent.name) - (sub_tree.depth + 1)) AS depth')
		->joinInner(
			array('sub_tree' =>
				$this->select()
					->from('category AS node', 'name')
					->from('category AS parent', '(COUNT(parent.name) - 1) AS depth')
					->where('node.lft BETWEEN parent.lft AND parent.rgt')
					->where('node.lft = ' . $lft)
					->group('node.name')
					->order('node.lft')
			), 'sub_parent.name = sub_tree.name', array()
		)
		->where('node.lft BETWEEN parent.lft AND parent.rgt')
		->where('node.lft BETWEEN sub_parent.lft AND sub_parent.rgt')
		->group('node.name')
		->having('depth = 1')
		->order('node.lft');

[Xeoncross]

I have no finger nails or hair, but after what has felt like several years, I've managed to sort it!!

 

Why do you think Geeks never win beauty contests?