# Named query with many conditional WHERE clauses without query string?

**URL:** <https://forum.inductiveautomation.com/t/named-query-with-many-conditional-where-clauses-without-query-string/47880>\
**Category:** Ignition\
**Tags:** ignition80\
**Created:** [June 17, 2021, 12:18pm UTC](https://forum.inductiveautomation.com/t/named-query-with-many-conditional-where-clauses-without-query-string/47880 "2021-06-17T12:18:23Z")\
**Posts on this page:** 4\
**Page:** 2

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [June 17, 2021, 3:10pm UTC](https://forum.inductiveautomation.com/t/named-query-with-many-conditional-where-clauses-without-query-string/47880/21 "2021-06-17T15:10:55Z")

</div>

Good to know thanks @paul-griffith . Time to go read up on `import threading` now.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 17, 2021, 5:22pm UTC](https://forum.inductiveautomation.com/t/named-query-with-many-conditional-where-clauses-without-query-string/47880/22 "2021-06-17T17:22:20Z")

</div>

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](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.

---

<div class="post-metadata">

**Author:** ![kcollins](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kcollins/32/19646_2.png) [@kcollins](https://forum.inductiveautomation.com/u/kcollins)\
**Post date:** [June 17, 2021, 5:29pm UTC](https://forum.inductiveautomation.com/t/named-query-with-many-conditional-where-clauses-without-query-string/47880/23 "2021-06-17T17:29:43Z")

</div>

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:

```sql
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…

---

<div class="post-metadata">

**Author:** ![stephen.wratten](https://avatars.discourse-cdn.com/v4/letter/s/c2a13f/32.png) [@stephen.wratten](https://forum.inductiveautomation.com/u/stephen.wratten)\
**Post date:** [June 17, 2021, 6:38pm UTC](https://forum.inductiveautomation.com/t/named-query-with-many-conditional-where-clauses-without-query-string/47880/24 "2021-06-17T18:38:42Z")

</div>

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.

[Previous page](https://forum.inductiveautomation.com/t/named-query-with-many-conditional-where-clauses-without-query-string/47880.md?page=1)