Jump to content

[SOLVED] Complex Query


completeamateur

Recommended Posts

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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');

 

 

Link to comment
Share on other sites

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');

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.