search

stch-library / sql

A DSL in Clojure for SQL query, DML, and DDL. Supports a majority of MySQL's statements.
Eclipse Public License 1.0
41 stars 7 forks source link

Support UNION ALL #9

Closed syunta closed 4 years ago

syunta commented 4 years ago

I want to write union-all on stch.sql support.

Maybe we could write union-all as following. I think this is natural.

example:

(describe "union-all"
  (it "multiple selects"
    (should= ["(SELECT name, email FROM users) UNION ALL (SELECT name, email FROM deleted_users)"]
             (sql/format
               (union (modifiers :all)
                      (-> (select :name :email)
                          (from :users))
                      (-> (select :name :email)
                          (from :deleted-users)))))))

or 

(describe "union-all"
  (it "multiple selects"
    (should= ["(SELECT name, email FROM users) UNION ALL (SELECT name, email FROM deleted_users)"]
             (sql/format
               (union-all (-> (select :name :email)
                              (from :users))
                          (-> (select :name :email)
                              (from :deleted-users)))))))
iku000888 commented 4 years ago

@syunta

My inclination is to add a union-all formatter&helper over updating union to support modifiers. (unless there are other flavors of union that could be supported...)

https://github.com/stch-library/sql/blob/92828e08778f53dc2e000eb99148a8b0c42cca71/src/stch/sql/format.clj#L334

Reason being union's syntactic handling is different from the select from join where that threads nicely with -> so not sure if the concept of modifiers fit. Modifier implies there is an existing sql thing to be modified but in the first example it is behaving more like an option.

What do you think?

syunta commented 4 years ago

@iku000888

I agree with you👌 I was thinking of UNION [ALL DISTINCT], but distinct is supported by mysql only. (other RDMS's union is equivalent to union distinct). So I rejudged distinct need not be supported.

I will add union-all. May I start to resolve this issue?

iku000888 commented 4 years ago

Go for it :rocket: :tada: