Zend 框架 SQL 选择查询构造(WHERE CLAUSE) [英] Zend framework SQL select query construction (WHERE CLAUSE)

问题描述

我正在尝试生成一个查询，该查询从用户表中选择所有用户表，其中名字的姓氏的任意组合与特定的搜索词相匹配

I am trying to generate a query that selects all from a user table where any combination of the last name of first name matches a particular search term

$select = $select->where('last_name LIKE ?', '%'.$term.'%')->orWhere('first_name LIKE ?', '%'.$term.'%')
                        ->orWhere("CONCAT(first_name,' ', last_name) LIKE ?", '%'.$term.'%')
                        ->orWhere("CONCAT(last_name,' ', first_name) LIKE ?", '%'.$term.'%');                           

还必须满足另一个条件，该条件在另一个 where 子句中指定

There is another condition that has to also has to be met which is specified in another where clause

$select = $select->where("deleted = 0 AND scholar = 0");

生成如下SQL语句

SELECT `user`.* FROM `user` WHERE (last_name LIKE '%frank%') OR (first_name LIKE '%frank%') OR (CONCAT(first_name,' ', last_name) LIKE '%frank%') OR (CONCAT(last_name,' ', first_name) LIKE '%frank%') AND (deleted = 0 AND scholar = 0) ORDER BY `date_created` desc LIMIT 25

这不会返回所需的结果，因为我得到了学者 = 1 的行；

This doesnt return the desired result as i get rows where scholar = 1;

我认为查询应该是

SELECT `user`.* FROM `user` WHERE ((last_name LIKE '%frank%') OR (first_name LIKE '%frank%') OR (CONCAT(first_name,' ', last_name) LIKE '%frank%') OR (CONCAT(last_name,' ', first_name) LIKE '%frank%')) AND  (deleted = 0 AND scholar = 0) ORDER BY `date_created` DESC LIMIT 25

使用 $select 对象实现此目的的正确语法是什么.

What the right syntax to achieve this using the $select object.

推荐答案

你可以使用quoteInto来准备你的条件，然后像这样使用它们:

You can use quoteInto to prepare your conditions and then use them like this :

    $first_name_cond = $db->quoteInto('first_name LIKE ?', '%'.$term.'%');
    $last_name_cond = $db->quoteInto('last_name LIKE ?', '%'.$term.'%');

    $concat_cond1 = $db->quoteInto("CONCAT(first_name,' ', last_name) LIKE ?", '%'.$term.'%');

    $concat_cond2 = $db->quoteInto("CONCAT(last_name,' ', first_name) LIKE ?", '%'.$term.'%');


    $select = $select->where($first_name_cond.' OR '.$last_name_cond.' OR '.

             $concat_cond1.' OR '.$concat_cond2)->where("deleted = 0 AND scholar = 0");

这篇关于Zend 框架 SQL 选择查询构造(WHERE CLAUSE)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！