在Ruby中结合Arel.sql与Arel::Nodes::Case实现动态子查询

需求说明：需要从locations表中查询每行记录对应本地时区的时间戳，时区信息存储在tz_name列中；部分记录的tz_name可能为null，此时需要使用默认时区America/New_York兜底。

现有可正常运行的查询代码 #

以下是当前能正常执行的查询，但仅固定使用America/New_York时区：

locations = Location.select(
  Location.arel_table[:id],
  Arel::Nodes::Case.new
    .when(Location.arel_table[:tz_name].eq(nil))
    .then('America/New_York')
    .else(Location.arel_table[:tz_name]).as("zone"),
  Arel::Nodes::NamedFunction.new(
    'date_part',
    [Arel::Nodes.build_quoted('hour'),
     Arel.sql(
       "(select CURRENT_TIMESTAMP AT TIME ZONE
                'America/New_York')"
     )
    ]
  ).as("now_hour")
).where(
  Arel::Nodes::NamedFunction.new(
    'date_part',
    [Arel::Nodes.build_quoted('hour'),
     Arel.sql(
       "(select CURRENT_TIMESTAMP AT TIME ZONE
                'America/New_York')"
     )
    ]
  ).eq(9)
)

尝试过但失败的写法 #

写法1：在Arel.sql中拼接Case语句 #

直接拼接Arel的Case节点到SQL字符串中会报错：

Arel.sql(
  "(select CURRENT_TIMESTAMP AT TIME ZONE " +
    Arel::Nodes::Case.new
      .when(Location.arel_table[:tz_name].eq(nil))
      .then('America/New_York')
      .else(Location.arel_table[:tz_name])
    + ")"
) # 执行失败

写法2：将Arel.sql放入Case语句的分支中 #

then分支可正常运行，但else分支引用表列时失败：

Arel::Nodes::Case.new           
  .when(Location.arel_table[:tz_name].eq(nil))                                  
  .then(Arel.sql("(select CURRENT_TIMESTAMP AT TIME ZONE 'America/New_York')"))     # 正常运行
  .else(Arel.sql("(select CURRENT_TIMESTAMP AT TIME ZONE '#{Location.arel_table[:tz_name]}')"))  # 执行失败

解决方案 #

利用PostgreSQL原生的TIMEZONE函数，结合COALESCE函数处理tz_name为null的情况，同时用Arel构建查询：

Arel::Nodes::NamedFunction.new(
  'date_part',
  [Arel::Nodes.build_quoted('hour'),
   Arel.sql("(TIMEZONE(COALESCE(locations.tz_name, 'America/New_York'), CURRENT_TIMESTAMP))")
  ]
).as("now_hour")

说明：

• COALESCE(locations.tz_name, 'America/New_York')会优先使用tz_name字段的值，若该值为null则返回默认时区America/New_York
• PostgreSQL的TIMEZONE(timezone, timestamp)函数可直接将时间戳转换为指定时区的时间，相比CURRENT_TIMESTAMP AT TIME ZONE的写法，更适配动态传入表列值的场景

内容的提问来源于stack exchange，提问作者dreadstar