Creating a nested OR condition from string

- 67 Posts
jimbob72 Reply #1, 15 years, 3 months ago

I have an array of user-defined values stored in an extended user field:

[0] = Red, [1] = Blue, [2] = Green etc.

I’m trying to construct a query in the following format:

where ( type = ’box’ AND size = ’large’ AND ( color LIKE ’red’ OR color LIKE ’blue’ OR color LIKE ’green’ ) )

The reason the color conditions need to be LIKE as opposed to = is because my boxes can be multi-colored and the color field in the table I’m querying contains a comma-delimited list of colors (e.g. red,green).

I need to create the final AND () condition from the array. I have tried several solutions using looping orconditions and nested arrays but the syntax is always wrong, with brackets not appearing correctly.

Any ideas would be very gratefully welcomed.

67 Posts

jimbob72 Reply #2, 15 years, 3 months ago

Found a solution to this:

//this code assumes you've already set up a custom xPDO package for the table
//the $color list comes from the user extended field (i.e. they can choose multiple favourite colors)
$colorArray = explode(',',$colors);

//how many colors have they chosen (e.g. red, green, blue, brown)
$colorCount = count($colorArray);

//prepare a query with your chosen table 
$c = $modx->newQuery('Boxes');

//this is an arbitrary condition to start the query - 'unspecified' could be anything you want as long as it doesn't appear in the table
$values[] = array('AND:color:LIKE' => 'unspecified');

//create an array of conditions for each color specified by the user
for ($i=0; $i<$colorCount; $i++) {
$values[] = array('OR:color:LIKE' => '%'.$colorArray[$i].'%');
}

//start the where query
$c->where($values);

//add on the AND conditions
$c->andCondition(array('type:=' => 'Box'));
$c->andCondition(array('size:=' => 'Large'));

$c->sortby('color','DESC');

//and if you want to debug
$c->prepare();
$modx->log(xPDO::LOG_LEVEL_ERROR, $c->toSql());

This delivers the following query:

WHERE ( (`Boxes`.`color` LIKE ’%red%’ OR `Boxes`.`color` LIKE ’%green%’ OR `Boxes`.`color` LIKE ’%blue%’ OR `Boxes`.`color` LIKE ’%brown%’) AND `Boxes`.`type` = ’Box’ AND `Boxes`.`size` = ’Large’)