How to convert a regular SQL query to Zend_Db_Select?

Question

Hi, I want to convert my regular 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 request 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 with this.

Thanks.

Answer 1

Perhaps, but hardly anyone will write a request for you.

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

Zend_Db_Select does not directly support the IF function, so for this you will need to use Zend_Db_Expr to add this operator to your selection.

---

Here is a basic example of what I'm 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 create a subheading that selects users where loginCount > 1000.

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

Then create an external query using 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; 

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 

---

Thus, the way is to first split the entire query into separate queries, and then build an external query. Just have patience and do it slowly. This and read the Zend_Db_Select docs to get a complete picture of what you have for you.