i need query like this
我需要这样的查询
SELECT tbl_leads . * , tbl_lead_category.vLeadCategoryName AS vLeadCategoryName, tbl_lead_category.vLeadCategoryIcon AS vLeadCategoryIcon, Concat( vFirst, ' ', vLast ) AS vFirst
FROM tbl_leads
LEFT JOIN tbl_lead_category ON tbl_lead_category.iLeadCategoryID = tbl_leads.iLeadCategoryID
LEFT JOIN tbl_user ON tbl_user.iUserID = tbl_leads.iUserID
WHERE (
tbl_leads.eDeleted = '0'
)
AND (
tbl_leads.iUserID = '1'
)
AND (
vCompanyName LIKE '%t%'
OR vCompanyUrl LIKE '%t%'
OR vPersonName LIKE '%t%'
OR vDesignationName LIKE '%t%'
OR vSkypeID LIKE '%t%'
OR vEmailID LIKE '%t%'
OR vPhoneNumber LIKE '%t%')
And my code is like:
我的代码是这样的:
public function fetchAll($data, $aColumns, $iUserID = "")
{
$select = $this->tableGateway->getSql()->select();
$select->columns(array('*'));
$select->join('tbl_lead_category', 'tbl_lead_category.iLeadCategoryID = tbl_leads.iLeadCategoryID', array('vLeadCategoryName', 'vLeadCategoryIcon'), "LEFT")
->join('tbl_user', 'tbl_user.iUserID = tbl_leads.iUserID',array("vFirst"=>new \Zend\Db\Sql\Predicate\Expression("Concat(vFirst,' ',vLast)")),"LEFT");
$where = new \Zend\Db\Sql\Where();
$where->NEST->equalTo('tbl_leads.eDeleted','0')->UNNEST;
if(isset($iUserID) && $iUserID != "")
$where->AND->NEST->equalTo ('tbl_leads.iUserID', $iUserID)->UNNEST;
// start for searching
if (isset($data['sSearch']) && $data['sSearch'] != "") {
for ($i = 0; $i < count($aColumns) - 1; $i++) {
$where->OR->like($aColumns[$i], "%" . $data['sSearch'] . "%");
}
}
/*i have tried this, but its giving error: not nested*****
if (isset($data['sSearch']) && $data['sSearch'] != "") {
$where->AND->NEST;
for ($i = 0; $i < count($aColumns) - 1; $i++) {
$where->OR->like($aColumns[$i], "%" . $data['sSearch'] . "%");
// how can i get parentheses to my where which i am searching
}
$where->UNNEST;
} ***************/
$select->where($where);
var_dump($select->getSqlString());
$resultSet = $this->tableGateway->selectWith($select);
return $resultSet;
}
if i use nest and unnest can't it created by using for loop and if statement, if i have a custom query like this how can i make the query, without parentheses my query have no meaning.
如果我使用nest和unnest不能通过for循环和if语句创建它,如果我有一个像这样的自定义查询我如何才能创建查询,没有括号我的查询就没有意义。
2 个解决方案
#1
4
I think following is better. NEST will open the bracket UNNEST will close bracket
我认为跟随会更好。巢将会打开括弧,将会关闭括弧。
$where = new Sql\Where();
$where->NEST//start braket
->equalTo('col','someVal')
->OR
->equalTo('col','secondVal')
->UNNEST //close bracet
#2
0
To add the expression nesting you will need to use the Predicate::nest()
. This will be $where->nest()
). The returned object is a PredicateSet
which will automatically be nested within the query for you.
要添加表达式嵌套,您需要使用谓词::nest()。这将是美元- >巢())。返回的对象是一个谓词集,它将自动地为您嵌套在查询中。
All you then need to do is loop over it and add your expressions
然后需要做的就是对它进行循环并添加表达式
$where = new \Zend\Db\Sql\Where();
$predicateSet = $where->nest(); // Returns a new 'predicate set'
if (isset($data['sSearch']) && ! empty($data['aSearch'])) {
for($x = 0; $x < count($aColumns); $x++) {
if ($x > 0) $predicateSet->or();
$predicateSet->like($aColumns[$x], $search);
}
}
$where->unnest();
#1
4
I think following is better. NEST will open the bracket UNNEST will close bracket
我认为跟随会更好。巢将会打开括弧,将会关闭括弧。
$where = new Sql\Where();
$where->NEST//start braket
->equalTo('col','someVal')
->OR
->equalTo('col','secondVal')
->UNNEST //close bracet
#2
0
To add the expression nesting you will need to use the Predicate::nest()
. This will be $where->nest()
). The returned object is a PredicateSet
which will automatically be nested within the query for you.
要添加表达式嵌套,您需要使用谓词::nest()。这将是美元- >巢())。返回的对象是一个谓词集,它将自动地为您嵌套在查询中。
All you then need to do is loop over it and add your expressions
然后需要做的就是对它进行循环并添加表达式
$where = new \Zend\Db\Sql\Where();
$predicateSet = $where->nest(); // Returns a new 'predicate set'
if (isset($data['sSearch']) && ! empty($data['aSearch'])) {
for($x = 0; $x < count($aColumns); $x++) {
if ($x > 0) $predicateSet->or();
$predicateSet->like($aColumns[$x], $search);
}
}
$where->unnest();