Fork me on GitHub
#honeysql
<
2021-06-16
>
orestis09:06:41

Is there a way to order by NULLS FIRST/LAST in honeysql 2?

orestis09:06:12

Oh I see, just pass in a keyword: :desc-nulls-last which sql-kw would convert to DESC NULLS LAST

orestis12:06:17

I see that honeysql v1 qualify was removed. It was quite handy for us, I've come up with a quick replacement:

(defn- qualify
  "Replace honeysql v1 qualify function, that takes a table and column
  and joins them together."
  [table column]
  (keyword (hsql/format-entity (keyword (name table) (name column)))))
I was just wondering if there was something wrong with the original? I could copy that code.

6
jaihindhreddy14:06:39

Encouraging pervasive use of namespaced keywords is the reason perhaps?

seancorfield14:06:48

What purpose do qualified names serve in SQL?

orestis17:06:16

So we use a lot of WITH statements that we later join to. Sometimes more than once with different table names. So the table name is dynamic. If you want to refer to a specific column you need to write it as table.column which is what the qualify function would give us

orestis17:06:56

Perhaps there’s a more direct way to do this?

seancorfield17:06:37

So you’re round-tripping through qualified keywords for that? I guess I’m having a hard time imagining what the code looks like for this to be needed… Can you show a (simplified) example?

orestis18:06:35

I’m on the phone but imagine that I have a base query map that I keep adding where clauses to (users adding filters etc) Some of the where clauses need additional with and left-join clauses. The with, left-join and where clauses must share the same table name. The table name is dynamic as a filter might be repeated. So all the column references need to be qualified at runtime with the dynamic table name.

orestis18:06:46

Note, I don’t care about namespaced Clojure keywords at all. All I want is a honeysql function that given a table and a column will construct the correct keyword (to be later escaped properly) for an “absolute” column reference.

orestis18:06:31

My sample code took advantage that format-entity uses namespaced keywords to generate the escaped column reference.

seancorfield19:06:16

:some_alias.some_col when you have [:table :some_alias] to alias table to some_alias?

seancorfield19:06:37

And :some_alias is the dynamic part here (not really the table name)?

seancorfield19:06:29

So I think you just want (keyword (name the-alias) (name column)) and then HoneySQL will take care of formatting that? (you don’t need to call format-entity and convert back to a keyword) What am I missing?

orestis07:06:13

Right, so there is a convention that I’m missing. In all of our hand written columns we use dot as a separator - alias.some_column - but you’re saying that namespaced keywords also would be equivalent; alias/some_column as far as honeysql is concerned?

seancorfield16:06:52

In v1, you needed to specify :namespace-as-table? true as an option — that has become the default in v2:

dev=> (sql/format {:select [:foo/bar] :from [:foo] :where [[:= :foo/id 42]]})
["SELECT foo.bar FROM foo WHERE (foo.id = ?)" 42]
dev=>