How can I use the results of one query as a filter for another?

Wappler General How To
#1

Hello

I have not encountered this before, but now there is a need. I need to use data from one database query as a filter in another query inside the server action. How to do this correctly in Wappler?

Thank you in advance

#2

You select the results of the first query just like you select a POST or GET, etc. by selecting it in the picker. You will have to do some formatting depending on what you need.

Feel free to post the specifics.

1 Like
#3

I have a table of products. Several documents can be attached to each product. Each document can have multiple products. When a user opens a document page, I take the document id from the url as the primary filter. Then on the server side I make a request to the database in which I get all the products that are listed in this document. I need to use this data in another query to build a table of all the documents that are connected to the products received in the first query.

I have already tried to do as you recommend, but I made a mistake when setting up the filter conditions and did not get any results. I thought I was doing something wrong. After your message, I returned to new attempts and found errors. Now I get the result in the form of an error (which is already pleasing).

I set the first query to the database as a filter in this form:

1
11433×895 143 KB

At the output I get the following error:
2
2963×380 72.1 KB

What needs to be fixed?

#4

Please check:

#5

Hi @Teodor

Yes, I have already studied this material and actively used it in another task. This really works perfectly. But the fact is that the repeater method does not fit the current task (or I do not know how to adapt it correctly).

What do I need now:
There is a document A. this document has products with id2, id5, and id7 attached. I just need to get these IDs in the first request by doing a simple filtering of products by document id A. Then I will take these IDs and apply them as a filter in the second request and filter all the documents. As a result, I will get a list of documents that can be arranged in a table by document type, so that first the table displays cash account, then certificates, then invoices.

When using the repeater, a table is built in which the specified products will be a kind of block within which all the documents attached to it will be listed, then the next product, etc.This is not what is needed.

#6

Your explanation is really unclear and confusing. What exactly do you mean by:

What do you attach? Where? How?

#7

I use the most common relational approach of many to many relationships:

3
31576×300 45.2 KB

In the first query, I filter the Link table using the ID of the document that the user opened and get the IDs of the products that are associated with this document.

In the second query, I want to use all received product IDs as a filter to get all related documents.

My question is very simple:
Can I pass the values of one query as filter values to another query within a server action without using a repeater?

#8

So what value you want to pass from the first table to the second one?

#9

My first query:

4
41093×672 72.5 KB

I get product IDs from it. I want to use this data in the second query as filter values:

5
51087×646 81.4 KB

#10

Will this from Paul (@psweb) be of any help Dynamic Dependant Dropdown Menus?

#11

just {{query}} won’t do the job.
The IN operator expects an array. So you will need to use:

{{query1.join(",", "hub_main_id").split(",")}}
2 Likes
#12

Thank you very much @Teodor, this is exactly what I needed. It works perfectly and exactly as it should.

But I would like to clarify something:

  1. What topics should I study and what wappler documentation should I read to better understand the expressions used in Data Formats? I often don’t understand how and what to apply to an expression in Data Formats in order to get the desired result.
  2. In the example you provided, I tried to build this expression entirely using the Data Formats tools. But when I apply join I can’t select the value "hub_main_id" via the Wappler visual interface in Property, only write it manually. Am I doing something wrong, or is it really impossible to install it through the interface, but only register it manually?
#13

Well that really depends on the specific case. Pretty much all of the formatter options are self explanatory, but special cases like yours cannot really be documented.

This needs to be entered manually, as the query values are not accessible directly, if it’s not in a repeat.