Conditional segments of queries

[apatrida]

Description

Minimal condition query segments would be useful, and can be based exclusively around named parameters or flag variables.

For example, two conditionals in this statement, one could just use the empty list without conditional although it makes a silly query, but the 2nd case requires the conditional.

selectPosts:
SELECT *
FROM posts
WHERE authorId NOT IN :muteUserList
	  AND category IN :categoryList
;

This is not intended as final syntax, but uses a tag plus Kotlin conditional that maybe helps code compilation to inject that code directly into the calling logic or as a lambda. Nesting might also be needed.

Of course you could write multiple queries, but large queries that have complex bases would be painful to maintain 4 or 5 copies for small variations.

You could use CTE's if you could nest queries and then further refine it, but that doesn't work in all cases such as those requiring a join and a where clause item, that have big impact on the primary query.

nesting queries maybe is another helpful flavor of this conditional or compound query structuring:

selectSomePosts:
SELECT *
FROM {{selectPosts}}
WHERE authorId NOT IN :muteUserList
 	  AND category IN :categoryList
;

[AlecKazakova]

I agree something like this would be super useful, and I've thought about syntax in the past for it.

For nested queries the recommendation is to use SQL views for it, since that allows you to reuse a projection/query across multiple queries. AFAIK all the SQL Dialects we support how non-materialized view support so using views wouldn't have a data storage impact.

For conditionals the only real requirement is that SQLDelight can still statically analyze your SQL, so all the permutations need to be deterministic. I could imagine something like this:

mutedUsers:
authorId NOT IN :muteUserList;

categoryList:
category IN :categoryList;

selectPosts:
SELECT *
FROM posts
WHERE {{mutedUsers OR categories}}
;

and then at runtime you can choose which conditional to pass

postQueries.selectPosts(
  postQueries.mutedUsers(muteUserList = listOf("someUsers"))
).executeAsList()

or...

postQueries.selectPosts(
  postQueries.categoryList(categoryList = listOf("someCategory"))
).executeAsList()

and you can opt in to conditionals by doing something like

ignore:
TRUE;

mutedUsers:
authorId NOT IN :muteUserList;

categoryList:
category IN :categoryList;

selectPosts:
SELECT *
FROM posts
WHERE {{mutedUsers OR ignore}} AND {{categoryList OR ignore}}
;

curious if that kind of thing would be helpful or can be made better.

[apatrida]

Yes, this syntax is along the lines, although sometimes there is not OR but rather just add, but I'm guessing that is the same as {{mutedUsers}} by itself.

[darky]

Is any workaround exists? How to use conditional SQL WHERE now?

[darky]

Try as workaround https://github.com/cashapp/sqldelight/issues/626#issuecomment-615098756 but not working via https://github.com/cashapp/sqldelight/issues/626#issuecomment-1213078500

[darky]

This works https://github.com/cashapp/sqldelight/issues/626#issuecomment-1213319111

[apatrida]

CASE is work around for some but not all issues (optional joins, subqueries, ...)