I’m trying to build xPDO query with nested condition, but I got wrong results.
I need to create query with the following conditions:
WHERE ( `TemplateVar`.`name` = ’tags’ AND ( `TemplateVarResources`.`value` like ’%work%’ OR `TemplateVarResources`.`value` like ’%nakhodka%’ ) )
If I try the following code:
/* Prepare and run Query. */
$query = $modx->newQuery(’modResource’);
$query->innerJoin(’modTemplateVarResource’, ’TemplateVarResources’);
$query->innerJoin(’modTemplateVar’, ’TemplateVar’, ’`TemplateVar`.`id` = `TemplateVarResources`.`tmplvarid`’);
$string[’TemplateVar.name’] = ’tags’;
$values[] = array(’AND:TemplateVarResources.value:like’ => ’%’.’work’.’%’);
$values[] = array(’OR:TemplateVarResources.value:like’ => ’%’.’nakhodka’.’%’);
$string[] = $values;
$query->where($string);
the result is as follows:
WHERE ( `TemplateVar`.`name` = ’tags’ OR ( `TemplateVarResources`.`value` like ’%keyword1%’ OR `TemplateVarResources`.`value` like ’%keyword2%’ ) )
I can’t figure out how to build query array to get AND condition before nested part.
Anyone can help?
It appears to be that query builder is buggy. I’ve worked it around with changing order of expression parts:
$query = $modx->newQuery(’modResource’);
if(isset($tags))
{
$query->innerJoin(’modTemplateVarResource’, ’TemplateVarResources’);
$query->innerJoin(’modTemplateVar’, ’TemplateVar’, ’`TemplateVar`.`id` = `TemplateVarResources`.`tmplvarid`’);
$values[] = array(’AND:TemplateVarResources.value:like’ => ’%’.’work’.’%’);
$values[] = array(’OR:TemplateVarResources.value:like’ => ’%’.’nakhodka’.’%’);
$query->where($values);
$query->andCondition(array(’TemplateVar.name’ => ’tags’));
}
$query->andCondition(array(’published’ => true, ’parent’ => $parent, ’context_key’ => $modx->context->key));
If anyone still has a clue you are welcome =)
you can try to use different condition-groups like that:
<?php
$query = $modx->newQuery('modResource');
$query->innerJoin('modTemplateVarResource', 'TemplateVarResources');
$query->innerJoin('modTemplateVar', 'TemplateVar', '`TemplateVar`.`id` = `TemplateVarResources`.`tmplvarid`');
$name = array('TemplateVar.name' => 'tags');
$query->andCondition($name,null,0);
$values[] = array('TemplateVarResources.value:like' => '%'.'work'.'%');
$values[] = array('OR:TemplateVarResources.value:like' => '%'.'nakhodka'.'%');
$query->andCondition($values,null,1);
It’s not working right anyway. The only thing that affects the result is rearranging the arguments in criteria expression. This shouldn’t be, these logical operations are commutative =). Hope it will be fixed in hear releases.
