From Rails 3.1.3to Ruby 1.9.2I have:
class User < ActiveRecord::Base
has_and_belongs_to_many :states
end
class State < ActiveRecord::Base
has_and_belongs_to_many :users
end
A Useris associated with many States and a Statewith many Users. Given User, I want to find all the others Userthat are in any of her states.
I tried:
class User < ActiveRecord::Base
scope :in_state, lambda { |states| joins(:states).where(:states => states) }
end
User.in_state(current_user.states).all
With this error (formatted for readability):
Mysql2::Error: Not unique table/alias: 'states_users':
SELECT COUNT(*)
FROM `users`
INNER JOIN `states_users` ON `states_users`.`user_id` = `users`.`id`
INNER JOIN `states` ON `states`.`id` = `states_users`.`state_id`
LEFT OUTER JOIN states_users on users.id = states_users.user_id AND states_users.state_id IS NULL
WHERE `states`.`id` IN (8)
I understand that I need to somehow describe one of the links states_users, but I don’t understand how to do this using scope.
Here SQL I would write:
SELECT u2.*
FROM users u
INNER JOIN states_users su ON su.user_id = u.id
INNER JOIN states s ON s.id = su.state_id
INNER JOIN states_users su2 ON s.id = su2.state_id
INNER JOIN users u2 ON u2.id = su2.user_id
WHERE u.id = 771
Thoughts?
Thank.
UPDATE 12/14/2011 @ 10: 48:
Here is what works:
scope :in_states_of, lambda { |user|
joins('INNER JOIN states_users su ON su.user_id = users.id ' +
'INNER JOIN states s ON s.id = su.state_id ' +
'INNER JOIN states_users su2 ON s.id = su2.state_id ' +
'INNER JOIN users u ON u.id = su2.user_id').
where("u.id = ?", user.id) }
User.in_states_of(current_user)
It is not particularly elegant, but it works. You must use the usersinbound link Userso that you can associate this area with others, for example:
scope :active, where(active => true)
User.in_states_of(current_user).active
.