I'm trying to figure out how to create a Sequel Dataset that contains function calls and constants as part of its select
and uses aliases. I'm stuck on the proper syntax.
I'm trying to avoid inserting raw SQL but I will if I have to as long as I can chain the resulting dataset and refer to column aliases inside the raw SQL.
My actual query is more complicated (and I'm not actually calling sin
, it's just an example of a function call), but for illustrative purposes, here's a stripped down version of what I'm trying to do in SQL:
SELECT
0 AS a, -- constant
sin(t.x) AS b, -- function call with aliased column value argument
t.x AS c
FROM
T AS t
;
What would the equivalent, idomatic, Sequel be? So far I have:
DB[:T.as(:t)].
select(
#0 as a, # How? constant
#sin(t.x) as b, # How? function call with aliased column value argument
:t__x.as(:c)
)
If you want a more concise way, you can use implicit aliasing using symbols with a triple underscore, use just the select method with a virtual row block:
DB[:T___t].select('0'.lit.as(:a), :t__x___c){sin(t__x).as(b)}
This isn't exactly the same as the order of the selected columns differs, but that usually doesn't matter.