bug in join ?

[swannodette]

(def photo-counts-by-user (-> photos (cql/aggregate [:count/*] [:user_id])))

(def users-and-photo-counts
     (cql/join users photo-counts-by-user
               (cql/where (= :users.id :photos.user_id))))

;; (cql/to-sql users-and-photo-counts)
;; gives
;; SELECT users.* FROM users JOIN (SELECT photos.user_id,count(*) FROM photos GROUP BY photos.user_id) AS photos_aggregation ON (users.id = photos_aggregation.user_id)
;; We lose the columns from photos_aggregation

I was trying to see if what is described here http://magicscalingsprinkles.wordpress.com/2010/01/28/why-i-wrote-arel/ can be accomplished w/ ClojureQL

[ninjudd]

Looks like the code below works, but it doesn't work if you don't alias :count/*

(let [photo-counts-by-user (-> (table :photos) (aggregate [[:count/* :as :cnt]] [:user_id]))]
  (-> (table :users)
      (join photo-counts-by-user
            (where (= :users.id :photos.user_id)))
      to-sql))

[ninjudd]

i added a test for the case i mentioned above: https://github.com/LauJensen/clojureql/commit/cf3a9300a3b454cfb44c80d4faa1c0d7ba0c95fd

i think aliasing count(*) is required to make it available outside the subselect. can you come up with a SQL query that can access it without aliasing?

[swannodette]

SELECT * FROM users JOIN (SELECT photos.user_id, count(*) FROM photos GROUP BY photos.user_id) AS photos_aggregation ON (users.id = photos_aggregation.user_id)

This MySQL query works for me. No need to alias count(*). The problem is that ClojureQL creates a query that starts like so:

SELECT users.* FROM users ...

[swannodette]

Here's my code in a gist, it's pretty easy to guess what the tables looks like: https://gist.github.com/706001

[LauJensen]

Since the query actually works on MySQL I'll assume that people who do an aggregation without aliasing really know what they're doing. Usually you'll always prefer the alias for future reference. In the usual case the compiler finds the first alias and adds that to the column specification, but I've added a backup plan, where if no alias is found the first column is used in its place:

https://github.com/LauJensen/clojureql/commit/71aab6e27a597b11775bf4b4aaa92cb8d189e19b