convert complicated query to zend_db select object format

Question

I am working with ZEND and I can't convert below query to zend_db select object format:

SELECT *,CASE WHEN @score != score THEN @rank := @rank + 1 ELSE @rank END AS rank,
                @score := score AS dummy_value
                FROM (  SELECT score,username,ID,firstName,lastName
                FROM site_members, 
                    (SELECT @rank := 0, @score := NULL) AS vars 
                    WHERE `status` = 1 AND score > 0
                ORDER BY score DESC) AS h;

like this:

    $select = $this -> db -> select();
    $select -> from('site_members', array('COUNT(*) AS count'));
    $select -> where("ID  = ?", $memberID, Zend_Db::INT_TYPE);
    $row = $this -> db -> fetchRow($select);

I think world is big, but I cant get my answer

Nasser Ghiasi
– Nasser Ghiasi

2012-11-20 13:14:30 +00:00
Commented Nov 20, 2012 at 13:14 — Nasser Ghiasi
– Nasser Ghiasi, Commented Nov 20, 2012 at 13:14

Nasser Ghiasi · Accepted Answer · 2012-11-20 15:45:24Z

1

I worked on my question and found best answer:

            $inner_query = $this -> db -> select() -> from('site_members', array('score', 'username', 'ID', 'firstName', 'lastName')) 
                                 -> from(array("vars"=>new Zend_Db_Expr('(SELECT @rank := 0, @score := NULL)')))
                                 -> where("site_members.status = ?",1)
                                 -> where("score > 0")
                                 -> order(array("score DESC"));
            $select = $this -> db -> select();
            $select ->from(array("h"=>$inner_query),array(new Zend_Db_Expr('*'), 
                           new Zend_Db_Expr('CASE WHEN @score != score THEN @rank := @rank + 1 ELSE @rank END AS rank'), 
                           new Zend_Db_Expr('@score := score AS dummy_value')));

answered Nov 20, 2012 at 15:45

Nasser Ghiasi

3722 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

convert complicated query to zend_db select object format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related