In this tutorial we will show you how to nest database queries. This is useful when you have two separate tables, one containing your categories and another one containing your products. You can nest the products query in the category query and filter it by the category ID. First, let's create a server action (1-2). Add a database connection step (3-5).
Setup your database connection (1-2).
Right click the database connection step(1) and add a database query (2).
This is the query which will return the categories (1). Make sure to turn the output option off. This is important as keeping it on may confuse you later when binding data on your page (2). Setup the query options (3). We select and add the categories table (4-6). And then we add all the table columns, to the selected columns list.
We add all the table columns to the selected columns list (1). Click ok (2).
In order to nest the products query inside the categories query we need to add a repeat (1). Under core actions (2), add repeat (3).
Add a name for it (1). Select an expression for the repeat (2-3). The expression should be the categories query, which we created already.
The output option for this step must be on as we will use the repeat step as a source for the main repeat region on our page. Then, from the output field, select the database table columns which you need to be available for binding on the page (1-2).
Inside the repeat step, right click steps(1) and add a database query (2-3).
This is our products query (1). The output option should be enabled for it (2). Setup the query options (3). Select and add your products table here (4-6). Then add the columns you need (7). Open the filters tab (8).
Add the column you'd like to filter your products by. We will filter them by category name (1-2). Select a value for the filter (3).
Select your value from the repeat step which returns info from the categories query (1-2). Click ok (3).
Save your server action(1) and open the app connect panel (2).
Add app connect to your page.
Add a new component (1). Under data (2), select server connect (3).
Select your server action in the action field (1-3).
Click on your page(1) and add a repeat region (2-4). This will be the main repeat region returning your categories.
Select the expression for your repeat region (1). Under server connect, data, select the repeat step which returns your categories (2-3).
Click inside the repeat(1) and bind the category name (2-4).
Click inside the repeat region(1) and add another repeat inside (2-4).
Select the expression for it (1). Select the products query which is nested in the repeat (2-3).
Click inside the nested repeat(1) and bind the data about your products inside (2-4).
And we are done! Let's preview the results.
You can see that each product is nested under its category. That's how easy it is to nest database queries using server connect and app connect.