CODEWITHSUNDEEP
ruby-on-rails
Mulite
Mulite

Reputation: 3

How can I use a scope in a join query?

I want to use a scope of a joined table. The goal is to write a scope for autors that have reports with a specific stat_id (for example 15)

Rails 5.2.3

class Author < ApplicationRecord
  belongs_to :report

class Report < ApplicationRecord
  has_many :authors
  scope :with_stat, ->(s) {
    where(stat_id: s)
  }

This works fine:

Autor.joins(:report).where(reports: {stat_id: 15})

If the scope is more complex. How can I use the scope from class Report? This doesn't work:

Autor.joins(:report).where(reports: {with_stat(15)})

What is the correct syntax?

Upvotes: 0

Views: 374

Answers (1)

max
max

Reputation: 101871

That scope will not give you the correct query.

What you want is Author.joins(:report).where(reports: { stat_id: 1 }). Which gives a single query:

Author Load (1.0ms)  SELECT  "authors".* FROM "authors" INNER JOIN "reports" ON "reports"."id" = "authors"."report_id" WHERE "reports"."stat_id" = $1 LIMIT $2

This is what happens if you use the scope instead:

irb(main):004:0> Author.joins(:report).where(Report.with_stat(1))
  Report Load (1.6ms)  SELECT "reports".* FROM "reports" WHERE "reports"."stat_id" = $1  [["stat_id", 1]]
  Author Load (0.6ms)  SELECT  "authors".* FROM "authors" INNER JOIN "reports" ON "reports"."id" = "authors"."report_id" LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation []>
irb(main):005:0> Author.joins(:report).where(report: Report.with_stat(1))
  Author Load (2.1ms)  SELECT  "authors".* FROM "authors" INNER JOIN "reports" ON "reports"."id" = "authors"."report_id" WHERE "authors"."report_id" IN (SELECT "reports"."id" FROM "reports" WHERE "reports"."stat_id" = $1) LIMIT $2  [["stat_id", 1], ["LIMIT", 11]]
=> #<ActiveRecord::Relation []>

The later uses a subquery which should give the same result but should be less effective.

What you can do is place the scope on the other side of the association:

class Author < ApplicationRecord
  belongs_to :report

  scope :with_stat, ->(s){
    joins(:report).where(reports: {stat_id: s})
  }
end

irb(main):010:0> Author.joins(:report).where(reports: { stat_id: 1 })
  Author Load (1.1ms)  SELECT  "authors".* FROM "authors" INNER JOIN "reports" ON "reports"."id" = "authors"."report_id" WHERE "reports"."stat_id" = $1 LIMIT $2  [["stat_id", 1], ["LIMIT", 11]]
=> #<ActiveRecord::Relation []>

Upvotes: 1

Related Questions