Sort in the order the array is stored

Wappler General Need Help
1

I have a database field that holds an array of IDs, such as 3,7,2

I have a server action that performs a single database query to get the correct row in my database and return that rows array of numbers.
Inside the same server action I have a multi record database query to another table, and the condition is that the split(",") IDs returned must be IN the array.

All is well and that works perfectly, however the records are retuned in ASC order of 2,3,7 and not as they are stored with 3,7,2

To get around the problem initially I changed my server action removing the multi record query and adding a repeat instead that does the split, and feeds each number into another single database query step in the repeat.

This now returns all the results in the order they are stored.

Here is my issue I am faced with now, I need to somehow use a GroupBy to display the records in two different groupings, which I can get to work if I return to my original method but then I lose the sorting.

I need to have the array read and the number order retained as it is stored and a groupBy together, any ideas?

2

I told you when I saw those fields with comma separated list it would come back to haunt you!

Can you give a sample of the data being returned and how you want it grouped and I will try and think of a solution, no promises but will try

3

Hhahaha, you did warn me, lol.

So if i have (id:1)Apple, (id:2)Car, (id:3)Beer

I have an array of [1,3,2] in my database and I want it to display as 1.Apple, 3.Beer, 2.Car

If i have a multi database query with an array split it gives 1.Apple, 2.Car, 3.Beer

The way around it is to have a repeat with the array split as the expression, then have a single database query inside that, this way it pulls out 1.Apple, 3.Beer, 2.Car as it was meant to be.

Now comes the problem, I want to use a GroupBy in AppConnect to put all Drinks into group A and all Food into group B and all Vehicles into group C. I have to target an Array for the GroupBy and the only Array is the repeat in the server action with the split, and its just not working.

Can I not add the query results back into an array in App Connect or something to use the GroupBy.

4

So what is the actual data coming out of your server action query?

is it
ID, Apple
ID, Beer
ID, Car
(3 records)
or

ID, Beer, Apple ,Car
(1 record)

5

That one

6

I just dont understand why sql has to have any sort function at all, surely it is reading it as 1 then 3, then 2 as the ids, so why cant it just not sort at all.

7

Basically as SQL sees your field “1,2,3” not 1, 2 and 3

8

What I have done to get around the painfulness of this is to add a new column to my table called order, and the reverted to the multi query, and sorted by the order column, so it is working now, but I can see this will cause a nightmare moving onward. But at least it looks like its working now to the client, lol

9

You know what they say, every software patch creates 3 more problem later

3 Likes