Rails的选择子查询（不finder_sql，如果可能的话） [英] Rails select subquery (without finder_sql, if possible)

问题描述

我有一个名为对象的模型（并不重要，它是什么）

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

它有一个默认的价格（列被称为价格）。

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

再有就是计划对象，允许覆盖价格具体日期。

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 ::关联对象之前获取结果自己pferred高度$ P $。

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)

这篇关于Rails的选择子查询（不finder_sql，如果可能的话）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！