Rails scope not displaying correct information - Rails 4

Question

I would just like to understand rails scopes. Any good explanation would be helpful.

I am trying to display only users who have resumes that have employments

I used a scope to try and display this:

scope :resumes_with_employments, -> {
  where("id in (select resume_id from employments)")
}

I have 2 users. They both have a resume with employments but yet only one user is being displayed.

models

user.rb
has_one :resume

resume.rb
belongs_to :user
has_many :employments

employment.rb
belongs_to :resume

schema

create_table "users", force: true do |t|
  t.string   "email",
  t.string   "firstname"
  t.string   "lastname"
  t.string   "city"
end

create_table "resumes", force: true do |t|
  t.string   "address"
  t.string   "postcode"
  t.text     "summary"
  t.integer  "user_id"
end

create_table "employments", force: true do |t|
  t.string   "companyname"
  t.date     "datestart"
  t.date     "dateend"
  t.integer  "resume_id"
  t.string   "employer_name"
  t.string   "employer_tel"
  t.string   "employer_address"
  t.string   "employer_location"
  t.string   "employer_city"
  t.string   "employer_postcode"
  t.string   "employer_email"
end

spickermann · Accepted Answer · 2016-03-01 17:03:55Z

2

scope :resumes_with_employments, -> {
  where("id in (select resume_id from employments)")
}

would scope the query to users which have an id that matches a resume_id from the employments table. Since the resume_id has nothing to do with the user's id this scope doesn't return useful results.

I would use joins in this case, because it translates to a INNER JOIN in SQL:

# in models/user.rb
scope :resumes_with_employments, -> { joins(resume: :employments) }

The nested INNER JOIN ensures that only users are return that have a resume that has at least one employment. Since resumes might have multiple employments and the nature of INNER JOIN you will need to use uniq to exclude duplicates like this:

User.resumes_with_employments.uniq

Or when you want to count uniq users with that scope:

User.resumes_with_employments.distinct.count(:id)

edited Mar 1, 2016 at 17:03

answered Mar 1, 2016 at 16:46

spickermann

108k9 gold badges115 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ARTLoe Over a year ago

thanks @spickermann i get this error that i do not quite understand

SQLite3::SQLException: ambiguous column name: id: SELECT DISTINCT COUNT(DISTINCT "users"."id") FROM "users" INNER JOIN "resumes" ON "resumes"."user_id" = "users"."id" INNER JOIN "employments" ON "employments"."resume_id" = "resumes"."id" WHERE (id in (select user_id from resumes))

will have a look into it again

ARTLoe Over a year ago

oh i see - i have this method that is trying to do a count which is creating the error <%= pluralize(@users.resumes_with_employments.count, 'jobseeker') %>

spickermann Over a year ago

@ARTLoe I updated my answer to address the Exception.

ARTLoe Over a year ago

awesome! this has resolved a lot of similar SQL query issues i had with the app.....wish i could "UP" your answer more than once. thank you once again!

Collectives™ on Stack Overflow

Rails scope not displaying correct information - Rails 4

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related