Zend_Db_Select: rearrangement conditions in where where
I would like to do something like this:
$select = $myTbl->select()
->from('download_log')
->joinLeft(...... etc........
->joinLeft(...... etc........
->joinLeft(...... etc........);
//Filter all configured bots (Google, Yahoo, etc.)
if(isset($this->_config->statistics->bots)){
$bots = explode(',',$this->_config->statistics->bots);
foreach ($bots as $bot){
$select = $select->where("user_agent NOT LIKE '%$bot%'");
}
}
$select = $select->where("download_log.download_log_ts BETWEEN '".$start_date." 00:00:00' AND '".$end_date." 23:59:59'");
But the query you received is incorrect because the orWhere clauses are not grouped together in a unique AND clause. I would like to know if it is possible to regroup those NOT LIKE clauses into a pair of parent loops.
My current alternative is this:
//Filter all configured bots (Google, Yahoo, etc.)
if(isset($this->_config->statistics->bots)){
$bots = explode(',',$this->_config->statistics->bots);
foreach ($bots as $bot){
$stmt .= "user_agent NOT LIKE '%$bot%' AND ";
}
$stmt = substr($stmt,0,strlen($stmt)-4); //remove the last OR
$select = $select->where("($stmt)");
}
Thanks!
+2
a source to share
3 answers
Your current alternative looks like the best option. Here's a slightly modified version, using correct quoting only in case something bad gets inserted into $ bots
$botWhere = '';
foreach ($bots as $bot){
$botWhere .= $myTbl->getAdapter()->quoteInto('user_agent NOT LIKE ? AND ', "%$bot%");
}
$botWhere = trim($botWhere, ' AND ');
$select = $select->where($botWhere);
0
a source to share
Thanks! I finally did it like this:
//Filter all configured bots (Google, Yahoo, etc.)
if(isset($this->_config->statistics->bots)){
$bots = explode(',',$this->_config->statistics->bots);
foreach ($bots as $bot){
$stmt .= $myTbl->getAdapter()->quoteInto("user_agent NOT LIKE ? AND ",%$bot%);
}
$stmt = trim($stmt, ' AND '); //remove the last AND
$stmt .= 'OR user_agent IS NULL';
$select = $select->where("($stmt)");
}
+1
a source to share