代码之家 › 专栏 › 技术社区 › Eric Norcross

带PostgreSQL重叠方法的Arel表

arel postgresql ruby-on-rails

0

Eric Norcross · 技术社区 · 6 年前

PostgreSQL OVERLAPS Arel . 为此,这个问题更多的是为了更好地理解Arel,而不是实现某些版本的重叠功能。

Rails 5.1

这是我试图在Arel中复制的SQL语句:

SELECT DISTINCT
  venues.id
FROM
  venues
LEFT OUTER JOIN
  reservations
ON
  venues.id = reservations.venue_id
WHERE (
  (reservations.venue_id IS NULL)
  OR NOT (
    (reservations.start, reservations.end)
    OVERLAPS
    (DATE '2018-09-02', DATE '2018-09-03')
  )
)

Venue.arel_table.project(
  Venue.arel_table[:id]
).from(
  Venue.arel_table
).join(
  Reservation.arel_table, Arel::Nodes::OuterJoin
).on(
  Venue.arel_table[:id].eq(Reservation.arel_table[:venue_id])
).where(
 Arel::Nodes::Grouping.new(
    Reservation.arel_table[:venue_id].eq(nil)
  ).or(
    Arel.sql("NOT ((reservations.start, reservations.end) OVERLAPS (DATE '2018-09-02', DATE '2018-09-03'))")
  )
)

然而,我觉得可能有更好的方法来实现这一点;我一直在尝试实现 Arel::Nodes::NamedFunction OVERLAPS 但是,当我替换上面的 Arel.sql

Arel::Nodes::NamedFunction.new(
  'OVERLAPS',
  [
    Arel::Nodes::SqlLiteral.new('(reservations.start, reservations.end)'),
    Arel::Nodes::SqlLiteral.new("(DATE '2018-09-02', DATE '2018-09-03')")
  ]
)

结果

...OVERLAPS ((reservations.start, reservations.end), (DATE '2018-09-02', DATE '2018-09-03'))

正确的实施方法是什么 重叠

1 回复 | 直到 6 年前

1

cyberskunk 6 年前

自 OVERLAPS

Arel::Nodes::InfixOperation.new(
  'OVERLAPS', 
  Arel::Nodes::SqlLiteral.new('(reservations.start, reservations.end)'),
  Arel::Nodes::SqlLiteral.new("(DATE '2018-09-02', DATE '2018-09-03')"
)