I have a query with multiple optional parameters, where the presence or not of each parameter determines the condition but also sometimes a particular join. If I was writing this from scratch, and rather than multiple IFs, I would probably use arrays to dynamically build the query with prepared statements something like
sql = "SELECT * FROM taxa t";
params = [];
joins = [];
conditions = [];
if (event_id) {
joins.push("LEFT JOIN occurrences o ON t.taxon_id = o.taxon_id AND o.event_id = ?");
params.push(event_id);
} else {
joins.push("LEFT JOIN occurrences o ON t.taxon_id = o.taxon_id");
}
if (filter) {
conditions.push("(t.name LIKE ? OR t.scientific LIKE ?)");
params.push(`%${filter}%`, `%${filter}%`);
}
if (joins.length) sql += " " + joins.join(" ");
if (conditions.length) sql += " WHERE " + conditions.join(" AND ");
and hence would develop the sql query string that way.
In Wappler I have looked at the array actions but am unclear how to use them for this, and run the resultant query. I'd appreciate any pointers for how to go about this.
I presume that should cover optional parameters, but not the joins based on optional parameters? In this case I have parameters which control whether or not there is an additional join, so I'd have to check for these in the server action anyway?
For example, if event_id is specified then there is one particular join, otherwise a different join. Similarly a different parameter determines a join on a different table or not.
Oh. I missed that part.
In such a case, create a custom module, rather than playing with arrays and other Wappler steps.
You will get much better control over the string you generate, and you can make it so that its parameterized. It will require additional efforts and knowledge though.
Another suggestion would be just create separate query step for every join condition. You can keep the name of the steps same, so that the final output can be bound in the UI without any hassle. This would be much more maintainable IMO.
I might be misunderstanding what you're trying to do here but if you're comfortable writing the SQL I would just click on 'custom' in your query and put it there. I would use a set of left outer joins to everything and then CASE statements or similar to apply conditions depending in your exact requirements. Works really well for me when the UI can't handle a more complex query. You can add variable parameters as needed.
Yes, I'm very comfortable doing it in SQL but keen to see how far I can get using Wappler's tools! I think this one will be a compromise, and it's been a useful learning exercise trying out different options