Using a query as a condition for an update query

[iamsoops]

Hi, I'm hitting a wall regarding matching data from multiple sources to define my insert query. Let me explain.

As part of my enquiry process I need to identify providers near to the enquiry postcode. My approach to this is to call the nearby outcodes via API to reduce the total number of providers in scope. I will then use another API to get the distance from the provider and sort.

So far, I can retrieve the outcodes linked to the main enquiry and have (with the help of apple and Ben) been able to land this in a subtable (see below). Thats also why I have opened a new post for this, as its a different query.

The next step is to match the providers with the relevant outcodes for the enquiry. To do this using the above example, I want to select all providers with any of the outcodes lists above and then add their provider ID and the enquiry ID to the new table.

I've been struggling with creating array lists and nested repeats but have come to the conclusion I'm being too scatter gun, so if anyone has some general guidance on the recommended approach then I'd really appreciate the steer.

I've tried to use some of the ideas from this article: Filter a query on a JSON field - #5 by famousmag but to no avail.

For what its worth, here is my current attempt (although you will see I have a number of other versions I have tried in the left. Here I am trying to use a repeat to add the result of a seperate query for each outcode to an array but am getting a 0 value error. I

[famousmag]

Hey @iamsoops,

I can't really follow what you're doing in your screenshot but we'll try to solve it in the next steps...

Need some clarification:

A) Have you set a schema for provider_array? Show us...

B)

• In this setValue step I don't see a name... Is there a global name? Show us...
• Here, when using the [0], you always get the first records of query_providers provider_id.
  Is this what you want or maybe you should get the repeat's current provider_id by using the $index (query_providers[$index].provider_id)?

C) Here you use the temp_prov_array but I can't see that anywhere in your screenshot...
What is temp_prov_array?

Let's start with A, B, C and see where it is going in the next steps

[iamsoops]

Hi famousmag. Thanks so much for taking the time to reply and help. Ive already taken a lot from your first response

I have broken out your questions below and tried to answer them clearly. Please let me know if anything needs further detail.

A) Have you set a schema for provider_array? Show us...

No, on this occasion I am working on the basis that the schema will be taken from the first value provided. In a previous iteration I did specify the schema but have reverted.

B) In this setValue step I don't see a name... Is there a global name? Show us...

Yes, I have gotten a little confused about the naming of fields in relation to repeats. Apple kindly pointed me to the following article (Set value - Name and Global Name values discussion - #16 by Apple) which my take away was that I would use globals in the circumstances. See below I have named this one temp_prov_array.

Here, when using the [0], you always get the first records of query_providers provider_id.

OK, this was deliberate on this occasion as I was troubleshooting and wanted to remove a variable. I will now replace with the $index value as directed. Thank you

Is this what you want or maybe you should get the repeat's current provider_id by using the $index (query_providers[$index].provider_id)?

That’s interesting. I hadn’t understood the use of the $index value, I will use that going forward. You will see in the in the earlier repeat that works I have used the “outcode_count” var successfully to increment each repeat.

C) Here you use the temp_prov_array but I can't see that anywhere in your screenshot...

What is temp_prov_array?
See above. I have tried to use that array to hold the output of the query in the set value mentioned above.

Lastly, another point for awareness, I have currently hard set the query to remove a variable in testing too. As you will see I am hard setting the GU18 outcode only and have ran within the query builder and get 5 lines as I would expect. So there is good data there for the repeat to work from.

My end desired endstate would be this, where I am using the first array to query the provider table. However, I have simplified the query to aid the troubleshooting of the array>insert query element:

If you're still reading, thanks in advance

[famousmag]

forgive me buddy but still can't understand exactly what you want to archieve...
All I can do is just pointing things that looks wrong to me

Here is one from your last post:
You search if provider_outcode exists IN {{final_oc_array}}
I CAN'T find any final_oc_array in your api steps, maybe you want final_prov_array

Since you referred to my post, i would recommend to read the steps explaining that I have given:

Filter a query on a JSON field

I have worked on my table “products” that contains a json column (prd_Tags). I pull all the records of my products table (NO OUTPUT) Create an arrayList and set the value to my step-1 query [image] Clear the arrayList (I just wanted to copy the schema, we don’t need the data) I add a repeat with expresion the step-1 query (loop throught the table’s products) Set Value tags_Array and gave it the current repeat’s prd_Tags column value I create a switch and set it to false (this is to check if …

But for your use case, it would be nice (at least for me) to know what exactly you have and what you want to achieve...
I see you create a few arrays, have a repeat step that you add ALL in provider_array in every repeat loop... Is that correct?

Can you just give us:

• the 2 APIs data schema
• the desired output that final_prov_array should have and on which condition

[iamsoops]

Hi famousmag. Thanks again for helping. I hope this isnt too frustrating and I think there is a possibility I was closer to the solution at one point and this API has now become a mess due to my troubleshooting.

I found your article extremely helpful and it has been the source that I have worked from, however, I havent been able to get it over the line yet.

To try and help you help me, I have included a high level summary of the objective/goal below.

The Goal
• User logs enquiry on website submitting Postcode
• New single record added to the tbl_enquiry with details of enquiry and multiple entries of providers from providers_tbl that have the same “outcode” as the original enquiry

High level approach
• Users logs enquiry via webform
• Send enquiry postcode to postcodes.io and collect the “nearest outcodes” to the postcode [repeat_outcodes – This works and I can push this information into a subtable on its own see image 1]
• Use the list of “nearest outcodes” to query the “provider_tbl” for all providers with the relevant outcodes. (There will be multiple outcodes and multiple providers with the each outcode)
• Database insert single record into tbl_enquiry with the main enquiry detail and add multiple records into the subtbl_providers for each provider with a matched outcode.

Answering your queries:
But for your use case, it would be nice (at least for me) to know what exactly you have and what you want to achieve...
I see you create a few arrays, have a repeat step that you add ALL in provider_array in every repeat loop... Is that correct?
Yes, that was my intention as I was expecting each repeat to query a single outcode. From the outcode there would be multiple providers to be added to the array. Then each cycle would add a number of providers to create the full list which would ultimatly be added to the subtbl_providers

Can you just give us: the 2 APIs data schema
So, the APIs are working as I want. I have tested that theory by doing an insert into a subtbl_outcodes. This bit works and uses the final_oc_array and associate repeat [repeat_outcodes] and the final action/insert query.

However, for completeness, see schema below Geolocation:

Outcode list:

Example of the desired output that final_prov_array should have and on which condition a list of providers (their URNs specifically) that have matched the outcodes listed in qry_providers.

Lastly, here is a breakdown of what I think everything is doing/for:

This evening I am going to have another go at getting this working and will update to use the $index field as a start. I'll also tidy everything up and update. If I here from you before then, I'll try and include any pointers/areas that need to be reworked.

Thanks

[famousmag]

I think you're on the right path my friend.

Please, as you said, now that you know what exactly is happening, start a new serveraction from scratch and let us know of the result.

I'm looking forward to seeing your progress

[iamsoops]

Brilliant, thanks.
I'll rebuild tonight and send you my results!

[Hyperbytes]

Have you considered using a custom query using an IN clause and a subquery

Something like

SELECT * FROM table1
WHERE field = value AND  myvalue IN (SELECT myvalue
         FROM table2
         WHERE postcode = ''a value here')

Basically the sub query would do the postcode and the primary query could calculate the distance (using something like the haversign formula)

See below which is now easy with custom queries.

Delivery zones/radius

Quick question as a Wappler Newbie How would I go about setting up a tool that would calculate if a user’s address is within a set radius? For example, on an ecommerce admin panel, the store owner would set up a maximum delivery radius which would save the value to a database field. Once a user goes to checkout, I would need the site to perform a check to see if their address is within the preset delivery radius. If not then the user wouldn’t be able to checkout and pay. Would it be po…

[iamsoops]

Hi Brian. Thanks for this suggestion.

There is a lot for me to digest here, as I haven't experimented with custom queries before. I'm not from a DBA background.

I do think this looks like it could help me going forward though. In the first instance I am going to attempt to work through the method discussed with famousmag above to see if its something I can master. And then I'll take a look at the formula options you have suggested for distance calc as I do have the geolocations available from postcodes.io

Thanks

[franse]

Sorry, trying to understand what do you need, andI need to ask something:

@iamsoops

Use the list of “nearest outcodes” to query the “provider_tbl” for all providers with the relevant outcodes. (There will be multiple outcodes and multiple providers with the each outcode)

If this is what you need:
For each code add item in array.
Then you can query the providers using the IN statement, so the query will bring only the items where code is in the array.

Something like that?

[iamsoops]

Hi famousmag - As per your suggestion, I have rebuilt the api clearing out all the old environment variable, troubleshooting steps etc. I have progressed it to the point where its stopped working and gone no further to avoid confusion. Below are a couple of videos demonstrating what works and what doesnt.

1. Create outcodes array - Add outcodes subtable entry
   Up to this point everything works. You will see I am able to pull the relevant data from the API, create the array which is passed into the subtable and linked. So this array is working.
   

2. Adding query using items from the outcode array
   The problem begins when I enable the add all array action which is calling the contents of the query direcftly above.

For clarity, what I am trying to do here is run a query each time the repeat runs selecting a single line from the outcode array. In this example that would be KT10 as the first one. The repeat would then run the query for each of the following outcodes within the arr_outcodes_final list. This is why I have selected equals rather than in for the condition. I am only expecting a single outcode to be passed to the query each time the repeat runs. I am using add all rather than add as there will be multiple records in the query (see demo at the end of this post)

The error received is: 2024-06-13T20:32:52.693Z server-connect:server Got error? Error: arraylist.addAll: value is required.

You will see in the video I have stringified and capitalised the value being fed into the query to see if that could be the issue.

I am using $index throughout, its marvelous! So thank you for pointing that out

As ever, any thoughts welcomed and gratefully received.

I believe if I can get the query_list_providers_by_outcome to work using arr_ourcodes_final as the condition I will be on my way. You will see from my first video that the method for adding the subtable works if the array is populated.

Below I have included some other screenshots that could be useful and hope now I have tidied everything up it will be easier for you to understand my logic/names:

Temporarily, I have manually passed KT10 into the query and ran it, you will see this pulls plenty of results that I would expect to be added to my array_providers. I have now removed this manual condition.

[iamsoops]

Hi franse. Thanks so much for looking at my problem and responding.

I think you are right in that the IN query should work and remove the need for the repeat. However, I did try this previously when I was troubleshooting and had no luck.

I was posting a lengthy reply as you added your comments which will give a bit more detail if you have the time to help.

A quick question though if I were to use the IN method. I believe the query would look something like the ecxample below and would select all items from the providers table matching any of the outcodes in the array:

To use this query in my final update query that created the record in the main table and multiple records in the sub table, would I need to push the query into an array or should I be able to just reference this query. I havent been able to get either to work, however, if you have a method that has worked for you I will focus on that.

Thank you!

[franse]

@iamsoops

However, I did try this previously when I was troubleshooting and had no luck.

Please make sure when you use IN array, to use join and split like: array.join(',', 'user_id').split(',')

Something like:

Replace user_id for code, or the value you need to filter

---

@iamsoops

would I need to push the query into an array or should I be able to just reference this query.

You can then use a repeat on the query, if I didn't misunderstood it.

[famousmag]

Hey James,

I will try to examine it but my first thought is:

Can you show as the output of this query?
DOES this query ALWAYS return values? Or you probably have to first check if it has result and then add to arraylist?

*EDIT:
This is the query? The expression in your condition is not correct...

What you want to filter here? You can't use the $index like that...

By the way, Brian has posted a very good guide for ArrayLists:

How to use Server Action Array Lists

1.0 Server Connect Array Lists Introduction Server Connect Array List management provides a mechanism to manipulate Array Lists at server level. This is ideally suited to the preparation of text for output to other components such as emails and for managing non database data sources such as CSV files. It is important to note that some Array List functions require a text array list and do not work on arrays of objects. In this tutorial i shall use TEXT ARRAY LISTS as some actions require th…

[iamsoops]

So, I think Ive come to the conclusion that its not possible to pass a query result directly into an array.

See video below. In summary I have hard set the query to a specific outcode and demonstrate the results in the query builder.

The process has been seperated from all api calls and repeats and only runs a static query, creates and array list and attempts to add all items in the query result to the array.

I've been working on this for 3 weeks now and think that fundamentally this option isnt possible (without some other intermediary step between the query and the array)

Thanks for everyones help to date. I'll review the other points raised but having simplified this down to the basic requirement, it appears to be unsupported.

[famousmag]

@iamsoops

change the condition to {{'GU9'}}

[iamsoops]

@famousmag

{{'GU9'}}

Hi @famousmag - Thanks for replying again and so quickly.

I've tried your suggestion and unfortunately it hasn't worked. You will see from the video that running the query within the builder with this condition in place no longer brings any results and also results in the same no value error in the console.

[famousmag]

please, erase the condition and fetch everything (all records)

Can you show us the results of the query?
Specificaly I want to see the provider_outcode values

[iamsoops]

Of course, here you go!

Here is the source table too:

[famousmag]

can you run it on the browser please?