How does one convert complex SQL queries into Zend_Db_Select statements?

Question

The MySQL website has an excellent tutorial on storing hierarchal data in a database. I'm trying to write the query that returns a node's immediate children. I don't want to just copy/paste the query from the MySQL website, because I'm trying to work on this in a database agnostic manner.

This is the query I'm trying to Zend_Db_Select-ify

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
    nested_category AS parent,
    nested_category AS sub_parent,
    (
        SELECT node.name, (COUNT(parent.name) - 1) AS depth
        FROM nested_category AS node,
        nested_category AS parent
        WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'PORTABLE ELECTRONICS'
        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
ORDER BY node.lft;

+1 just for the link to the tutorial!

Marcin
– Marcin

2011-02-03 08:37:55 +00:00
Commented Feb 3, 2011 at 8:37 — Marcin
– Marcin, Commented Feb 3, 2011 at 8:37

Radek · Accepted Answer · 2011-02-03 09:44:56Z

1

You can try spliting your query into two Zend_Db_Select statements - parent query and subquery. You can use Zend_Db_Select object as param of from() method, like below:

$mainQuery = $db->select();
$mainQuery->from('user');

$sub = $db->select();
$sub->from('company');

$mainQuery->from(array('subquery' => $sub));

And you will get that kind of query:

SELECT `user`.*, `sub`.* FROM `user` 
    INNER JOIN (
        SELECT `company`.* FROM `company`
    ) AS `sub`

As you see, it automatically adds INNER JOIN when you add second from() - but I think, it's possible to rewrote your query to user joins instead of multi from syntax. So you should use joinInner() method, because then you can specify join condition as second param of it.

Notice, that subquery is similar to main query, so you could build main query, clone it as subquery, and thanks to Zend_Db_Select possibilities remove unnecessary parts (reset() method) and replace them:

$mainQuery = $db->select(); //and rest
$subQuery = clone $mainQuery;
$subQuery->reset(Zend_Db_Select::WHERE);
$subQuery->where(); // and add valid conditions for subquery

answered Feb 3, 2011 at 9:44

Radek

8,3614 gold badges34 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Billy ONeal Over a year ago

+1 -- Is there an easier way to specify the AS clause in the from besides calling Zend_Db_Adapter_Abstract::quoteIdentifier ?

Radek Over a year ago

Saying 'specify' you mean sanitizing? If yes, when you creating a subquery you can use named bind parameters, for e.g. :nodeName and bind it at the moment of query execution - as second parameter of fetchAll() method from db adapter.

Billy ONeal Over a year ago

@singles: No, I mean if one of the fields on my table is called "Left" (instead of "Lft"), which is a SQL reserved word. Therefore it must be escaped. For example, on MySQL it needs to turn into `Left`, while on MSSQL it needs to be [[Left]].

Radek Over a year ago

@Billy ONeal Ohhh, in this case I don't know another way than quoteIdentifier, sorry :/

Billy ONeal Over a year ago

@Singles: I mean just like Zend_Db_Select already does. I.e. if you do $x->select()->from('MyTable', array('Left')); it gets escaped.

|

Jake N · Accepted Answer · 2011-02-03 10:38:05Z

You can just paste in the query directly like this

$result = $db->query("SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
        FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
            SELECT node.name, (COUNT(parent.name) - 1) AS depth
            FROM nested_category AS node,
            nested_category AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt
            AND node.name = 'PORTABLE ELECTRONICS'
            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
    ORDER BY node.lf");

Be sure to use quote() to input any of your own parameters into this query.

This is a bit of a cheat and isn't something I have resorted to yet.

Collectives™ on Stack Overflow

How does one convert complex SQL queries into Zend_Db_Select statements?

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related