I want to do a search through 2 columns. I get phrase from the user query, explode it and trying to build following syntax:
[...] WHERE (column1 LIKE ’%word1%’ AND column1 LIKE ’%word2%’) OR (column2 LIKE ’%word1%’ AND column2 LIKE ’%word2%’)
im trying to use following code but I get ( ) AND ( )
what to do in case it’s dynamic query depending on number of words.
$searchArr = explode (' ', $this->_config['phrase']);
$searchCount = count ($searchArr);
$q = $this->modx->newQuery('nlFile');
$q->select('nlFile.*, Cart.id AS cartid, Cart.file_size AS file_size');
$q->leftJoin('nlCart', 'Cart', 'nlFile.id = Cart.fid AND Cart.uid = '.$this->_config['uid']);
$searchArrQ = array();
if ($searchCount > 1) {
foreach ($searchArr as $key => $search) {
if ($key == $searchCount -1) {
$q->andCondition(array(
'title:LIKE' => '%'.$search.'%'
), NULL, 1);
$q->andCondition(array(
'description:LIKE' => '%'.$search.'%'
), NULL, 2);
} else {
$q->andCondition(array(
'title:LIKE' => '%'.$search.'%',
), NULL, 1);
$q->andCondition(array(
'description:LIKE' => '%'.$search.'%'
), NULL, 2);
}
}
} else {
$q->where(array(
'title:LIKE' => '%'.$search.'%',
'description:LIKE' => '%'.$search.'%'
));
}
$q->sortby('added_date', 'DESC');
$q->limit($this->_config['limit'], $this->_config['offset']);
$q->prepare();
$this->sql = $q->toSQL();
print ($this->sql);
$this->data = $this->modx->getCollection('nlFile', $q); 
MODX Staff