Clojurians Log v2
sql
I'm fiddling around with HoneySQL and I'm trying to create a query generator that would return me a query that would return me a list where I decide on the order of items.
I found this thread and in PostgreSQL it's very inelegant to use a specific order of the items: https://stackoverflow.com/a/6822901
It also means that I would have to use query input variables during query built time which I wanted to avoid.
I'm allowed to do this by HoneySQL by default because I could simply put in the IDs into the :in expression but... oh my.
Is there a better way to do this? I really wanted to make my query creation and variable insertion functions separate and at format time and not at HoneySQL DSL build time.
The pain comes from when I want to use IN in the query but I also want the sorter to be WHEN ID=5 -> 1 and so on. This forces me to generate the HoneySQL expression from the inputs and not in advance. And it means that I can't reuse the query for different inputs.
You can pass in a json array of objects [ { key, sortVal } ], join to that json table on the key, then use the sortVal in the order by.
Not sure how to do it in honeySQL.
I did it using an outer select statement which only had order-by. But now I have problems where I want to return nulls in my query and I think I can't do that with order-by because it only orders the items. So I think I'll have to do something in my back-end code. 🤔