clojurekormasqlkorma

Korma - join on a sub select but how do I specify an alias?


I would like to assign an alias to the subselect in this query:

(->
  (korma/select* table_a)
  (korma/join
   (korma/subselect table_b
                    (korma/fields :id (korma/raw "COUNT(*) AS count"))
                    (korma/group :id))
   (= :id :id)))

This is the sql I am looking to create:

SELECT * FROM table_a LEFT JOIN (SELECT id, COUNT(*) AS count FROM table_b GROUP BY id) AS b b.id = id;

But at the moment sql korma just generates:

SELECT * FROM table_a LEFT JOIN (SELECT id, COUNT(*) AS count FROM table_b GROUP BY id) id = id;

Solution

  • I needed to define the sub-select as a separate entity, see below:

    (korma/defentity sub-select
                 (korma/table (korma/subselect table_b
                                               (korma/fields :id(korma/raw "COUNT(*) AS count"))
                                               (korma/group :id)) :b))
    
    (->
      (korma/select* table_a)
      (korma/join sub-select (= :b.id :id)))
    

    Which generates the following query:

    SELECT * FROM table_a LEFT JOIN (SELECT id, COUNT(*) AS count FROM table_b GROUP BY id) a.id = id;