Forums

Hi,

I created a search field and in the Read Data function I mentioned two 'Searchable fields':
Model.a
Model.b

Currently the system executes the WHERE clause as: WHERE 'Model'.'a' LIKE '%search text%' AND 'Model'.'b' LIKE '%search text%'
While I would expect an OR instead of AND: WHERE ('Model'.'a' LIKE '%search text%' OR 'Model'.'b' LIKE '%search text%')
Please also note the round brackets around the search conditions, so: WHERE category='123' AND (a LIKE '%s%' OR b LIKE '%s%') in case another condition is part of the original Read Data function.
This would make more sense in my opinion as a search algorithm.

Is this subject for improvement, or do I miss something here?

Kind regards,
Nico

If you are using CC6, you can already do brackets and OR.

Just under the "Where conditions" header, you can find the green "+ Operator" button. This allows you to add "(", ")", "AND", and "OR". So it'll look something like this:

category='123'
AND
(
'Model'.'a' LIKE '%search text%'
OR
'Model'.'b' LIKE '%search text%'
)

If you need more complex queries, you can click on the red "+ Rules list" and use custom code for conditions.

Thanks for your suggestion! However it is not the solution for my problem I'm afraid.

I want to use the standard Search functionality CC6 provides.
If one uses the standard Search field functionality of CC6, the system itself uses the Read Data query to generate automatically the query for the search.
So, the 'AND a LIKE '%s%' AND b LIKE '%s%' is automatically appended by CC6 to the query of the Read Data function.
That part can not be changed.

In case I use your suggestion, I need to change the Read Data function to include the Search fields, but then again, the final query will be automatically appended with 'AND a LIKE '%s%' AND b LIKE '%s%', causing the same -unwanted- result. Maybe it will work if I make my own search field, but that's not the way I want to go.

I want to convince the developer(s) of CC6 that searching with multiple fields -in my opinion- makes no sense using 'AND'.
Example:
The table has a Title and Description column. One of the records has the word 'hello' in the description column.
If I search both Title and Description for the word 'hello', CC6 appends the following to the WHERE clause of the query: Model.title LIKE '%hello%' AND Model.description LIKE '%hello'
This will result in no records found.

While, if CC6 would generate: Model.title LIKE '%hello%' OR Model.description LIKE '%hello'
This will result in the record to be found.

My question will remain: can this be changed in CC6: using OR between search fields instead of AND, generated in the field Search feature using two or more searchable fields?

Ah sorry I misunderstood your question.

As I understand it the Search functionality is pretty basic and cannot be customised to use OR for the search terms (I just code my own search function in PHP). I agree it makes no sense having 1 search field and using "AND" for multiple columns. Changing it to OR shouldn't be a difficult thing to do for Max.

However this should be able to be coded in using the custom WHERE in the Read Data function as above. Don't use CC6's search function at all, instead put it in under WHERE conditions in the Read Data. Have a text field "search_term".

category='123'
AND
(
'Model'.'a' LIKE '%search_term%'
OR
'Model'.'b' LIKE '%search_term%'
)

That will give you the results you want. I'm not sure how the "AND" is appended in the above solution?

You have a point there! Both AND as well as OR will have its applicability.
Thanks for your suggestion, I will give it a try.
KR, Nico

Sorry I edited my response. I only just realised in CC6 you can only use a single search term for multiple columns - I agree this makes no sense. But yeah the Search functionality can be improved considerably, although with Max focusing on CF7 it might not be a priority.

Ah, yes you are right, it is one Search field. So the OR makes more sense.
If it is on the backlog (even if it's for CC7 ;-), I'm happy.

I tend to go one step further and use "+ Rules List" to do a IF .. OR statement for the search field, so that it only add the WHERE condition for the search term if it isn't empty.

Eg. in the "Multiline list of conditions rules", you can add this:

<?php

$search_term = $this->data("search");

if(!empty($search_term)) {
    echo "Title/like:%{data:search}%";
    echo "\nOR\n";
    echo "Description/like:%{data:search}%";
}

?>

Yess! This is the way to go! Thanks!

I used your code to finally get this working example:

<?php

$search_term = $this->data("keywords");

echo "Type/LIKE:new";
if(!empty($search_term)) {
    echo "\nAND\n(\n";
    echo "Title/LIKE:%{data:keywords}%";
    echo "\nOR\n";
    echo "Description/LIKE:%{data:keywords}%";
    echo "\n)\n";
}

?>

It addresses the initial wish I had to always select Type=new records.
Additionally, when a search is done, the AND (... OR ...) is added to narrow down the search.

I had to remove all Searchable fields to reframe CF7 to generate the additional standard conditions and I had to remove the Conditions and Operators that where defined initially with the wizzard. So, the Rules list is the only condition used.
This avoids several problems I had with CF7 parsing operators and place them in a strange order. No problem with that by the way, I got around it.

Please note the term 'keywords' instead of 'search' used in your example.
I changed the (default) Search parameter name 'keywords' into 'search', but the 'keywords' field kept up appearing in the debug log.
Maybe something to look into, but for now it's totaly fine.

Thanks for your help; much appreaciated!