How to write a subquery refactoring WITH clause using Zend framework?

Question

I want to use the WITH syntax inside the SQL query in Zend framework, the SQL looks like this:

WITH t AS
(SELECT item_id, row_number() OVER (ORDER BY some_criteria DESC) rn
   FROM orders)
SELECT t2.rn, t2.item_id
  FROM t t1 JOIN t t2 ON (t2.rn > t1.rn)
 WHERE t1.item_id = 145;

How can I do so in Zend Framework? with Zend_Db_Select? Any Suggestions?

Answer 1

With Zend_Db_Select you can't do it in beautiful way, so I suggest you to use simple query:

$q = "WITH t ..."
$db->fetchAll($q);

Answer 2

I quote-

Using Zend_Db_Select is not mandatory. For very simple SELECT queries, it is usually simpler to specify the entire SQL query as a string and execute it using Adapter methods like query() or fetchAll(). Using Zend_Db_Select is helpful if you need to assemble a SELECT query procedurally, or based on conditional logic in your application.

So you can try this

$wthselect = 'WITH t AS
          (SELECT item_id, row_number() OVER (ORDER BY some_criteria DESC) rn
              FROM orders)
         SELECT t2.rn, t2.item_id
           FROM t t1 JOIN t t2 ON (t2.rn > t1.rn)
           WHERE t1.item_id = 145';

$stmt = $select->query($wthselect);
$result = $stmt->fetchAll();

Answer 3

The Zend_Db_Select is usefull if the query is somewhat complicated, with parameters and logic included in your application, as already quoted on another answer. Check this link on the official Zend Framework for detailed description of this insctruction.

Also check this useful link from the Zend Framework community, specially this post. It clearly shows that for small and non complicated queries, as the one you posted, is simpler to use the fetchAll() or query() instructions.

So, my advice, use the fetchAll() or query() for your case.