php – 如何将普通的sql查询转换为Zend_Db_Select？

嗨,我想将我正常的mysql查询转换为zend.db.select;

我想使用这个脚本：

$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);

我的查询如下

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

我希望有人可以帮助我.

谢谢.

解决方法:

这是可能的,但不太可能有人会为您编写查询.

我对解决此类查询的建议是将每个子查询编写为自己的Zend_Db_Select对象,然后使用已有对象的子查询构建最终查询.

Zend_Db_Select不直接支持IF函数,因此您需要使用Zend_Db_Expr将该语句添加到您的选择中.

这是我所说的基本例子.让我们构建以下查询：

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

首先构建选择loginCount>用户的子选择. 1000.

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

接下来,使用IF函数构建外部查询：

$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;

输出：

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.userIdWHERE id = (    (SELECT `users`.`id`    FROM `users`    WHERE (loginCount > 1000)))GROUP BY `id`ORDER BY `age` DESC

因此,首先要将整个查询分解为单个查询,然后构造外部查询.只要有耐心并慢慢来.然后阅读Zend_Db_Select文档,全面了解您可以使用的内容.

来源：https://www.icode9.com/content-2-253551.html