How to avoid a query repeating through a loop?

How to avoid a query repeating through a loop?

Hello all,

I have question about queries. I have a list box that yields a record list of neighborhoods an assigns a true or false to each. I then have a query that runs through a loop. The ForEach node goes to an if node. If the current neighborhood is "true", the query will pull all homes that match the criteria for that neighborhood. If the current neighborhood is "false", the query is bypassed. I can help but think that there is a way to consolidate all of this to one advanced query. Unfortunately I dont think we can query record lists so Im not sure. Any advice would be appreciated. The goal is to speed up my site and loops can take a while to complete.


Hi Julius, 

I'm thinking what you need is a left join. When current neighborhood is true, make the join, if false (i'm guessing no join condition is met) the result for the left join should be left empty.
This way you only loop through one query and when neighborhood is true you already have the inner query in the current record.
If you have no funky logic to test whether to make that query or not, and if the left join condition is possible, then this should solve it.

Kind Regards,
João Grazina
Does the neighborhood recordlist have to be in the form of a table? Or is there a way to bring that data into an advanced query through an input parameter? Sorry for all the questions.
Any input of type RecordList, would turn your query into a performance destroyer.
Record Lists are a temporary way of working with table information. They do not replace tables inside SQL.

Unless you save data to table, execute your query, extract again to RecordList. Is that an option?

" I can help but think that there is a way to consolidate all of this to one advanced query" - The main problem seems to be that whether a neighbourhood is to be included is determined on screen, and hence saved to a record list. Your approach seems sensible, unless the query to get houses is very heavy and relatively immune to the number of neighbourhoods to search through, in which case you could first loop over the record list to produce a comma separated list of neighbourhood Ids (in a single Text variable, probably using a Stringbuilder if you have more than a handful of neighbourhoods), and passing that as an expand inline parameter to your query.
Hi, Nuno Reis got it right: RecorLists will kill your query (and your app by adding viewstate weight).
I suggest that you create an entity (ie: userRequest_tmp), with your user_id and the neighbourhood_id.
In the preparation you must clean any records for your user and then insert/delete every selection with an OnChange action.
From here you will have a pretty straigthforward query with an inner join of neighbourhoods and houses for each user of your app (provided you have the neighbourhoodId in your houses entity).