SQL optional where clauses without exponentially large number of only slightly different queries

Question

I'm working with postgres in python and I want to write a clear query that optionally allows WHERE clause subsetting, but doesn't require me writing an exponential explosion of whether the subsetting where clauses exist or not. Specifically, I have the following:

query = """
select *  -- in reality, something more complicated
from myTable t
WHERE 
  t.first_id in :args1 AND
  t.second_id in :args2
"""

I would like to duplicate as little as possible, and for this query to return sensibly:

1. When args1 is null, and args2 is null

2. When args1 is null, and args2 has 1 argument

3. When args1 is null, and args2 has n arguments

4. When args1 has 1 argument, and args2 is null

5. When args1 has 1 argument, and args2 has 1 argument

6. When args1 has 1 argument, and args2 has n arguments

7. When args1 has n arguments, and args2 is null

8. When args1 has n arguments, and args2 has 1 argument

9. When args1 has n arguments, and args2 has n arguments

Where args1 can be:

args1 = []
args1 = ["a"]
args1 = ["a", "b"] 

...for example, and similar for args2.

As you can see above,

   (1) is effectively no WHERE clause
   (2) is effectively remove the first argument and the AND
   (2) Would also likely want an equality operator = 'a', rather than an in ('a')

...etc.

For 2 clauses and 3 options, we have 3^2 = 9 different queries to write. If we add another 1-2 where clauses, this quickly becomes overwhelming and duplicates a lot of code for what is effectively the same problem. While I can write each of these queries out and switch case on them, I suspect this is a syntax SQL can do for me, and that has possibly been answered a thousand times on stackoverflow but I can't seem to find.

I'm using sqlalchemy in python if that changes anything.

Answer 1

Given that the phrase column IN () is not valid SQL syntax, I do not believe there is a way to do this with SQL. If there is any SQL syntax to do this, it is going to be exceptionally convoluted, likely lead to sub-optimal query plans, and bloat your queries by requiring you to specify the same argument list multiple times -- once to check the argument list in SQL, and once to actually use it.

In Postgres, the database should not treat a single-item IN clause differently than an equality clause. Rewriting a single-item IN clause as an equality clause gains you nothing.

Since you are using SQLAlchemy already, you can conditionally add these conditions to your query based on what is in the argument lists:

# your values need to be tuples for Postgres to properly
# convert them to lists for IN clauses; Python lists will convert
# to Postgres ARRAY types
args1 = ("value",)
args2 = ()

# the "1=1" is an always-true check so that you can
# specify a WHERE clause, even with no provided arguments
clauses = ["1=1"]
if len(args1) > 0:
    clauses.append("t.first_id IN :args1")
if len(args2) > 0:
    clauses.append("t.second_id IN :args2")
...

query = """
SELECT *  -- in reality, something more complicated
FROM myTable t
WHERE
""" + " AND ".join(clauses)

result = conn.execute(text(query), args1=args1, args2=args2, ...).fetchall()

SQLAlchemy does not care if you provide unused parameters. In other words, if args2 is empty, passing it to execute() will work here, even though the query will not have a placeholder for it.

If you are using the SQLAlchemy Core API for generating queries, you can chain your where() clauses in a similar way:

s = select([myTable])
if len(args1) > 0:
    s = s.where(myTable.c.first_id.in_(args1))
if len(args2) > 0:
    s = s.where(myTable.c.second_id.in_(args2))
...
result = conn.execute(s).fetchall()