2025-10-30 honeysql | Clojure Slack Archive

honeysql

Christoph 2025-10-30T07:37:53.035719Z

Hello everyone 👋 How do I translate this query into honeysql ?

SELECT name
  FROM dinosaurs
  ORDER BY random() --- please help with this line ---
  LIMIT 10;

specifically, the order by random() part ? I'm using honeysql 2.7.1350 and the database is PostgreSQL

(defn plz-halp-ty!
  "Get a random sample from the db"
     []
    {:select [:name]
     :from :dinosaurs
     :order-by [:raw "RANDOM()"] ;; i've tried a gazillion stuff and I'm stuck here
     :limit 10})

p-himik 2025-10-30T07:43:36.294269Z

(honey.sql/format {:select   [:name]
                   :from     :dinosaurs
                   :order-by [[[:random]]]
                   :limit    10})
=> ["SELECT name FROM dinosaurs ORDER BY RANDOM() ASC LIMIT ?" 10]

🙏 1

🎉 1

Christoph 2025-10-30T07:45:03.602219Z

thank you thank you thank you! 🕺 🙌 👏

Christoph 2025-10-30T07:49:07.989579Z

how did you know triple [[[ would do the trick ? @p-himik

p-himik 2025-10-30T07:50:37.623729Z

In general, the top level [] means a collection of things, the next level of [] means aliases (like for :select, :from, etc.) or extra parameters (like for :order-by, where you can specify :desc that way, for example), and the third level of [] means function calling.

p-himik 2025-10-30T07:50:52.391779Z

BTW :select [:name] can be written as :select :name.

Christoph 2025-10-30T07:51:33.410939Z

thank you very much, you've made my day bowtie

p-himik 2025-10-30T07:51:39.369839Z

No problem. :)

seancorfield 2025-10-30T14:02:38.932819Z

The docs have this example for ORDER BY but I can see how it wouldn't be obvious to extrapolate to the case you wanted:

user=> (sql/format '{select (*) from table
                     ;; explicit direction provided:
                     order-by ((status asc), ((year created-date) asc))})
["SELECT * FROM table ORDER BY status ASC, YEAR(created_date) ASC"]

seancorfield 2025-10-30T14:13:25.016149Z

I'm adding these two additional examples:

user=> (sql/format {:select [:*] :from :table
                    ;; explicit direction provided:
                    :order-by [[:status :asc] [[:year :created-date] :asc]]})
["SELECT * FROM table ORDER BY status ASC, YEAR(created_date) ASC"]
user=> (sql/format {:select [:*] :from :table
                    ;; order by single function:
                    :order-by [[[:random]]]})
["SELECT * FROM table ORDER BY RANDOM() ASC"]

p-himik 2025-10-30T19:22:49.680549Z

Just curious - why does :with require pairing of its arguments? Unlike e.g. :from or :join. There's also a somewhat relevant ancient issue describing the confusion: https://github.com/seancorfield/honeysql/issues/164

seancorfield 2025-10-30T19:51:28.806869Z

I suspect the logic was "Well, it's a bit like an alias -- WITH expr AS ident -- and SELECT uses pairs for aliases, and a sequence of pairs for multiple expressions/aliases, so WITH should "match" that, even tho' the "alias" comes before the expression...?

seancorfield 2025-10-30T19:52:49.724189Z

{select ((foo.bar old) (quux.bar new)) ...} and {with ((old foo.bar) (new quux.bar)) ...}

seancorfield 2025-10-30T19:53:23.789199Z

But I'd probably have to go back in time to when it was first added and see if there are any bread crumbs...

p-himik 2025-10-30T19:53:40.464789Z

Ah, I see. In my mind, :with is closer to Clojure's let, which can be contrasted with some other lisps here, because there are no extra () around each pair. :)

seancorfield 2025-10-30T19:54:36.943469Z

Yeah, there could be an argument for supporting just an alternating sequence of ident/expr and partitioning I guess...

p-himik 2025-10-30T19:58:42.463039Z

Eh, I wouldn't touch it, I think. It's documented. Way too many little things to tackle each and every one. :)

❤️ 1

Clojurians Log v2

honeysql