# Complex conditions: passing a block to where + schema alias
**URL:** https://discourse.rom-rb.org/t/complex-conditions-passing-a-block-to-where-schema-alias/555
**Category:** Issues
**Created:** [August 23, 2022, 9:27pm UTC](https://discourse.rom-rb.org/t/complex-conditions-passing-a-block-to-where-schema-alias/555 "2022-08-23T21:27:46Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@codeslinger](https://discourse.rom-rb.org/u/codeslinger)
#### Post date: [August 23, 2022, 9:27pm UTC](https://discourse.rom-rb.org/t/complex-conditions-passing-a-block-to-where-schema-alias/555/1 "2022-08-23T21:27:46Z")
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?
```auto
def within_effective_range(range)
join(:event)
.where {
( event[:effective_at] < range.max ) &
( event[:effective_until] > range.min )
}
end
```
I get the following error…
```auto
Sequel::DatabaseError: Mysql2::Error: Unknown column 'event.effective_at' in 'where clause'
```
because the sql is incorrect…
```auto
... INNER JOIN `schedule_it_events` ON ...
WHERE ((`event`.`effective_at` < '2022-08-23 17:10:05') ...
```
---
### Author:  [@alassek](https://discourse.rom-rb.org/u/alassek)
#### Post date: [August 25, 2022, 3:48pm UTC](https://discourse.rom-rb.org/t/complex-conditions-passing-a-block-to-where-schema-alias/555/2 "2022-08-25T15:48:12Z")
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
---
### Author:  [@codeslinger](https://discourse.rom-rb.org/u/codeslinger)
#### Post date: [August 25, 2022, 6:46pm UTC](https://discourse.rom-rb.org/t/complex-conditions-passing-a-block-to-where-schema-alias/555/3 "2022-08-25T18:46:48Z")
> What is the schema of the SQL tables?
```auto
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…
```auto
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)
---
### Author:  [@alassek](https://discourse.rom-rb.org/u/alassek)
#### Post date: [August 27, 2022, 5:22am UTC](https://discourse.rom-rb.org/t/complex-conditions-passing-a-block-to-where-schema-alias/555/4 "2022-08-27T05:22:41Z")
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:
```ruby
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 🤷♂️ But on the plus side, real SQL is actually exposed publically as opposed to Rails’ query interface. It’s a tradeoff.
---
### Author:  [@solnic](https://discourse.rom-rb.org/u/solnic)
#### Post date: [August 31, 2022, 5:03am UTC](https://discourse.rom-rb.org/t/complex-conditions-passing-a-block-to-where-schema-alias/555/5 "2022-08-31T05:03:13Z")
> `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`](https://www.rubydoc.info/gems/rom-sql/ROM/SQL/Relation/Reading#where-instance_method) and it does support blocks.
---
### Author:  [@codeslinger](https://discourse.rom-rb.org/u/codeslinger)
#### Post date: [September 1, 2022, 1:44pm UTC](https://discourse.rom-rb.org/t/complex-conditions-passing-a-block-to-where-schema-alias/555/6 "2022-09-01T13:44:00Z")
This clears things up! Thank you both for your time and responses!