高效的rails查询包括record（返回boolean）(efficient rails query includes record (returns boolean))

高效的rails查询包括record（返回boolean）(efficient rails query includes record (returns boolean))

我的人有分数，我想要一种有效的方法来查询给定用户是否在顶级X用户中。

# person.rb class Person scope :top_score, -> {order('score DESC')} scope :page_limit, -> { limit(10) } def self.in_top_score(id) top_score.page_limit.something_something_soemthign? end end

以前在做：

user.id.in?(top_score.page_limit.pluck(:id))

但我更愿意将此检查移至数据库，以防止数百/数千条记录的对象序列化。

Person.order('score DESC').select([:score, :id]).limit(1) Person Load (0.5ms) SELECT score, id FROM `people` ORDER BY score DESC LIMIT 1 => [#<Person id: "dxvrDy...", score: 35>]

现在检查该列表中是否存在另一个用户^^

Person.order('score DESC').select([:score, :id]).limit(1).exists?({id: "c_Tvr6..."}) Person Exists (0.3ms) SELECT 1 AS one FROM `people` WHERE `people`.`id` = 'c_Tvr6...' LIMIT 1 => true

返回true但应返回false

My people have scores and I'd like an efficient way to query if the given user is in the top X users.

# person.rb class Person scope :top_score, -> {order('score DESC')} scope :page_limit, -> { limit(10) } def self.in_top_score(id) top_score.page_limit.something_something_soemthign? end end

previously was doing:

user.id.in?(top_score.page_limit.pluck(:id))

but i'd prefer to move this check to the database to prevent the object serialization of hundreds/thousands of records.

Person.order('score DESC').select([:score, :id]).limit(1) Person Load (0.5ms) SELECT score, id FROM `people` ORDER BY score DESC LIMIT 1 => [#<Person id: "dxvrDy...", score: 35>]

now to check if another user exists in that list^^

Person.order('score DESC').select([:score, :id]).limit(1).exists?({id: "c_Tvr6..."}) Person Exists (0.3ms) SELECT 1 AS one FROM `people` WHERE `people`.`id` = 'c_Tvr6...' LIMIT 1 => true

returns true but should return false

最满意答案

更新的答案

对不起，我的原始答案不正确。 （ exists?查询显然使用LIMIT 1并从page_limit范围覆盖LIMIT 10 ，并且显然也抛出了ORDER BY子句。完全错误！:-p）

那这个呢？ 它有点不那么优雅，但这次我实际测试了答案:-p，它似乎按照需要工作。

def self.in_top_score?(id) where(id: id).where(id: Person.top_score.page_limit).exists? end

这是我的测试（使用Rails 4.2.6）和它生成的SQL（使用子查询）的示例用法：

pry(main)> Person.in_top_score?(56) Person Exists (0.4ms) SELECT 1 AS one FROM "people" WHERE "people"."id" = $1 AND "people"."id" IN (SELECT "people"."id" FROM "people" ORDER BY "people"."score" DESC LIMIT 10) LIMIT 1 [["id", 56]] => false

在我的测试中，与原始版本相比，这确实至少有一点性能提升。

---

原始答案

top_score.page_limit.exists?(user.id)

http://apidock.com/rails/ActiveRecord/FinderMethods/exists%3F

updated answer

Sorry, my original answer was incorrect. (The exists? query evidently uses LIMIT 1 and overwrites the LIMIT 10 from the page_limit scope, and evidently throws out the ORDER BY clause, too. Totally wrong! :-p)

What about this? It's a little bit less elegant, but I actually tested the answer this time :-p, and it seems to work as desired.

def self.in_top_score?(id) where(id: id).where(id: Person.top_score.page_limit).exists? end

Here's an example usage from my testing (using Rails 4.2.6) and the SQL it generates (which uses a subquery):

pry(main)> Person.in_top_score?(56) Person Exists (0.4ms) SELECT 1 AS one FROM "people" WHERE "people"."id" = $1 AND "people"."id" IN (SELECT "people"."id" FROM "people" ORDER BY "people"."score" DESC LIMIT 10) LIMIT 1 [["id", 56]] => false

In my testing, this does indeed have at least a bit of a performance boost compared to your original version.

---

original answer

top_score.page_limit.exists?(user.id)

http://apidock.com/rails/ActiveRecord/FinderMethods/exists%3F