Or condition criteria not working as in template

[alanski66]

Description

This works in twig:

    {% set results = craft.entries().section('imdData').search('lsoacode:E01026960 OR ladnm:*Northampton').all() %}

but this doesn't in the api - it only pullls back the admindistrict data

 'search' => 'title:'.$searchTerm->admindistrict.' OR lsoacode:'.$searchTerm->lsoa,

What am I missing?

[brianjhanson]

Thanks for reaching out! At first glance, and in an initial test, things are working for me on a clean install.

Any chance you can post a full example of your element-api.php file? Specifically, I'm curious where $searchTerm is coming from.

[alanski66]

So my incoming query query looks like: ?query=%7B"admindistrict"%3A"North%20Northamptonshire"%2C"lsoaname"%3A"Corby"%2C"lsoa"%3A"E01026959"%7D and endpoint looks like

'api/myapp-search-imd-data.json' => function () {
            // settings
            $section_handle = 'imdData';
            $q = Craft::$app->request->getQueryParams('query');

            $searchTerm = json_decode($q['query']);

            $criteria = [
                'section' => $section_handle,
                'search' => 'title:*'.$searchTerm->admindistrict.'* OR lsoacode:'.$searchTerm->lsoa,
                'limit' => null
            ];

            return [
                'elementType' => Entry::class,
                'criteria' => $criteria,
                'paginate' => false,
                'pretty' => true,
                'cache' => false,
                'transformer' => function (craft\elements\Entry $entry) {
                    return [
                        'id' => $entry->id,
                        'title' => $entry->title,
                        'localauthority' => $entry->ladnm,
                        'lsoacode' => $entry->lsoacode,
                        'lsoaname' => $entry->lsoaname,
                        'mapitAreaId' => $entry->mapitAreaId,
                        'cicName' => $entry->cicName,
                        'fundableRegion' => $entry->fundableRegion,
                        'openForFunding' => $entry->openForFunding,
                        'geojson' => json_decode($entry->geojson)
                    ];
                },
            ];
        },

If I manually add the values as above in criteria

lsoacode:E01026960 OR ladnm:*Northampton'

then i get expected "OR" results. As soon as I am passing in

 'search' => 'ladnm:'.$searchTerm->admindistrict.' OR lsoacode:'.$searchTerm->lsoa,

then it doesn't seem to work. I though I needed to escape or double quote spaced values but this hasn't resolved it either

[brianjhanson]

It looks like the manual values you're adding

lsoacode:E01026960 OR ladnm:*Northampton'

aren't the same as the search query in the criteria

'search' => 'title:*'.$searchTerm->admindistrict.'* OR lsoacode:'.$searchTerm->lsoa,

I'd expect it to be

'search' => 'lsoacode:'.$searchTerm->lsoa.' OR ladnm:*'.$searchTerm->lsoaname,

in order to get the same results.

I'm assuming that's just a copy/paste error though.

I think the real issue is that your query has an encoded space "North%20Northamptonshire" within it, which makes its way into the search query as title:*North Northamptonshire* OR lsoacode:E01026959.

That space will cause the query to get parsed into three tokens, which ends up with a query that's looking for something like title:* North AND (Northampton OR lsoacode:E01026960).

You should be able to throw quotes around it, but the caveat there is that you'll be searching only for entries where the title contains the exact phrase from the query.

'search' => 'title:"'.$searchTerm->admindistrict.'" OR lsoacode:'.$searchTerm->lsoa,

[alanski66]

Thanks Brian.I've been through quoted variations to deal with the space and will dump it on screen to check what is fed in but i still cant get any joy. I will post some different criteria next to get your thoughts. thx

[alanski66]

So here is my query again: myapp-search-imd-data.json?query=%7B"admindistrict"%3A"North%20Northamptonshire"%2C"lsoaname"%3A"Corby"%2C"lsoa"%3A"E01026959"%7D

and my criteria: 'search' => 'lsoacode:'.$searchTerm->lsoa.' OR ladnm:*\"'.$searchTerm->admindistrict.'\"', Trying to quote the spaced values doesn't seem to help.

So i ran and tested both of these:

'search' => 'lsoacode:'.$searchTerm->lsoa.' OR ladnm:*\"'.$searchTerm->admindistrict.'\"', 'search' => 'lsoacode:E01026960 OR ladnm:*\"North Northamptonshire\"',

Still no or condition is active. If i remove the space from North Northamptonpshire then i get the OR condition.

So this has become a long winded way of asking "how do I escape a space in OR conditions passed into element-api search criteria?"

[brianjhanson]

You'll need to ditch the * if you're using quotes, those two can't be combined, but quotes should look for the exact phrase contained within it anywhere within the field. You also shouldn't need to escape the " because you're using ' to contain the string.

'search' => 'lsoacode:E01026960 OR ladnm:"North Northamptonshire"'

is the query you'll want to end up with.

[alanski66]

Interesting. 'search' => 'lsoacode:E01026960 OR ladnm:"North Northamptonshire"', only gives the first part of the query when calling the api. ie the lsoacode results are returned and nothing else.

Dumping the query shows what I guess should work but it doesn't. array(3) { ["search"]=> string(52) "lsoacode:E01026960 OR ladnm:"North Northamptonshire"" ["limit"]=> NULL }

[brianjhanson]

Just to make sure, if you copy and paste that exact string into your twig element query, do you get the results you expect?

This plugin applies the criteria you provide directly to the element query so there's not a lot of room for the query to get messed with, which is why I have a feeling the issue is more around the parsing and passing of the query string.

[alanski66]

$criteria = [ 'section' => $section_handle, 'search' => 'lsoacode:E01026960 OR ladnm:"North Northamptonshire"', 'limit' => null ]; Using the string as above only gives ther lsoacode data not the ladnm. I can confirm it's not what I was expecting:)

[brianjhanson]

It's possible you'll need to break out of the search param in order to get the results you're going for. It's probably worth refreshing on the search docs though.

If you do need to break out of the search functionality, you should be able to create a where query within the criteria block as well. You'll need to get the actual column names of the fields from the DB in order to formulate that query though. The code would look something like the following.

'api/myapp-search-imd-data.json' => function() {
    // settings
    $section_handle = 'imdData';
    $q = Craft::$app->request->getQueryParam('query');

    $searchTerms = \craft\helpers\Json::decode($q);
    $where = ['or'];

    foreach ($searchTerms as $handle => $value) {
        // If you're looking for something that's not a custom field.
        // if ($handle === 'title') {
        //     $where[] = ['like', 'title', $value];
        // }

        $field = Craft::$app->getFields()->getFieldByHandle($handle);
        if ($field) {
            $column = ElementHelper::fieldColumnFromField($field);

            if ($column) {
                $where[] = ['like', $column, $value];
            }
        }
    }

    $criteria = [
        'section' => $section_handle,
        'where' => $where,
        'limit' => null
    ];

    return [
        'elementType' => Entry::class,
        'criteria' => $criteria,
        'paginate' => false,
        'pretty' => true,
        'cache' => false,
        'transformer' => function(craft\elements\Entry $entry) {
            return [
                'id' => $entry->id,
                'title' => $entry->title,
                'localauthority' => $entry->ladnm,
                'lsoacode' => $entry->lsoacode,
                'lsoaname' => $entry->lsoaname,
                'mapitAreaId' => $entry->mapitAreaId,
                'cicName' => $entry->cicName,
                'fundableRegion' => $entry->fundableRegion,
                'openForFunding' => $entry->openForFunding,
                'geojson' => json_decode($entry->geojson)
            ];
        },
    ];
},

More information about how to formulate the where query [can be found in the Yii docs](https://www.yiiframework.com/doc/api/2.0/yii-db-queryinterface#where()-detail)

[alanski66]

Thanks for the generous response Brian. I'll definitely look into this. Can you confirm that the behaviour I am seeing should be the same as the template and does this indicate that OR is not working as expected in criteria search?

[brianjhanson]

The search via twig and via the criteria search should function exactly the same. My best guess is there's something happening in the parsing and preparing step. Either way, the where parameter will give you a lot more control & flexibility vs. the search parameter. It just requires a little more MySQL knowledge.

[alanski66]

Not to labour the point.....but if the criteria string is fed in with manual values like this: $criteria = [ 'section' => $section_handle, 'search' => 'lsoacode:E01026960 OR ladnm:"North Northamptonshire"', 'limit' => null ]; then it should work but it doesn't. Is that right?

[brianjhanson]

Correct. Maybe I didn't follow. Are you saying that

 $criteria = [
    'section' => 'imdData',
    'search' => 'lsoacode:E01026960 OR ladnm:"North Northamptonshire"'
];

and

{% set entries = craft.entries().section('imdData').search('lsoacode:E01026960 OR ladnm:"North Northamptonshire"').all() %}

are producing different results?

[alanski66]

No template and element api with those manual values are producing the same - undesired - result. So the universe is ok:)

[brianjhanson]

Glad to hear it! I'm going to close out this issue as things seem to be working as expected as far as the plugin is concerned. If you have any other questions don't hesitate to reach out.