Multiple optional query terms

Need Help
1

Using cakephp 4. I have a complex search form with check boxes, date ranges, and text input controls. The user may enter none, some, or all of the inputs available on the form. What’s the best approach to build the query in this instance. For example assume the table is Products with a long list of attributes for each record like color( red, green, blue, yellow, orange, etc), size (s,m,lg), manufacture date, price, description.

The form is created with checkboxes for each color and size, low and high manufacture date range, low and high price range, text input for description keywords.

To create the controller I was attempting something like:

$search_terms = $this->request->getData();
$query = $this->Products->find();

if($serch_terms['red']){
   $query = $query->where(['color =' => '"red"']);
}

if($search_terms['blue']{
  $query = $query->where(['OR' => [['color =' => '"blue"'],]]);
}

if($search_terms['yellow']{
  $query = $query->where(['OR' => [['color =' => '"yellow"'],]]);
}
........
if($serach_terms['small']{
  $query = $query->where(['AND' = >['size =' => '"small"']);
}
..........

if{$search_term['keywords']){
   $query = $query->where(['AND' => ['description like' => "% keyword %"]]);
}

I would expect this to generate SQL like:

Select * from Products where
(color = “red” OR color = “blue” OR color = "yellow’)
AND
(size = “small” or size = “medium”)
AND
(description contains “Keywords list”);

But what I get is a list of these conditions all connected with AND. What is the proper approach to building this type of query?

2

Every time you call ->where, it adds an “AND” clause, with that clause being built from what you’ve passed to it. The “OR” structure is for you to pass multiple conditions into the clause that’s being built. It’s a subtle difference, which can be hard to grasp. As an example, if you want

(color = “red” OR color = “blue” OR color = "yellow’)

you need to use

->where(['OR' => [['color' => 'red'], ['color' => 'blue'], ['color' => 'yellow']]])

Consider what you expect your code to generate if “red” wasn’t set but “blue” was; there would not have been an initial where, so what would the “or” in your blue query do?

I think what you want to do is, for example, go through all your possible colours, build a list of what’s been selected, then if there are any, use an IN query for that. Like so:

$colours = [];
foreach (['red', 'blue', 'yellow'] as $colour) {
    if ($search_terms[$colour]) {
        $colours[] = $colour;
    }
}
if (!empty($colours)) {
    $query = $query->where(['color IN' => $colours]);
}

There’s other ways to do it, of course, but this seems pretty good to me. If you really want to use an OR instead of IN, that might look like this:

$colours = [];
foreach (['red', 'blue', 'yellow'] as $colour) {
    if ($search_terms[$colour]) {
        $colours[] = ['color' => $colour];
    }
}
if (!empty($colours)) {
    $query = $query->where(['OR' => $colours]);
}
3

I use the search plugin which lets you define filters in the model.

You can use the multiValue and multiValueSeparator options for multiple values (i.e. checkboxes)

Without using that, i would set the checkboxes to have a name with arrays, so the URL is like this

/products?colors[yellow]=0&colors[blue]=1&colors[red]=1

so you get

['blue', 'red']

and to get the SQL

// $colors = ['blue', 'red'];
$query->whereIn('color', $colors);
4

Thanks for the reply Zuluru! This seems to be the most straightforward approach but also a bit like brute force. I’ll have to process all of the request data first then create the query versus building the query while processing the request data.

It’s good to learn that I can pass an array to where([‘OR’]) and it will enumerate the values. That’s helpful and I’ll give it a try.

:+1:

5

Raul338,

The search plugin seems to have a lot of power. It has a bit of a learning curve too so I’ll have to knock my current project out with the more straightforward approach, but I appreciate the tip for search. I will add it to the list for future projects.

6

@techsailor I can definitely recommend the search plugin but the current docs can be quite the journey to take.

I tried to split them up and explain in more detail how the whole filter system works in here (with the corresponding SQL):

7

Note that you wouldn’t need to use the search plugin to benefit from naming your checkboxes such that you get an array of, e.g., colour values directly posted to you, instead of needing to brute-force them out of the data.