NodeJS Array Showing Extra Records

Hi All,

I’ve got an unusual one here that is a head banger (for me anyway). I have an array stored in a VARCHAR field (UsersCats) in a users table. The array contains Primary Key IDs for another DB table - WorkCats.

I have a repeat that uses Server Connect to list the WorkCats table as the Expression, then a show/hide to only show the rows that match the Primary Key in the users UserCats field.

It seems to display correctly, however, giving it a closer look its adding another 2 rows to the query.

Heres a view of what Im seeing:

Screenshot 2023-02-16 102302

You can see from the blue bits that the following have been added, yet they don’t exist in the UsersCats array. Its added a ‘2’ and a ‘4’.

Ive tried a lot of stuff, including converting UserCats to a string, a number etc but it all shows the same result.

Example:

Screenshot 2023-02-16 102524

Screenshot 2023-02-16 102449

I’m now stumped.!

Anyone have any ideas…?

Are you storing an actual array or a comma separated list of values in this database field?

Also - are you filtering this on the client side and if yes, why not filter a query using the value(s) and sending the results filtered to the front end?
This usage of show/hide and a repeat region seems like a weird implementation to me.

Its a comma separated list of values in a DB VARCHAR field. I was planning on using the WorkCats serverconnect elsewhere on the page. TBH, I didn’t even think about filtering server side, which makes a lot more sense.

Has anybody done this on the server side? I cant seem to find any documentation for this type of action.

Repeat your first query, add the second query inside the repeat. Use the field with the comma separated values as a filter for the nested query with the flatten formatter applied to It and IN condition.
Or if you don’t need to repeat the first query, skip this step and the second one directly after it.

1 Like

Why not ditch the comma separated list and use a subtable of single elements instead?
I always find it a better solution in the long term and much more flexible

1 Like

I agree entirely with you Brian, however I’m working with a very large legacy database so have no choice. Its lasted well over the years but this particular part of it is proving to be a pain the backside.

1 Like

Wowza, that was a tough one for me. I got the solution, so here goes in case anyone else has similar issues:

As per @Teodor suggestion, I did the work on the server side (DUH!) which makes total sense, I set a value with the comma separated list in it, then used that as a query to pull out the details from the categories table:

Screenshot 2023-02-17 102717

I had to use a custom SQL query so I could use FIND_IN_SET - this was the only way I could find that would get me the actual amount of rows.

Screenshot 2023-02-17 102644

Once done, I just had to create a repeat on the page to pull out the records:
Screenshot 2023-02-17 102738

end result was perfect, it now displays the correct amount of records for the amount of IDs in the comma separated list:

Screenshot 2023-02-17 102536

PHEW…! That was a bit of a pain but the end result was way better than my original.

Thanks again to @Teodor and @Hyperbytes for pitching in, as we all know, it’s nice to have another set of eyes on something when you’re struggling - much appreciated…