External filtering using like/contain

Luck Kanthatham-2308338

Is there a way to filter records using something that's akin to SQL like '%%'?

So the JSON is something like this:

"op": "contain"
"right": {
              "operand": ""
            }

I searched high and low but couldn't find an answer to this.

Also is it possible to do case-insensitive comparison?  I tried to_lower() but that only converts the operand to lowercase not the field itself.

Thanks in advance.

Michael Udinski

Hi  that would be the "Like" operand in the Query All Records function (Query all records > Filter > By Custom Query)

[CleanShot 2021-11-11 at 11.45.14.png]

Luck Kanthatham-2308338

Thank you, Michael, for your help.  I was able to get the "like" operator to work using case-insensitive comparison.  However, when using it in the JSON for external filtering, the "like" operator does not ignore the case.  This seems to be a bug.


"op": "like"
"right": {
              "operand": "%%"
            }

It would only work if the casing matches.

For example, if I try "first" when the field value is "First batch", it does not return any match.  However, if I use "First", then it returns the results fine.

Using the example you provided, when the filter is applied on the where clause, it works fine.

Michael Udinski

Apologies, for the confusion - like is going to be case-sensitive. In this use case, you will want to use "ilike" for case-insensitive operator 

Fernando Andrade

Hi ,

I'm also trying to use this functionality, but I must be doing something wrong. Here is my json:
{
"external": {
"expression": [
{
"statement": {
"left": {
"tag": "col",
"operand": "items.name"
},
"op": "ilike",
"right": {
"operand": "%lista%"
}
}
}
]
}
}But I'm getting this error message:
[error324234.png]
and here is the normal result I get without the filter:
{
"result": {
"itemsReceived": 2,
"curPage": 1,
"nextPage": null,
"prevPage": null,
"itemsTotal": 2,
"pageTotal": 1,
"items": [{
"id": 1,
"name": "Lista de teste 1",
"type": "Lista dinâmica",
"created_at": "08/03/22",
"deleted": false,
"deleted_at": "31/12/69",
"_created_by": {
"id": 61,
"first_name": "Joenne"
}
}, {
"id": 2,
"name": "Lista de teste 2",
"type": "Lista estática",
"created_at": "08/03/22",
"deleted": true,
"deleted_at": "08/03/22",
"_created_by": {
"id": 55,
"first_name": "Fernando"
},
"_deleted_by": {
"id": 55,
"first_name": "Fernando"
}
}]
}
}can you help me?

Luck Kanthatham-2308338

  Sorry, I'm not Michael but hopefully I can help.  Instead of "items", use the name of the table/object instead.  If your table name is "user", then the value should be "user.name".

Fernando Andrade

 
It helped a lot. Worked perfectly.

Thanks

Fernando Andrade

When including the sortBy filter in the same json it works perfectly with the fields returned from the same table, but I can't sort the result by the fields obtained through addon.

Is there any way to do this?
{
"external": {
"expression": [
{
"statement": {
"left": {
"tag": "col",
"operand": "audience_custom_list.name"
},
"op": "ilike",
"right": {
"operand": "%lista%"
}
}
}
],
"sort": [
{
"sortBy": "user.first_name",
"orderBy": "asc"
}
],
"page": 1,
"per_page": 10
}
}

Luck Kanthatham-2308338

 I'd say you probably need Michael on this one.  I haven't tried it with addon.

Michael Udinski

 You can't sort the main query by an Addon. Addons are a second pass after the main query has already executed.