Question: Best way to rename columns?

[sthomp]

Just finished going through the documentation but didn't see any mention of renaming columns.

I can do something like this:

(hugsql/sqlvec "select :i*:cols from users"
               {:cols ["name AS my_name" "email AS my_email"]})

But Im in a scenario where Im joining to the users table multiple times and each time I'm selecting the same columns. But, I just need them named differently in the resultset (ie: user1_name, user1_email, user2_name, user2_email).

The only way I can think to do this is to have a clojure array of the columns: ["name" "email"] and a couple arrays for the renamed columns ["user1_name", "user1_email"] and ["user2_name", "user2_email"] then zip these together and map to insert the 'AS'.

Not sure if theres a better way to compose this?

Thanks!

[csummers]

This can be achieved a couple of ways with either Clojure Expressions or implementing your own HugSQL parameter type.

Here's the general idea with a Clojure Expression:
SQL

-- :name select-labeled-identifiers :? :*
/* :require [clojure.string :as string]
            [hugsql.parameters :refer [identifier-param-quote]] */
select
/*~
(string/join ", "
  (for [[field label] (:cols params)]
    (str (identifier-param-quote field options)
      (when label (str " as " label)))))
~*/
from test

Clojure:

(testing "labeled identifiers: col_name as label_name"
    (is (= ["select  a as lbl_a, b as lbl_b, c from test"]
           (select-labeled-identifiers-sqlvec
            {:cols [["a" "lbl_a"]
                    ["b" "lbl_b"]
                    ["c"]]}))))

If you're going to use this kind of thing very often, then implementing a custom HugSQL parameter type is probably worth it.

I'm might consider adding a couple of built-in parameter types to support this pattern (:aliased-identifier and :aliased-identifier-list). Let me think on that.

[csummers]

Here's a different way to do it. This expression does not require the use of identifier-param-quote, but instead rewrites the parameters to use identifier param types referenced by the index in the passed-in vector using HugSQL's Deep Get Param Name feature. And, I put it in a snippet named cols for reuse:

-- :snip cols
/* :require [clojure.string :as string] */
/*~
(string/join ", "
  (map-indexed
    (fn [i [field label]]
      (str ":i:cols." i ".0" 
        (when label (str " as :i:cols." i ".1"))))
    (:cols params)))
~*/

-- :name select-labeled-identifiers :? :*
select :snip:cols from test

Only issue here is that calling the snippet is a little verbose with the cols

(testing "labeled identifiers: col_name as label_name"
    (is (= ["select a as lbl_a, b as lbl_b, c from test"]
           (select-labeled-identifiers-sqlvec
            {:cols (cols {:cols [["a" "lbl_a"]
                                 ["b" "lbl_b"]
                                 ["c"]]})}))))

[csummers]

So, I decided the best way to tackle this was to just extend the existing identifier parameter types to support aliases. This will be in the next release allowing you to pass along vector pairs:

 (is (= ["select id as my_id, name as my_name from test"]
           (identifier-param-list-sqlvec {:columns [["id" "my_id"], ["name" "my_name"]]})))
    (is (= ["select * from test as my_test"]
           (identifier-param-sqlvec {:table-name {"test" "my_test"}})))

[sthomp]

Thanks for all your help. Really enjoying hugsql so far!

[csummers]

This is released in 0.4.6. Give it a try!

[sthomp]

Just curious, is there a reason why :table-name uses map notation whereas :columns uses array notation?

[csummers]

Well, you can actually use either, but I copied examples from the test suite here that didn't match. The docs use vectors in both cases and that is the preferred usage: http://www.hugsql.org/#param-identifier

[csummers]

That reminds me...the test suite should not assume column order when using a map.