How to convert normal sql query to Zend_Db_Select?

Question

Hi I want to convert my normal mysql query to zend.db.select;

I want to use this script:

$select = $db->select();

// Add a FROM clause
$select->from( ...specify table and columns... )

// Add a WHERE clause
$select->where( ...specify search criteria... )

// Add an ORDER BY clause
$select->order( ...specify sorting criteria... );

$select->limit(20, 10);

for my query below

SELECT 
    IF(derived_messages.toid = '$user', derived_messages.fromid, 
    derived_messages.toid) friend1,c.UserName,
    derived_messages.message, derived_messages.fromid, derived_messages.toid,
    derived_messages.is_read,derived_messages.type,derived_messages.id as mesid,                    
    derived_messages.date,
    (SELECT M.message_id FROM messagesmapped M where M.message_id= derived_messages.id AND M.user_id ='$user' AND M.important = 1) as MesMapid
    FROM 
    (               
    SELECT * 
    FROM messages
    WHERE messages.deleted_by NOT 
    IN ( $user ) 
    ORDER BY Date DESC      
    ) derived_messages
    INNER JOIN Users c ON c.MemberID = IF(derived_messages.toid = '$user', derived_messages.fromid, 
    derived_messages.toid)
    WHERE (derived_messages.id IN 
    (SELECT M.message_id FROM messagesmapped M where M.message_id= derived_messages.id AND M.user_id ='$user' AND M.important = 1)                  
    AND 
    (derived_messages.toid='$user' OR derived_messages.fromid='$user'))

    GROUP BY friend1 ASC 
    ORDER BY derived_messages.date DESC, derived_messages.id DESC LIMIT $limit $offset

I hope someone can help m on this.

Thank you.

score 5 · Answer 1 · answered Sep 14 '12 at 01:12

It's possible but unlikely someone will write the query for you.

My recommendation on tackling such a query is to write each individual subquery as its own Zend_Db_Select object and then build the final query using the subqueries that you already have objects for.

Zend_Db_Select doesn't directly support the IF function, so for that you will need to use Zend_Db_Expr to add that statement into your select.

Here is a basic example of what I am talking about. Let's build the following query:

SELECT IF(msg.toId = 'drew010', msg.fromId, msg.toId), id, name, age, history.ip
FROM users
JOIN history ON users.id = history.userId
WHERE users.id = (
    SELECT id FROM users WHERE loginCount > 1000
)
GROUP BY id,
ORDER BY age DESC

First build the subselect that select users where loginCount > 1000.

$subquery1 = $db->select()
                ->from('users', array('id'))
                ->where('loginCount > ?', 1000);

Next, build the outer query with the IF function:

$cols  = array(
    new Zend_Db_Expr('IF(' . $db->quoteInto('msg.toId = ?', 'drew010') . '), msg.fromId, msg.toId'),
    'id', 'name', 'age'
);

$query = $db->select()
            ->from('users', $cols)
            ->join('history', 'users.id = history.userId', array('ip'))
            ->where('id = ?', $subquery1)
            ->group('id')
            ->order('age DESC');

echo $query;

The output:

SELECT
  IF(msg.toId = 'drew010', msg.fromId, msg.toId),
  `users`.`id`,
  `users`.`name`,
  `users`.`age`,
  `history`.`ip`
FROM `users`
INNER JOIN `history`
  ON users.id = history.userId
WHERE id = (
    (SELECT `users`.`id`
    FROM `users`
    WHERE (loginCount > 1000))
)
GROUP BY `id`
ORDER BY `age` DESC

So the way to go is break the entire query into individual queries first, and then construct the outer query. Just have patience and take it slow. That and read over the Zend_Db_Select docs to get a full picture of what you have available to you.

How to convert normal sql query to Zend_Db_Select?

1 Answers1