Topic: [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;

Code: [Select]

$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),

Code: [Select]

$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...

Code: [Select]

$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!!

Code: [Select]

$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?