support DISTINCT-ON

[WorldsEndless]

There is a need to support DISTINCT ON() syntax, which is problematic with vanilla Honey. See full details here: seancorfield/honeysql#112

[b-ryan]

I needed this today too. There's no way to properly use the DISTINCT ON syntax when you are quoting column names. I came up with the following today:

(defn- format-distinct-on
  [fields]
  (str "DISTINCT ON(" (h-fmt/comma-join (map h-fmt/to-sql fields)) ") "))

;; This overrides the default :select clause to add support for :distinct-on.
;; Since this is necessarily always evaluated AFTER the default :select has
;; been evaluated, I don't think there's any way for the default honeysql code
;; to be used.
(defmethod h-fmt/format-clause :select
  [[_ fields] sql-map]
  (str "SELECT "
       (when (:distinct-on sql-map) (format-distinct-on (:distinct-on sql-map)))
       (when (:modifiers sql-map)
         (str (h-fmt/space-join (map name (:modifiers sql-map))) " "))
       (h-fmt/comma-join (map h-fmt/to-sql fields))))

(honey/defhelper distinct-on [m args] (assoc m :distinct-on args))

As can be seen, however, it relies on overriding the default honeysql :select clause. I don't know whether this is a viable way to do this.

[ukriish]

@b-ryan Yea, one of the limitations of building honeysql-postgres as an extension of honeysql was that we would have to override the default clause. Over the years, some of these have been accomplished by using different names but it has its own limitations. Like implementing on conflict using upsert.

It might actually be more readable to implement this as an override as you mention.

[ukriish]

I've not been getting a lot of time recently to work on honeysql-postgres, it would be great if you can raise a PR for this.

[mping]

I think modifiers has support for distinct, and ideally distinct on would be supported there.
I'm using this:

(select :*)
      (from :candidates)
      (modifiers "distinct on" "(day,category)") ;;close enough
      (order-by [:day :asc])
      sql/format))

[b-ryan]

modifiers doesn't quite work, unfortunately: seancorfield/honeysql#241

[WorldsEndless]

The cleanest way I found was this, which doesn't override anything:

{:select [(sql/raw "DISTINCT ON (applications.id) applications.id") ;; and loads of other select stuff for the query

[b-ryan]

@WorldsEndless that's not bad in terms of just getting something to work. Insofar as adding support goes, I can't see a way to build that into the extension points honeysql provides.

In terms of adding support, from looking into honeysql's code the only option appears to be override the default select query. Or providing a new select query that's named something different, but I don't really see the point in that, since you still have to duplicate the default select behavior for it to work as users expect.

Maybe @seancorfield could shed light on whether some other extension point may be provided. Maybe another angle of attack I could have taken in seancorfield/honeysql#241 would be to ask whether a new extension point could be added or some other suggestion for doing this in a cleaner way.

[b-ryan]

I could see a nice syntax for select queries being that the first item in the fields could be a map which would then be rendered using the normal clause rendering code. An example SELECT DISTINCT:

(select {:distinct true} :a :b) => "SELECT DISTINCT A, B"

And then distinct on:

(select (distinct-on :x) :a :b) => "SELECT DISTINCT ON A, B"

In fact I like that so much that I may ask in honeysql if that would fly.

[seancorfield]

Looking over this -- and the source code -- and I think the root problem is that the select modifiers aren't extensible, and I think this is true of several other pieces in honeysql that have been problematic to extend?

seancorfield/honeysql#242

[seancorfield]

Try the latest master in Git -- honeysql.format/format-modifiers is a multimethod you should be able to extend for :distinct-on to do whatever you want now.

[b-ryan]

@seancorfield Thanks! This works like a charm: #33

[mping]

@b-ryan yeah it doesn't work but you can hack it to get close enough. The example I posted actually generates valid sql, although its a bit of a hack.