ActiveRecord/ARel 修改包含中的左连接中的“ON”

发布于 2024-12-16 18:00:45 字数 780 浏览 0 评论 0原文

我想知道是否可以使用 ActiveRecord includes 指定额外的 JOIN ON 标准?

例如:我正在获取一条记录并包含与某些条件的关联

record.includes(:other_record).where(:other_record => {:something => :another})

这给了我(大致):

select * from records 
left outer join other_records on other_records.records_id = records.id 
where other_records.something = another

有谁知道我如何指定额外的连接条件,以便我可以实现类似的目标。

select * from records 
left outer join other_records on other_records.records_id = records.id 
    and other_records.some_date > now()
where other_records.something = another

我希望我的包含内容能够引入 other_records,但我需要在加入中添加其他条件。任何使用 ARel 的东西都会很棒,我只是不知道如何将 ARel 的左外连接插入到 ActiveRecord::Relation 中

I'm wondering if it's possible to specify additional JOIN ON criteria using ActiveRecord includes?

ex: I'm fetching a record and including an association with some conditions

record.includes(:other_record).where(:other_record => {:something => :another})

This gives me (roughly):

select * from records 
left outer join other_records on other_records.records_id = records.id 
where other_records.something = another

Does anyone know how I can specify an extra join condition so I could achieve something like.

select * from records 
left outer join other_records on other_records.records_id = records.id 
    and other_records.some_date > now()
where other_records.something = another

I want my includes to pull in the other_records but I need additional criteria in my join. Anything using ARel would also be great, I've just never known how to plug a left outer join from ARel into and ActiveRecord::Relation

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

深者入戏 2024-12-23 18:00:45

我可以让你接近 ARel。 注意:我的代码最终在幕后调用了两个查询,我将对此进行解释。

最近,我自己必须在 ARel 中解决 LEFT JOIN。在使用 ARel 时,您可以做的最好的事情就是启动 Rails 控制台或 IRB 会话并在 ARel 对象上运行 #to_sql 方法以查看它们代表哪种 SQL。尽早并经常这样做!

这是您的 SQL,为了一致性做了一些修改:

SELECT *
FROM records
  LEFT OUTER JOIN other_records ON other_records.record_id = records.id
    AND other_records.some_date > now()
WHERE other_records.something = 'another'

我假设您的 records 模型是 Record,other_records 是 OtherRecord。转换为 ARel 和 ActiveRecord:

class Record < ActiveRecord::Base

  # Named scope that LEFT JOINs OtherRecords with some_date in the future
  def left_join_other_in_future
    # Handy ARel aliases
    records = Record.arel_table
    other   = OtherRecord.arel_table

    # Join criteria
    record_owns_other = other[:record_id].eq(records[:id])
    other_in_future   = other[:some_date].gt(Time.now)

    # ARel's #join method lets you specify the join node type. Defaults to InnerJoin.
    # The #join_sources method extracts the ARel join node. You can plug that node
    #   into ActiveRecord's #joins method. If you call #to_sql on the join node,
    #   you'll get 'LEFT OUTER JOIN other_records ...'
    left_join_other = records.join(other, Arel::Nodes::OuterJoin).
                              on(record_owns_other.and(other_in_future)).
                              join_sources

    # Pull it together back in regular ActiveRecord and eager-load OtherRecords.
    joins(left_join_other).includes(:other_records)
  end

end

# MEANWHILE...

# Elsewhere in your app
Record.left_join_other_in_future.where(other_records: {something: 'another'})

我将连接封装在命名范围内,因此您不需要将所有 ARel 与应用程序逻辑混合在一起。

我的 ARel 最终在幕后调用两个查询:第一个使用 JOIN 和 WHERE 标准获取记录,第二个使用来自以下位置的所有记录 ID 的大列表获取所有其他记录“WHERE other_records.record_id IN (...)”第一个查询。

Record.includes() 绝对可以为您提供所需的 LEFT JOIN,但我不知道如何将您自己的条件注入到连接中。如果您想自己编写 SQL,您可以使用 Record.joins() 代替 ARel:

Record.joins('LEFT OUTER JOIN other_records' +
             ' ON other_records.record_id = records.id' +
             ' AND other_records.some_date > NOW()')

我真的非常喜欢让数据库适配器编写我的 SQL,所以我使用了 ARel。

如果是我,我会考虑将附加连接条件放在 WHERE 子句中。我假设您问这个问题是因为在连接上添加附加条件会使查询的 EXPLAIN 看起来更好,或者因为您不想在没有任何相关的 other_records 时处理 other_records.some_date 列中的 NULL。

I can get you close with ARel. NOTE: My code ends up calling two queries behind the scenes, which I'll explain.

I had to work out LEFT JOINs in ARel myself, recently. Best thing you can do when playing with ARel is to fire up a Rails console or IRB session and run the #to_sql method on your ARel objects to see what kind of SQL they represent. Do it early and often!

Here's your SQL, touched up a bit for consistency:

SELECT *
FROM records
  LEFT OUTER JOIN other_records ON other_records.record_id = records.id
    AND other_records.some_date > now()
WHERE other_records.something = 'another'

I'll assume your records model is Record and other_records is OtherRecord. Translated to ARel and ActiveRecord:

class Record < ActiveRecord::Base

  # Named scope that LEFT JOINs OtherRecords with some_date in the future
  def left_join_other_in_future
    # Handy ARel aliases
    records = Record.arel_table
    other   = OtherRecord.arel_table

    # Join criteria
    record_owns_other = other[:record_id].eq(records[:id])
    other_in_future   = other[:some_date].gt(Time.now)

    # ARel's #join method lets you specify the join node type. Defaults to InnerJoin.
    # The #join_sources method extracts the ARel join node. You can plug that node
    #   into ActiveRecord's #joins method. If you call #to_sql on the join node,
    #   you'll get 'LEFT OUTER JOIN other_records ...'
    left_join_other = records.join(other, Arel::Nodes::OuterJoin).
                              on(record_owns_other.and(other_in_future)).
                              join_sources

    # Pull it together back in regular ActiveRecord and eager-load OtherRecords.
    joins(left_join_other).includes(:other_records)
  end

end

# MEANWHILE...

# Elsewhere in your app
Record.left_join_other_in_future.where(other_records: {something: 'another'})

I bottled the join in a named scope so you don't need to have all that ARel mixed in with your application logic.

My ARel ends up calling two queries behind the scenes: the first fetches Records using your JOIN and WHERE criteria, the second fetches all OtherRecords "WHERE other_records.record_id IN (...)" using a big list of all the Record IDs from the first query.

Record.includes() definitely gives you the LEFT JOIN you want, but I don't know of a way to inject your own criteria into the join. You could use Record.joins() instead of ARel if you wanted to write the SQL yourself:

Record.joins('LEFT OUTER JOIN other_records' +
             ' ON other_records.record_id = records.id' +
             ' AND other_records.some_date > NOW()')

I really, really prefer to let the database adapter write my SQL, so I used ARel.

If it were me, I'd consider putting the additional join criterion in the WHERE clause. I assume you're asking because putting the additional criterion on the join makes the query's EXPLAIN look better or because you don't want to deal with NULLs in the other_records.some_date column when there aren't any related other_records.

江城子 2024-12-23 18:00:45

如果您有一个简单的(相等)额外连接条件,它可能只是

record.includes(:other_record).where(:other_record => {:something => :another,
                                                       :some_date => Time.now})

但如果您需要大于比较,则可以执行以下操作。

record.includes(:other_record).where([
  'other_records.something = ? and other_records.some_date > ?',
   another, Time.now])

希望有帮助。

If you have a simple (equality) extra join condition it could simply be

record.includes(:other_record).where(:other_record => {:something => :another,
                                                       :some_date => Time.now})

But if you need the greater than comparison the following should do it.

record.includes(:other_record).where([
  'other_records.something = ? and other_records.some_date > ?',
   another, Time.now])

Hope that helps.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文