将SQL语句转换为Arel Rails查询 [英] Convert SQL statement to Arel Rails query

问题描述

所以，让我们直接说：我很努力地要几天才能转换此SQL语句：

So, let's get straight: I'm struggling to days do convert this SQL Statement:

select * from (
    select distinct on (program_id) editions.*, programs.* 
    from editions inner join programs on editions.program_id = programs.id 
    where programs.station_id = 1) as editionsprograms 
order by fixed desc, published_at desc, time desc 
limit 4;

到Arel rails查询。有人对如何进行与上述SQL具有相同效果的Arel查询有任何想法吗？

to an Arel rails query. Someone has any idea about how could make an Arel query that would have the same effect of the above SQL?

推荐答案

from 方法来完成您棘手的部分查询（从子查询中选择）。子查询本身应该是一个简单的映射（ select ， joins ， where 等）。因此结果将是这样的：

You can use the from method to accomplish the tricky part of your query (selecting from a subquery). The subquery itself should be a straightforward mapping (select, joins, where, etc). So the result would be something like this:

Edition.select('*')
.from(
  Editions.select('DISTINCT ON (program_id) editions.*, programs.*')
          .joins(:programs)
          .where(programs: { station_id: 1 })
)
.order(fixed: :desc, published_at: :desc, time: :desc)
.limit(4)

这将返回版本记录，并将程序记录中的其他数据存储在每个记录中。为避免该结果类型的怪异性（可能还会抛出错误且无法解析），您可以直接使用Arel（类似语法），也可以在其上使用 .pluck 最终只选择您实际需要的列。

This will return Edition records, with additional data from the Program record stored on each. To avoid the weirdness of that result type, which may also just throw errors and be unable to resolve, you could either use Arel directly (similar syntax) or use .pluck on the end to pluck only the columns you actually need.

这篇关于将SQL语句转换为Arel Rails查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！