Named query with many conditional WHERE clauses without query string?

Good to know thanks @PGriffith . Time to go read up on import threading now.

I recommend a cache using a dictionary in the top level of a script module. If you want the cache to survive script restarts, use system.util.getGlobals(). (But watch out–late versions of v7.9 and all versions of 8.0 have a buggy getGlobals().) For this particular application, I would cache a CompletableFuture and an expiration timestamp. The key into the cache dictionary would be a tuple of all of the filter criteria extracted from the message payload. An example script is here:

https://www.automation-pros.com/ignition/query.py

Note that jython’s dictionary is fully thread-safe and this code needs no locks. At worst, you might have the same query running simultaneously. All but one of the resulting futures will be discarded.

Edit: Just updated the example with a function to prune the cache of expired entries.

2 Likes

It has been a little while since I’ve implemented something like this, so my apologies in advance if the syntax isn’t quite right… What I would do for optional filters is leverage short-circuit evaluation for the parameters and feed None/NULL when the dropdown is set to All. The query would then look something like this:

SELECT column_a, column_b FROM table
WHERE 1=1
  AND (@for_filter IS NULL OR for_column=@for_filter)
  AND (@rsvp_filter IS NULL OR rsvp_column=@rsvp_filter)
  AND ...

This general method lets you bundle in all of the filters (and not have to maintain separate queries) and automatically includes rows for a given where clause where the filter parameter is NULL.

Hope this helps…

3 Likes

I’ve tested something like this with sqlite, MySQL, and MSSQL and it generally works well (not sure on the performance though).

You will have to convert all parameters to strings in the named query (may or may not be a deal breaker depending on your db design), and then for fields that could have an ‘All’ value, just use ‘%’ as the parameter to pass. The wildcard matches on any record for that column. It lets you use named queries, should allow you to keep weird conversions down to a minimum, doesn’t use conditional WHERE clauses and other values should correctly match. The most annoying part will be converting ‘All’ → ‘%’, but you may be able to handle that in the dropdown name → value mapping anyway.