sql 2019-05-02 | Slack Archive

Can I write a lateral join using honey sql? i.e.

select t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
    select * from t t_inner
    where t_inner.section_id = t_outer.section_id
    order by t_inner.name
    limit 2
) t_top on true
order by t_outer.section_id;

Chris O’Donnell02:05:53

I have not done it, but I expect you can define your own :lateral-join clause by extending format-clause. (See https://github.com/jkk/honeysql/blob/master/src/honeysql/format.cljc#L533 for an example.) You will also need to register a priority for it (see https://github.com/nilenso/honeysql-postgres/blob/master/src/honeysql_postgres/format.cljc#L6 for an example).

💯 4

kenny15:05:28

How do I register a priority? It doesn't look extensible.

kenny15:05:06

Oh, I see: (fmt/register-clause! :foobar 110)

kenny23:05:55

Any way to write SELECT DISTINCT ON (location)?

kenny23:05:24

This gets close:

(sql/format
  {:select    [:*]
   :modifiers [:distinct :on]})
=> ["SELECT DISTINCT ON * "]

kenny23:05:56

Ah, this might do it:

(sql/format
  {:select    [(sql/raw "(location)") :*]
   :modifiers [:distinct :on]})
=> ["SELECT DISTINCT ON (location), * "]

kenny23:05:17

Nope - the comma after the paren messes it up.

2019-05-02

Channels