Rails 选择子查询(如果可能，没有 finder_sql)

2021-12-23 00:00:00 sql ruby-on-rails subquery mysql activerecord

我有一个名为 Object 的模型(它是什么并不重要)

I have an model called Object (doesn't really matter what it is)

它有一个默认价格(列称为价格").

It has a default price (column is called "price").

然后有一个 Schedule 对象，它允许覆盖特定日期的价格.

And then there is a Schedule object that allows to override the price for specific dates.

我希望能够在 SQL 查询期间确定最低价格(根据定义，默认价格和当前"价格之间的最低价格)只是为了能够按计算出的最低价格进行订购

I want to be able to determine the MINIMUM price (which is by definition the MINIMUM between the default and "current" price) during the SQL-query just in order to be able to ORDER BY the calculated minimum price

我想让我的搜索查询尽可能高效，我想知道我是否可以做这样的事情:

I want to make my search query as efficient as possible and I was wondering if I can do something like that: 

Object.select("id AS p_id, id, (SELECT MIN(`schedules`.`price`) FROM `schedules` WHERE `schedules`.`object_id` = p_id`) AS objects.min_price").limit(5)

但是，它会生成一个奇怪的 SQL，看起来像这样:

But, it generates an odd SQL that looks like this: 

SELECT `objects`.`id` AS t0_r0, `objects`.`title` AS t0_r1, `objects`.`created_at` AS t0_r2, `objects`.`updated_at` AS t0_r3, `objects`.`preferences` AS t0_r4 ........ (a lot of columns here) ... ` WHERE `objects`.`id` IN (1, 2, 3, 4 ....)

<小时>

所以，如您所见，它不起作用.首先 - 它加载了对象表中的所有列，其次 - 它看起来很糟糕.

So, as you can see it doesn't work. First of all - it loads all the columns from the objects table, and second of all - it looks horrible.

我不想使用 finder_sql 的原因是我有很多可选参数和东西，所以在自己获取结果之前使用 AR::Relation 对象是非常受欢迎的.

The reason why I don't want to use finder_sql is that I have a lot of optional parameters and stuff, so using the AR::Relation object is highly preferred prior to fetching the results themselves.

除了上面提到的，我在数据库中有很多记录，我认为将它们全部加载到内存中不是一个好主意，这就是我要执行这个子查询的主要原因 - 只是为了过滤-输出尽可能多的记录.

In addition to abovementioned, I have a lot of records in the DB, and I think that loading them all into the memory is not a good idea and that is the main reason why I want to perform this subquery - just to filter-out as many records as possible.

有人可以帮助我如何更有效地做到这一点吗?

Can someone help me how to do it more efficiently ?

推荐答案

如果单独生成子查询并使用连接而不是相关子查询，则可以使这更容易:

You can make this easier if you generate the subquery separately and use a join instead of a correlated subquery:

subquery = Schedule.select('MIN(price) as min_price, object_id')
                   .group(:object_id)
                   .to_sql
Object.joins("JOIN (#{subquery}) schedules ON objects.p_id = schedules.object_id")
      .select('objects.*, schedules.min_price')
      .limit(5)

相关文章