Select distinct on is broken about honeysql

[lambdahands]

I'm running into this issue as well. I thought I might be able to solve this by using the :modifiers key, but the current behavior simply uppercases the keys within the value, which is embedded in the format-clause implementation of :select. I chose to create a new implementation :selectd with added behavior to support DISTINCT ON (...):

(require '[honeysql.format :as f])
(require '[clojure.string :as string])

(defmethod f/format-clause :selectd [[_ fields] sql-map]
  (str "SELECT "
       (when (:distinct-on sql-map)
         (str "DISTINCT ON ("
              (f/comma-join (map name (:distinct-on sql-map)))
              ") "))
       (f/comma-join (map f/to-sql fields))))

(f/register-clause! :selectd 50)

It's used like so:

{:selectd     [:foo]
 :from        [:bar]
 :distinct-on [:baz]

This workaround has its limits as we lose the composability of HoneySQL with :selectd (the helper methods like merge-select no longer work here).

I think there are two ways to solve this:

1. The :select clause could be augmented to handle a :distinct-on top-level clause, which I believe would violate the general design decision of the library to be database agnostic (DISTINCT ON is specific to Postgres).
2. Open up the :modifiers to multi-methods, and allow users to extend the space between SELECT and its clauses as needed.

The second option seems intuitive to me. Something like this might work:

{:select    [:foo]
 :from      [:bar]
 :modifiers [[:distinct-on :baz]]}

[seancorfield]

It seems like this should be pushed to honeysql-postgres?

[piranha]

I think so, it's just unclear if it's possible to make it work, because in our codebase we're doing this: {:select [(sql/raw "distinct on (id) id") :other_field]}.

[seancorfield]

Given HoneySQL's position of trying to be vendor-neutral and the presence of reasonable approaches to make this work (without changing HoneySQL), I'm closing this out.