search

korma / Korma

Tasty SQL for Clojure.
http://sqlkorma.com
1.48k stars 222 forks source link

Support also other subqueries than just subselect. #320

Closed juhovh closed 8 years ago

juhovh commented 8 years ago

Subselect works quite fine, but I need to be able to select from union results. I haven't written tests for the patch yet, because I want to make sure that it's ok first, but to me it feels pretty straightforward.

immoh commented 8 years ago

Why subselect wouldn't work here?

(sql-only (select (subselect (union (queries (subselect :users1 (where {:id [<= 10]}))
                                             (subselect :users2 (where {:id [> 10]})))))
                  (fields :id)))
=> "SELECT \"id\" FROM ((SELECT \"users1\".* FROM \"users1\" WHERE (\"users1\".\"id\" <= ?)) UNION (SELECT \"users2\".* FROM \"users2\" WHERE (\"users2\".\"id\" > ?)))"
juhovh commented 8 years ago

Sorry, forgot to reply to this.

You're probably right that subselect works here, wasn't aware of it and it's not quite documented. But I find it extremely confusing and somewhat semantically incorrect to have 4 selects in Clojure query resulting in just 3 selects in the final SQL. Feel free to disagree, but it makes me feel really dirty. :)

Nevertheless, I'll keep my macro in my own code to result in more readable queries, "select subselect union queries subselect" is a bit too much of select to me.

immoh commented 8 years ago

I agree that it is awkward and dirty, but I just don't think "select subquery union queries subselect" is that much better.

Ultimately it boils down to the fact that select, union etc. do two things: create they query and execute it against db. This makes composition awkward and makes creating more complicated queries difficult.

juhovh commented 8 years ago

Closing this, since subselect works.