Why does the following rails statement
User.find(:all, :joins => [:roles,:roles_users],
:conditions => { :roles => { :name => 'subscriber',
:authorizable_id => self.id,
:authorizable_type => self.class.to_s }})
Translates into this (with 2x the same join)
SELECT "users".* FROM "users"
JOIN "roles_users" ON ("users"."id" = "roles_users"."user_id")
JOIN "roles" ON ("roles"."id" = "roles_users"."role_id")
JOIN "roles_users" roles_users_users ON roles_users_users.user_id = users.id
WHERE ("roles"."authorizable_id" = 4 AND "roles"."name" = 'subscriber' AND "roles"."authorizable_type" = 'Howto')
Just curious.
I didn't need to join to roles_users because the plugin was already doing that once...
Thanks for your help.
has_many :users, :finder_sql => 'SELECT DISTINCT users.* FROM users INNER JOIN roles_users ON user_id = users.id INNER JOIN roles ON roles.id = role_id WHERE authorizable_type = \'#{self.class.base_class.to_s}\' AND authorizable_id = #{id}', :counter_sql => 'SELECT COUNT(DISTINCT users.id) FROM users INNER JOIN roles_users ON user_id = users.id INNER JOIN roles ON roles.id = role_id WHERE authorizable_type = \'#{self.class.base_class.to_s}\' AND authorizable_id = #{id}', :readonly => true
I don't see the duplicate join, I see rails trying to use naming conventions to join roles_users and users resulting in rails looking for roles_users_users table.
because you have a relationship in your model between users and roles you don't have to specify joining roles_users
I have no idea why it would produce that much SQL code. Would need to see more code from your model. Something like this might be less code/complex and produce optimized SQL code:
class Server < ActiveRecord::Base
has_and_belongs_to_many :roles, :join_table => :roles_users
end
User.all(:include => :roles, :conditions => {:roles => { :names => 'subscriber', :authorizable_id => self.id, :authorizable_type => self.class.to_s }})