sql - 如何仅使用 arel 解决此 sql 语句

Question

经过多次摆弄，一位同事编写了以下 SQL 语句:

SELECT id, max(updated_at) FROM (
  SELECT (activities.id) as id, (notes.updated_at) as updated_at FROM `activities`
    INNER JOIN `notes` ON `notes`.`activity_id` = `activities`.`id`
    WHERE (context_id = 8) AND (notes.updated_at > '2011-12-01 18:56:16')
  UNION ALL (
    SELECT (activities.id) as id, (transitions.updated_at) as updated_at
    FROM `activities`
      INNER JOIN `transitions` ON (
        `transitions`.`transitionable_id` = `activities`.`id` 
         AND `transitions`.`transitionable_type` = 'Activity'
      )
      WHERE (context_id = 8) AND (transitions.updated_at > '2011-12-01 18:56:16')
  )
  ) transitions
  GROUP BY id ORDER BY updated_at DESC

现在我们先不谈细节或该声明的有效性 - 他问我是否可以为其制作 AR 示波器。

我想出了以下解决方案:

scope :most_recent_since, lambda { |way_back|
  activity_t   = Activity.arel_table
  transition_t = Transition.arel_table
  note_t       = Note.arel_table

  note_activities = activity_t.join(note_t).on(
    note_t[:activity_id].eq(activity_t[:id])
  ).where(
     activity_t[:context_id].eq(8).and(note_t[:updated_at].gt(way_back))
  )  

  transition_activities = activity_t.join(transition_t).on(
    transition_t[:transitionable_id].eq(activity_t[:id]).and(
      transition_t[:transitionable_type].eq('Activity')
    )
  ).where(
    activity_t[:context_id].eq(8).and(
      transition_t[:updated_at].gt(way_back)
    )  
  )  

  union = note_activities.project(
    activity_t[:id], note_t[:updated_at]
  ).union(
    transition_activities.project(activity_t[:id], transition_t[:updated_at])
  )  

  sql = transition_t.project(
    transition_t[:id]
  ).group(
    transition_t[:id]
  ).order(
    'updated_at'
  ).to_sql

  # now fiddle in the union sql - cant seem to find how to do it with Arel
  sql.gsub!("FROM `trans", "FROM #{union.to_sql} `trans")

  where( :id => Transition.find_by_sql(sql) )
}  

不，我的问题是；我怎么才能让 gsub 消失并让 ARel 从多个来源(包括 SQL/联合)中选择

Answer 1

这个 Arel 解决方案来自惊人的 scuttle.io :

transitions = Arel::Table.new('transitions')
Activity.select(:id, transitions[:updated_at].maximum).from(
  Activity.select(
    Arel::Nodes::Group.new(Activity.arel_table[:id]).as('id'), Arel::Nodes::Group.new(Transition.arel_table[:updated_at]).as('updated_at')
  ).where(
    Activity.arel_table[:context_id].eq(8).and(
      Transition.arel_table[:updated_at].gt('2011-12-01 18:56:16')
    )
  ).joins(
    Activity.arel_table.join(Note.arel_table).on(
      Note.arel_table[:activity_id].eq(Activity.arel_table[:id])
    ).join_sources
  ).joins(
    Activity.arel_table.join(Transition.arel_table).on(
      Arel::Nodes::Group.new(
        Transition.arel_table[:transitionable_id].eq(Activity.arel_table[:id]).and(
          Transition.arel_table[:transitionable_type].eq('Activity')
        )
      )
    ).join_sources
  ).as('transitions')
).order(:updated_at).reverse_order.group(:id)