如何在PostgreSQL中为Rails的ServiceAgreement模型创建生成列？

一、迁移文件写法（分数据库实现） #

PostgreSQL 版本 #

PostgreSQL支持数组类型和generate_series函数，可直接生成日期数组虚拟列：

class AddSuggestedServiceDatesToServiceAgreements < ActiveRecord::Migration[7.0]
  def change
    add_column :service_agreements, :suggested_service_dates, :datetime, array: true, virtual: true, as: <<~SQL
      (
        SELECT ARRAY(
          SELECT generate_series(
            -- 从起始日期后第一个间隔开始生成
            starts_at + CASE service_interval
              WHEN 0 THEN '1 week'::interval
              WHEN 1 THEN '1 month'::interval
              WHEN 2 THEN '6 months'::interval
              WHEN 3 THEN '1 year'::interval
            END,
            ends_at,
            CASE service_interval
              WHEN 0 THEN '1 week'::interval
              WHEN 1 THEN '1 month'::interval
              WHEN 2 THEN '6 months'::interval
              WHEN 3 THEN '1 year'::interval
            END
          )
        )
      )
    SQL
  end
end

MySQL 版本 #

MySQL无原生序列生成函数，需用递归CTE生成日期序列后转为JSON数组：

class AddSuggestedServiceDatesToServiceAgreements < ActiveRecord::Migration[7.0]
  def change
    add_column :service_agreements, :suggested_service_dates, :json, virtual: true, as: <<~SQL
      (
        WITH RECURSIVE service_dates AS (
          -- 初始值：起始日期后第一个间隔的日期
          SELECT 
            CASE service_interval
              WHEN 0 THEN DATE_ADD(starts_at, INTERVAL 1 WEEK)
              WHEN 1 THEN DATE_ADD(starts_at, INTERVAL 1 MONTH)
              WHEN 2 THEN DATE_ADD(starts_at, INTERVAL 6 MONTH)
              WHEN 3 THEN DATE_ADD(starts_at, INTERVAL 1 YEAR)
            END AS date,
            service_interval
          FROM service_agreements sa
          WHERE id = sa.id
          UNION ALL
          -- 递归生成后续日期
          SELECT
            CASE service_interval
              WHEN 0 THEN DATE_ADD(sd.date, INTERVAL 1 WEEK)
              WHEN 1 THEN DATE_ADD(sd.date, INTERVAL 1 MONTH)
              WHEN 2 THEN DATE_ADD(sd.date, INTERVAL 6 MONTH)
              WHEN 3 THEN DATE_ADD(sd.date, INTERVAL 1 YEAR)
            END AS date,
            sd.service_interval
          FROM service_dates sd
          WHERE sd.date <= sa.ends_at
        )
        -- 把日期序列转为JSON数组
        SELECT JSON_ARRAYAGG(date) FROM service_dates sd WHERE sd.date <= sa.ends_at
      )
    SQL
  end
end

二、SQL逻辑说明 #

• 两种实现均遵循需求：从starts_at之后的第一个间隔日期开始，按service_interval定义的周期生成日期，直到不超过ends_at
• 若需包含starts_at本身，只需将序列起始值改为starts_at（比如PostgreSQL去掉+ CASE...部分，MySQL递归CTE初始值设为starts_at）

三、备选方案（Ruby层面实现） #

如果数据库不支持虚拟列（如SQLite），可直接在模型中定义方法，用Ruby生成日期数组：

class ServiceAgreement < ApplicationRecord
  enum service_interval: { weekly: 0, every_month: 1, biannually: 2, annually: 3 }

  def suggested_service_dates
    interval = case service_interval
               when 'weekly' then 1.week
               when 'every_month' then 1.month
               when 'biannually' then 6.months
               when 'annually' then 1.year
               end
    # 从起始日期后第一个间隔开始，按步长生成直到结束日期
    (starts_at + interval..ends_at).step(interval).to_a
  end
end

注意事项 #

• 虚拟列由数据库实时计算，不存储在表中，大表查询时需注意性能
• 不同数据库的虚拟列语法、数组/JSON支持存在差异，需根据生产数据库选择对应实现

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