The Context

Consider these 2 models: Group and Event

class Group < ApplicationRecord
  include FlagShihTzu # https://github.com/pboling/flag_shih_tzu

  has_many :events
  has_flags 5 => :reminders_enabled # other flags exist we are ignoring
  # reminder_offset: integer (in seconds)
end

class Event < ApplicationRecord
  belongs_to :group
  # starts_at: datetime
  # remind_at: datetime (will be nil if reminders are disabled)

  before_create do
    self.remind_at = group.reminders_enabled? ? starts_at - group.reminder_offset : nil
  end
end

The Problem

When you update a group’s default reminder, we also want to update the remind_at time for all of the group’s events.

First attempt

group.events.each do |group|
  event.update(remind_at: group.reminders_enabled? ? starts_at - group.reminder_offset : nil)
end

This is the most straightforward approach. The downside is that it requires a separate update query for every event. If you have a lot of events, that is a lot of calls to the database O(n). Is there a way to do the same work with a single query? The trouble is that remind_at is calculated from 3 separate values:

• event.starts_at
• group.reminders_enabled
• groups.reminder_offset

So each event will have a different value. This prevents us from doing a simple group.events.update_all(remind_at:). What options are we left with?

Enter MySQL case statements

MySQL supports using CASE statements within a SET command. How might this look?

UPDATE events
INNER JOIN groups ON groups.id = events.group_id
SET events.remind_at =
    CASE
        WHEN (groups.flags & 16) = 16
        THEN DATE_SUB(events.starts_at, INTERVAL groups.reminder_offset SECOND)
        ELSE NULL
    END;

There’s a lot going on here, let’s break it down.

(groups.flags & 16) = 16

We are storing flags in an integer column with the flag_shih_tzu gem, where each flag is a different bit. If reminder_enabled is stored on bit 5, it means that the fifth bit of the integer column will be used to determine whether the reminder is enabled or not. In binary representation, this would mean the value 00010000, or 16 in decimal notation.

To check if the flag is enabled, you would perform a bitwise AND (&) operation between the column value and 16 (which will return either 0 or 16) and see if the result is 16.

DATE_SUB(events.starts_at, INTERVAL groups.reminder_offset SECOND)

MySQL provides really helpful date arithmetic functions. DATE_SUB lets us subtract an interval from the starts_at date. The final INTERVAL n SECOND statement is used to coerce the plain integer value into something DATE_SUB understands.

This is really cool so far, but mixing raw SQL statements with ActiveRecord doesn’t always feel amazing. Is there another option that keeps us in ActiveRecord land?

Enter Arel

Arel is a lesser-known but incredibly powerful companion to ActiveRecord. It lets us build out complex SQL in plain ruby.

Arel’s Building Blocks

1. Arel Tables

   Using arel_table, we are able to treat our models like tables. This gives us the ability to refer to columns as if we were dealing with SQL directly:

    Event.arel_table[:starts_at]
    Group.arel_table[:reminder_offset]
   

2. Bitwise Operations

   To check if a flag is enabled in a bitwise column:

    (Group.arel_table[:flags] & bit).eq(bit)
   

3. Conditional Logic with Case

   Arel’s Nodes::Case lets us construct a SQL CASE statement:

    Arel::Nodes::Case.new
      .when(condition)
      .then(value_if_true)
      .else(value_if_false)
   

4. Date Arithmetic

   For date subtraction, we can utilize Arel’s capability to create custom SQL functions:

    Arel::Nodes::NamedFunction.new(
      'DATE_SUB',
      [starts_at, interval_expression(offset)],
    )
   

5. Building Raw SQL Expressions

   Sometimes we need to craft custom SQL expressions. Arel’s SqlLiteral makes this possible:

    Arel::Nodes::SqlLiteral.new("INTERVAL ... SECOND")
   

The Final Class

By combining these concepts, we can create a new class using plain old ruby and ActiveRecord.

class ApplyGroupDefaultsToEvents
  def update_reminder_offsets(events)
    events.joins(:group).update_all(remind_at:)
  end

  private

  def remind_at
    Arel::Nodes::Case.new
      .when(group_flag_enabled(:reminder_enabled))
      .then(date_subtraction(
        Event.arel_table[:starts_at],
        Group.arel_table[:reminder_offset],
      ))
      .else(nil)
  end

  def group_flag_enabled(flag)
    bit = Group.flag_mapping["flags"][flag]
    (Group.arel_table[:flags] & bit).eq(bit)
  end

  def date_subtraction(starts_at, offset)
    Arel::Nodes::NamedFunction.new(
      'DATE_SUB',
      [starts_at, interval_expression(offset)]
    )
  end

  def interval_expression(offset)
    Arel::Nodes::SqlLiteral.new(
      "INTERVAL `#{offset.relation.name}`.`#{offset.name}` SECOND"
    )
  end
end

Conclusion

Isn’t that cool! I’ve been writing Rails code for over 10 years and am just now learning about Arel. That is what I love about this job, it feels like I learn something new every day.

So the next time you find yourself writing plain SQL strings in your Rails code, give Arel a try instead.