Custom SQL query with multiple optional parameters and conditions

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.

1 Like

Here's a simplified version of what it might look like in Wappler:

  1. Input Parameters:

    • event_id
    • filter
  2. Set Base Query:

    • Set sql to SELECT * FROM taxa t.
  3. Set Joins:

    • If event_id is present:
      • Push LEFT JOIN occurrences o ON t.taxon_id = o.taxon_id AND o.event_id = ? to the joins array.
      • Add event_id to the params array.
    • Else:
      • Push LEFT JOIN occurrences o ON t.taxon_id = o.taxon_id to the joins array.
  4. Set Conditions:

    • If filter is present:
      • Push (t.name LIKE ? OR t.scientific LIKE ?) to the conditions array.
      • Add %${filter}% to the params array (twice).
  5. Build Final Query:

    • Concatenate the joins and conditions arrays to the base query.
    • Add WHERE clause if conditions are present.

Thanks. It is mainly the last bit I am stuck on. After

how do I

  • if joins array isempty set sql += joins.join(' ')
  • run query sql
    ?

Creating query in strings - when params might be involved - sounds like inefficient code.
Have you seen this: Using condition with conditional fields in custom queries - #4 by sid ?

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.

1 Like

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.

1 Like

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