Operator for filtering "any from array" in "array"?

Anna Sho

Hello Xano team!

I query all records from "products" DB and I need to filter them by input var array

products.tags - array of tags
input - array of tags

I need: any of tag from input is in product.tags array[Screenshot 2022-10-13 at 13.49.18.png]which operator to use if both sides are arrays? 

pete letkeman

 you probably want to use the In, Not In, Contains, Not Contains, Overlaps, or Does Not Overlap operators

Anna Sho

that doesn't work, if left and right parts both are arrays(

pete letkeman

 does the plan that you are on support Lambda?
[image.png]If so then that may be of some help.
Otherwise, you may have to loop through the two arrays before or after the database request.

Michael Udinski

Hi  - it sounds like you want to use OVERLAPS for this!

IN and NOT IN are used when comparing a single value (scalar) to a list (array).


OVERLAPS is used to compare arrays to arrays!

Anna Sho

Thanks! Looks like im getting error because of some other reason when do OVERLAPs
[Screenshot 2022-10-14 at 08.58.15.png]input tags_input [8,12] and there is record like [8,12,13....] tag_if array
so that should work

Error:[Screenshot 2022-10-14 at 08.55.16.png] can you help me to understand what that error mean?

Michael Udinski

 that should work as long as products.tag_id is the same type of array []integer...

Can you please open a ticket with our support chat so we can gather more information and take a closer look?

Anna Sho

  i sent message to support

tag_id is integer  list table ref
tags input too
[Screenshot 2022-10-14 at 10.13.31.png][Screenshot 2022-10-14 at 10.14.46.png]

Mamad

Hi @Michael Udinski , i'm having the same issue , any update regarding this ?

here the error message : stripos(): Argument #1 ($haystack) must be of type string, array given

my two tables are integer arrays

Michael Udinski

Hey @Mamad care to provide debugger details and function stack info? Typically that error means you are applying some form of text string manipulation, filter, operator, etc. to an array

Mamad

sure @Michael Udinski , here a screen , im comparing a integer array ( as input ) with an other integer array

Michael Udinski

hi @Mamad - it looks like you just flip the order of your expression to start with your DB.

So WHERE

db: worker.fk_searched_jobs OVERLAPS input:test[]

Mamad

Hi @Michael Udinski , yes sorry !

Here the good error message :

{"message":"SQLSTATE[22023]: Invalid parameter value: 7 ERROR: cannot extract elements from a scalar (SQL: select \"worker\".* from \"mvpw7_82\" as \"worker\" where (((ARRAY(SELECT (jsonb_array_elements_text(\"worker\".\"xdo\"->'fk_searched_jobs'))::bigint))) && ((ARRAY[1::bigint,2::bigint]))))"}

Michael Udinski

What kind of data typeis fk_search_jobs? can you provide any screenshots?

Mamad

sure. Here a screenshot

Michael Udinski

ah I see the issue now: you won't want to have your arrays as "nullable". I'd turn this setting off and update any existing null values to be empty arrays in your DB.

Mamad

@Michael Udinski we are using this nullable because our Frontend tool is Jetadmin and they told us there is an issue on Xano side. If nullable integer[] parameter is unfilled Xano applies [0] value to it (what is unexpected).

dhonato

Hi, I have the same issue.
I have a DB with a lot of registers, and I need to filter a text field named object.
But this search has more than one word or term. I can´t do it with an array. I tried these suggestions without success.
could you help me?
tnks.

Michael Udinski

hi @dhonato can you clarify more of your question? You're trying to return results of a text field that have the word object in it?

dhonato

Hi Michael, thanks for your reply.

• object (objeto in portugês BR) is the name of the search text field

I receive many purchasing opportunities from an API described in a text field (objeto) and saved in the table.

Each user is interested in a type of opportunity, and needs to search in this text field, according to the keyword or search term corresponding to what they are interested in selling (usually there are more than one keyword and terms).

What I need is a return of all opportunities that contain the selected keywords.
Is it possible?

Michael Udinski

How are you passing in the keywords? Are they individual text inputs? Or something else?

dhonato

Micheal, when I use individual text input, works fine.
But I need more words in the same search, in this case, I receive a list of words comma-separate. This is the point.
I tried to make an array, but I don't know how to compare it with a description of opportunity.

Michael Udinski

So you have a list of search terms, which is an array input.

Then you have a field you are trying to search, objects, will the search terms match the value of the what's in the objects field or will it be words inside a longer string or sentence?

turgay

Hello, I'm facing a similar issue related to this topic.

In my table, I have a text array, and I also have a single text input.

When I use the following query: "where bayiKodu in cashbackRate.branchCodes," it appears to only check the first element of the branchCodes array.

To clarify, I have a branchCodes text array like this:

• If I provide bayiKodu as 81, it returns the corresponding line.
• However, when I provide bayiKodu as 82, it returns an empty result, indicating that it cannot find a match.

How can i resolve these issue? I attempted to transform the bayiKodu text into a single-element array and also try with overlaps, but the problem persists.

Michael Udinski

Hi @turgay - sounds like this is something else but nonetheless, your expression should work. Just to confirm your branchCodes is a text field list? I did a quick test on my end and saw no issues.

Is this the only expression in your query or do you have others?

turgay

Hello @Michael Udinski, I appreciate your quick response. Its not only expression in my query.

In my cashbackRate data type, I have various fields, including courseIds (text array), schoolIds (text array), branchCodes (text array), barkod (text), and status (bool). Additionally, the return type is an aggregate that groups by barkod and cashbackId while checking for the maximum cashbackRate field.

In my test scenario, schoolIds and courseIds are empty, and the status is set to true. Consequently, they filter correctly in this context.

Michael Udinski

@turgay I see a lot of different dependencies in your custom query expression. My hunch is that you will see different results based on the input value.

I recommend first, doing a simple test: Create a separate query with only the expression you first wrote in about. "where bayiKodu in cashbackRate.branchCodes," I'm very confident you will find this logic works soundly.

Once you confirm this works. You will want to examine your expression with the many dependencies. You can always clone the query and eliminate things one by one if you're trying to determine what the dependency is that gives you different results in this use case.

turgay

If i try using different table and start from strach, i can get the result like you.

I am developing in such a way that I have eliminated the dependencies. However, if I add a filter for branchCodes, it will not return the correct result, except for the first element of the array that matches.

When I input 96 for branchCodes, I get the correct result:

It return DFAULT113 correctly below, with cashbackRate: 5 . You can see my table:

But when i changed to input branchCodes 97, i cannot get any kind of these line.

DFAULT111 include empty branchCodes thats why we can see in result:

My branchCodes array also there:

I also try using direct database query with these code:

and i get result using branchCode"96" but, i cannot get any result using "97" it return empty array:

i couldn't understand the issue 😞

dhonato

Michael, here are the answers

?"So you have a list of search terms, which is an array input."

Yes, exactly!!!

?"Then you have a field you are trying to search, objects, will the search terms match the value of the what's in the objects field or will it be words inside a longer string or sentence?"

The second option is right. The words or terms do not need to coincide exactly with the field, but they must be contained in the field.

—

The exact match will be given by the user himself when reading the field in the front end. In this case, the text has subjectivities that need human evaluation, the search is a helpful filter since there are many records.