CODEWITHSUNDEEP
clojurekormasqlkorma
Freid001
Freid001

Reputation: 2850

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;

Upvotes: 1

Views: 275

Answers (1)

Freid001
Freid001

Reputation: 2850

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;

Upvotes: 1

Related Questions