honeysql 2024-09-17

For some reason {:nest } is bringing empty subquery.

execute query: SELECT `a`.`some_value`, `b`.`some_id` FROM `table_a` `a` WHERE (`a`.`type` = ? AND `a`.`user_id` = ())
18:59:49.231 [main] WARN org.mariadb.jdbc.message.server.ErrorPacket -- Error: 1064-42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1
See an example I'm trying to write with HoneySQL:
(hsql/format
  {:select [:table_a.some_value :table_b.some_id]
   :from   [[:table_a :a]]
   :where  [:and
            [:= :a.type "SOME_TYPE"]
            [:= :a.user_id
             {:nest
              {:select [[{:case
                          [[:when [:in :some_status ["STATUS_A" "STATUS_B"]]
                            {:nest
                             {:select [:b.user_id]
                              :from   [[:table_b :b]
                                       [:join [:table_c :c] [:= :b.c_id :c.id]]]
                              :where  [:= :b.id :some_id]}}]
                           [:when [:in :some_status ["STATUS_C" "STATUS_D" "STATUS_E"]]
                            {:nest
                             {:select [:b.requester_id]
                              :from   [[:table_b :b]]
                              :where  [:= :b.id :some_id]}}]
                           [:else nil]]}
                         :user_id]]}}]]}
  {:quoting :mysql})
Am I missing some syntax? I used https://github.com/seancorfield/honeysql/blob/develop/doc/special-syntax.md#nest as reference. Thank you.

Which version of HoneySQL? With your code, I get

Execution error (ExceptionInfo) at honey.sql/format-dsl (sql.cljc:1607).
These SQL clauses are unknown or have nil values: :case(perhaps you need [:lift {:case ...}] here?)

[honeysql "1.0.461"]

That's v1, support for :nest has appeared only in v2.

Oooh! Hm... I failed to trust on lein ancient upgrade , sigh.

V1 and v2 have different group and artifact ID.

Ah thanks.

Giving a try here

Note that while the DSL is mostly the same between v1 and v2, there are differences, so it is not entirely a drop-in replacement. Which is why v1 and v2 use both different group/artifact and different nses -- so you can use both side-by-side as you migrate your code one query at a time.

And, in particular for your original example, case is special syntax in v2 (not a clause, like you're trying to use): https://cljdoc.org/d/com.github.seancorfield/honeysql/2.6.1161/doc/getting-started/sql-special-syntax-#case

(`case` is called out in the differences page, BTW)

It worked! Beautiful! Learnt. Thanks a lot everyone!

👍 1
👍🏻 1