gistfile1.txt

;;
;; Queries
;;

(def users (table db :users))

(to-sql users)
;;-> ["SELECT `users`.* FROM `users`"]

(to-sql (select users (where (< :id 3)))))
;;-> ["SELECT `users`.* FROM `users` WHERE (`id` < ?)" 3]

(to-sql (-> (select users (where (!= :id 3)))))
;;-> ["SELECT `users`.* FROM `users` WHERE (`id` != ?)" 3]

(to-sql (select users (where (and (= :id 1) (= :title "Dev")))))
;;-> ["SELECT `users`.* FROM `users` WHERE ((`id` = ?) AND (`title` = ?))" 1 "Dev"]

(to-sql (select users (where (or (= :id 1) (= :title "Design Guru")))))
;;-> ["SELECT `users`.* FROM `users` WHERE ((`id` = ?) OR (`title` = ?))" 1 "Design Guru"]


;;
;; Aliasing
;;

(-> (select users (where (< :id 3)))
    (project [:title])
    to-sql)
;;-> ["SELECT `users`.`title` FROM `users` WHERE (`id` < ?)" 3]

(-> (table db :salary)
    (project [[:id :as :userid]])     ; Nested vector means aliasing
    (select (where (= :userid 5)))
    to-sql)
;;-> ["SELECT `salary`.`id` AS `userid` FROM `salary` WHERE (`userid` = ?)" 5]


;;
;; Aggregates
;;

(to-sql (-> (table db :salary)
            (aggregate [:avg/wage])))
;;-> ["SELECT avg(`salary`.`wage`) FROM `salary`"]

(to-sql (-> (table db :salary)
            (aggregate [[:avg/wage :as :average]])))
;;-> ["SELECT avg(`salary`.`wage`) AS average FROM `salary`"]

(to-sql (-> (table :mysql :test-hyphen)
            (aggregate [:avg/wage-stuff])))
;;-> ["SELECT avg(`test-hyphen`.`wage-stuff`) FROM `test-hyphen`"]

(to-sql (-> (table db :salary)
            (aggregate [:avg/wage:expenses])))
;;-> ["SELECT avg(`salary`.`wage`,`salary`.`expenses`) FROM `salary`"]

(-> (table {} :users)
     (select (where (= :admin true)))
     (aggregate [:count/*])
     to-sql)
;;-> ["SELECT count(*) FROM `users` WHERE (`admin` = ?)" true]

(-> (table {} :users)
     (select (where (= :admin true)))
     (aggregate [:count/*] [:country])
     to-sql)
;;-> ["SELECT `users`.`country`,count(*) FROM `users` WHERE (`admin` = ?) GROUP BY `users`.`country`" true]