ruby-on-rails 具有不同嵌套联接的范围

s3fp2yjn 于 2023-05-02 发布在 Ruby

关注(0)|答案(2)|浏览(140)

我的数据库包括参加训练的跑步者
所以：

class Runner 
  has_many :attendances
  has_many :attended_happenings, through: :attendances, source: :happening
end

class Attendance < ActiveRecord::Base
  belongs_to :happening
  belongs_to :runner
end

class Happening
  has_many :attendances, dependent: :destroy
  has_many :attendees, through: :attendances, source: :runner
end

我想找到所有在过去9周内没有参加任何活动的跑步者。在runner模型中，我创建了一个scope :inactive_for_nine_weeks：

left_joins(:attendances)
  .joins("join happenings on attendances.happening_id = happenings.id")
  .where("attendances.id is null")
  .where("happenings.started_at > ?", 9.weeks.ago)

我想我需要left_join的出勤率，以获得跑步者，* 没有 * 有出勤率，然后我需要做一个定期加入的事件。
虽然我知道应该有结果，但我没有得到任何结果，所以显然执行得很差。
从作用域中获取计数而产生的SQL：

SELECT COUNT(*) FROM "runners" LEFT OUTER JOIN "attendances" ON "attendances"."runner_id" = "runners"."id" join happenings on attendances.happening_id = happenings.id WHERE (attendances.id is null) AND (happenings.started_at > '2023-02-27 08:54:12.460052')

任何提示将不胜感激！

ruby-on-rails

来源：https://stackoverflow.com/questions/76145564/scope-with-different-nested-joins

2条答案

按热度按时间

zhte4eai1#

ActiveRecord中最直接的解决方案是只做一个子查询：

class Runner < ApplicationRecord
  has_many :attendances
  has_many :attended_happenings, through: :attendances, source: :happening

  # scope is really just syntactic sugar for defining class methods
  # and is really only suited for one liners as multi-line lambas are horrible for readibity
  def self.inactive_since(time = 9.weeks.ago)
    where.not(
      id: Attendance.joins(:happening)
                    .where(happenings: { started_at:  ...time })
                    .select(:runner_id)
    )
  end
end

Runner.inactive_since给出以下SQL：

SELECT "runners".* FROM "runners" 
WHERE "runners"."id" NOT IN (
  SELECT "attendances"."runner_id" FROM "attendances" 
  INNER JOIN "happenings" ON "happenings"."id" = "attendances"."happening_id" 
  AND "happenings"."started_at" > $1
)

您还可以使用EXISTS而不是WHERE IN作为一种轻微的优化（在某些DB上）。

class Runner < ApplicationRecord
  def self.inactive_exists(time = 9.weeks.ago)
    where.not(
      Attendance.joins(:happening)
                .select(1)
                .where.not(Attendance.arel_table[:runner_id].eq(arel_table[:id]))
                .where(happenings: { started_at: ...time })
                .arel
                .exists
    )
  end
end

SELECT "runners".* FROM "runners" 
WHERE NOT (
  EXISTS (
   SELECT 1 FROM "attendances" 
   INNER JOIN "happenings" ON "happenings"."id" = "attendances"."happening_id" 
   WHERE "attendances"."runner_id" = "runners"."id" 
   AND "happenings"."started_at" < $1
  )
)

如果你需要它，你可以去更优化的解决方案，不是像横向或交叉连接或使用Postgres上的COUNT(happenings.*) FILTER (WHERE started_at < ?) = 0多语言。但是我会从子查询开始，当你到达它的时候，我会跨过那座桥。

赞(0）回复(0）举报 2023-05-02

shyt4zoc2#

这是针对您的问题的SQL解决方案。
你首先需要交叉加入所有跑步者的所有hapenings得到所有的组合。
在那之后，你用LEFT JOIN检查哪个跑步者在比赛现场，其余的必须为空

CREATE tABLe "runners"("id" int)

INSERT INTO "runners" VALUES (1), (2)

CREATE tABLe "attendances"(id int,"runner_id" int,happening_id int )

INSERT INTO "attendances" VALUES(1,1,1),(2,1,2),(3,2,1)

CREATE tABLe happenings( id int, started_at timestamp)

INSERT INTO happenings VALUEs(1, CURRENT_TIMESTAMP),(2, CURRENT_TIMESTAMP)

SELECT 
  COUNT(*) 
  FROM ("runners" CROSS JOIN happenings) 
  LEFT OUTER JOIN "attendances" ON "attendances"."runner_id" = "runners"."id" 
  AND attendances.happening_id = happenings.id 
 WHERE (attendances.id is null) 
  AND (happenings.started_at > '2023-02-27 08:54:12.460052')

计数
1

fiddle

赞(0）回复(0）举报 2023-05-02

我来回答

ruby-on-rails 具有不同嵌套联接的范围

2条答案

相关问题

热门标签

最新问答