Complex conditions: passing a block to where + schema alias

Issues
1

Where can I find more documentation on restriction DSL to compose more complex conditions?
And more specifically if a schema has an alias (… infer: true, as: :events do …) how do I refer to it in a view?

      def within_effective_range(range)
        join(:event)
          .where {
            ( event[:effective_at] < range.max ) &
            ( event[:effective_until] > range.min )
          }
      end

I get the following error…

Sequel::DatabaseError: Mysql2::Error: Unknown column 'event.effective_at' in 'where clause'

because the sql is incorrect…

... INNER JOIN `schedule_it_events` ON ...
WHERE ((`event`.`effective_at` < '2022-08-23 17:10:05') ...
2

This is very difficult to answer without more information. What is the schema of the SQL tables, and where is schedule_it_events coming from? It would also help to see the exact schema configuration for each relation

3

What is the schema of the SQL tables?

ROM::SQL.migration do
  change do
    create_table "schedule_it_schedule_elements" do
      primary_key :id
      foreign_key :schedule_id, "schedule_it_schedules", null: false
      foreign_key :event_id, "schedule_it_events", null: false

      column :temporal_expression_id, Integer, null: false
      column :temporal_expression_type, String, null: false

      column :created_at, :timestamp, null: false
      column :updated_at, :timestamp, null: false

      index [:schedule_id, :event_id], unique: true
    end
  end
end

ROM::SQL.migration do
  change do
    create_table "schedule_it_events" do
      primary_key :id

      column :name, String, null: false
      column :effective_at, DateTime, null: false
      column :effective_until, DateTime, null: false

      column :created_at, :timestamp, null: false
      column :updated_at, :timestamp, null: false
    end
  end
end

Here are the relations…

  module Relations
    class ScheduleElements < ROM::Relation[:sql]
      gateway :default

      schema :schedule_it_schedule_elements, infer: true, as: :schedule_elements do
        associations do
          belongs_to :schedule
          belongs_to :event

        end

        use :timestamps, attributes: %i[created_at updated_at]
      end

      def within_effective_range(range)
        join(:event)
          .where {
            ( event[:effective_at] <= range.max ) &
            ( event[:effective_until] >= range.min )
          }
      end

    end
  end

  module Relations
    class Events < ROM::Relation[:sql]
      gateway :default

      schema :schedule_it_events, infer: true, as: :events do
        attribute :effective_at, Types::DateTime, read: Types.Constructor(Time, &:to_datetime)
        attribute :effective_until, Types::DateTime, read: Types.Constructor(Time, &:to_datetime)
        
        associations do
          has_one :schedule_element
        end

        use :timestamps, attributes: %i[created_at updated_at]
      end

    end
  end

The error comes from calling #within_effective_range(range)

4

Okay, I have a reproduction script working. The reason is deceptively simple.

There are two SQL interfaces, Sequel with a thin layer of ROM::SQL on top. ROM::SQL knows about your relations, Sequel does not. You have to take care when moving from one to the other.

join(:event) works because this is a ROM interface, and it knows what event is.

event[:effective_at] does not work, because where { ... } is executed by Sequel, not by ROM. It’s interpreting event as a regular SQL table.

Rule of thumb: executing SQL within blocks is plain SQL without knowledge of your relation objects.

You can write where { schedule_it_events[:effective_at] >= range.min } but that sort of defeats the purpose of making an easier alias.

I suggest doing this:

def within_effective_range(range)
  join(:event)
    .where(events[:effective_at] >= range.min)
    .where(events[:effective_until] <= range.max)
end

Since these are expressed as method args instead of blocks, events here refers to the ROM::Relation instead of a Sequel object, and the aliasing works.

It’s a somewhat leaky abstraction :man_shrugging: But on the plus side, real SQL is actually exposed publically as opposed to Rails’ query interface. It’s a tradeoff.

5

event[:effective_at] does not work, because where { ... } is executed by Sequel, not by ROM

It is executed by ROM. We have our own Relation#where and it does support blocks.

6

This clears things up! Thank you both for your time and responses!

1 Like