2

I have the following query which I would like to know how to execute in Rails 5

SELECT users.id, users.first_name, users.last_name, users.image, users.email, memberships.staff 
FROM users 
LEFT JOIN memberships
ON memberships.shop_id = 1
WHERE memberships.staff = false

I'd like a model method e.g. 

Shop.staff

I've had no success with using .joins()

The join table has

user_id | shop_id | staff
1       | 2       | false
2       | 2       | true

In my Shop model I have:

has_and_belongs_to_many :users, join_table: "memberships"

I don't want to change this particular relationship since I don't require the join table as a model however I do want to filter users by using the staff column in the join table (true or false)

Ideally all the users that are staff should return when I call Shop.staff

Ruegen
  • 605
  • 9
  • 35

1 Answers1

2

I've created a small project to re-do what you have,

Here's how to do the models:

User: user.rb

class User < ApplicationRecord
  has_many :memberships
  has_many :shops, through: :memberships
end

Shop: shop.rb

class Shop < ApplicationRecord
  has_many :memberships
  has_many :users, through: :memberships

  def staff
    return users.joins(:memberships).where(memberships: { staff: true })
  end
end

Membership: membership.rb

class Membership < ApplicationRecord
  belongs_to :user
  belongs_to :shop
end

You'll have to do something like Shop.first.staff that will return a list of users that are marked as Shop Staff.

Roc Khalil
  • 1,365
  • 6
  • 22
  • So I need a model for Membership.. hmm I guess I will have to – Ruegen Dec 22 '18 at 03:59
  • I'm getting ```ActiveRecord::ConfigurationError: Can't join 'User' to association named 'memberships'; perhaps you misspelled it?``` but I have no spelling mistakes.. – Ruegen Dec 22 '18 at 04:30
  • fixed sorted, didn't know you required ```has_many :memberships``` in the user model too in order for it ALL to work – Ruegen Dec 22 '18 at 04:35