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

发布时间：2020-12-13 17:21:10 所属栏目：PHP教程来源：网络整理

导读：嗨,我想将我正常的 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 ORDE

嗨,我想将我正常的 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.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.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.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

首先构建选择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.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',`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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!