Re: Column aliases

Hello,

There is not an easy syntax way of creating aliases (yet),
though good / reasonable patches for this would be accepted.  You will
also have better luck if you include what backend(s) you are having
issues with.

  * [response_date date] will create the column name response_date.date

  * You can check the output of a sql expression by calling
(clsql-sys:sql [response_date date] )

  * You can always ensure that your exact desired output is emitted by
using the sql-expression :string function. This is probably the easiest 
path.
(clsql-sys:sql-expression :string "response_date as date")

  * You *could* use a sql-ident-table directly though this is the wrong
type and I cannot guarantee it would work in all forms, and it is not
supported by the syntax either. This seems unideal, but might solve your
issue

eg>(clsql-sys:sql
      (clsql:sql-expression :table "foo.response_date" :alias "date" ))
=> "\"foo.response_date\" \"date\""

  * You could use clsql:query instead and enter the whole query as a
string

  * This form seems like it should work, but doesnt (because it creates an
sql-ident-attribute which doesn't support an alias)

 >(clsql-sys:sql
    (clsql:sql-expression
     :table "foo" :attribute "response_date"
     :alias "date"))

  * You could try implementing the AS sql operator (see code below). This
will probably work in many situations, but may require debugging
random parts of clsql (eg: group-by, order-by clauses) verifying that
it works for both tables an attributes in all of the various places
that they are used.  I have included code below to get you started,
but do not have time to debug and write tests for this right now.


Cheers,
Russ Tyndall
Acceleration.net

----

;; these would go in expressions.lisp
(defclass sql-as-exp (%sql-expression)
   ((name :accessor name :initarg :name :initform nil)
    (alias :accessor alias :initarg :alias :initform nil))
   (:documentation "An SQL between expression."))

(defmethod output-sql ((expr sql-as-exp) database)
   (with-slots (name alias)
       expr
     (output-sql name database)
     (write-string " AS " *sql-stream*)
     (output-sql alias database))
   t)


;; this would go in sql/operations.lisp
(defsql sql-as (:symbol "as") (name alias)
   (make-instance 'sql-as-exp :name name :alias alias))

(clsql-sys:sql [as [date-removed] [date]]) => "DATE_REMOVED AS DATE"
(clsql-sys:sql [as [dates.entered] [date]]) => "DATES.ENTERED AS DATE"


On 4/29/2012 10:11 AM, [email protected] wrote:
> I've not had much luck finding a way to alias column names.
>
> (clsql:select
> 		  [response_date] [comment] [val]
> 		  :from *comments-view*
> 		  :where [and [= [survey_id] tool] [null [question_id]]])
>
> I've tried
>
> [response_date date]
>
> but I see no difference in the column names or any error.
>
>
> How can I do this?
>
>
> _______________________________________________
> CLSQL mailing list
> [email protected]
> http://lists.b9.com/cgi-bin/mailman/listinfo/clsql