left join in rails/mysql

Question

hi all,

i am newbie in rails and try to perform left join in mysql.

there are two objects - user and message.

user has_and_belongs_to_many messages, message has_and_belongs_to_many users

currently, by simply writing user.messages i get following query in console

SELECT * FROM `messages` INNER JOIN `messages_users` ON `messages`.id = `messages_users`.message_id WHERE (`users_messages`.group_id = 1 )

Message with restricted==false is not connected to any user but is accessible by any user, and i need to add collection Message.all(restricted=>false) to user.messages

the query which would solve my problem would be:

select * from messages left join messages_users on messages_users.message_id=messages.id and messages_users.user_id=1 where (messages_users.user_id is NULL and messages.restricted=false) OR (messages_users.user_id=1 and messages.restricted=true);

how do i write it in rails as elegantly as possible?

would it be smth like

Message.find(:all,:conditions => "(messages_users.user_id is NULL and messages.restricted=false) OR (messages_users.user_id=1 and messages.restricted=true)", :joins => "left join messages_groups on messages_users.message_id=messages.id and messages_users.user_id=1 " )

or can it be nicer?

i am using rails 2.3.2

thanks, Pavel

Answer 1

I think named_scopes might be your answer. In your model put something like:

named_scope :restricted,   :conditions => {:restricted => true}
named_scope :unrestricted, :conditions => {:restricted => false}

Then you can call things like:

Message.restricted
=> All restricted messages

User.first.messages.unrestricted
=> All unrestricted messages belonging to the first user.

I believe these work through HABTM associations.

Answer 2

why not use :include ?

Answer 3

It seems to me that you are trying to pull two things in that query: 1) all messages not tied to a user with restricted=false and 2) all messages tied to the current user with restricted=true.

If I'm understanding that correctly, I don't see a better way to do it if you want it done as a single query. However, if you are open to the idea of making it two queries, you can clean it up slightly in code (while possibly slowing down the execution). Here is the alternative setup:

class Message < ActiveRecord:Base
  has_and_belongs_to_many :users

  named_scope :restricted, :conditions => {:restricted => true}
  named_scope :unrestricted, :conditions => {:restricted => false}
  named_scope :public, :conditions => "id NOT IN (SELECT DISTINCT message_id FROM messages_users)"
end

class User < ActiveRecord:Base
  has_and_belongs_to_many :messages
end

To get the list in less code, but requiring two database hits you can do:

@current_user.messages.restricted + Message.unrestricted.public

In either case if there is any substantial amount of data in these tables you need to make sure they are properly indexed or this is going to slow down with any load. If this is an app with a lot of messages being sent you're probably better off with the single query. If it's just a side function that will not be used very much, I would probably take the cleaner version.

What would probably work better from a model perspective is to get rid of the HABTM relationship and model the relationship explicitly. Then you have a handy place to keep track of other data about the message sending/delivery/receiving process (like tracking time sent, time read, etc). It doesn't change any of the discussion above, I just prefer has many through to HABTM.

class Message < ActiveRecord:Base

  has_many :subscriptions
  has_many :users, :through => :subscriptions

  named_scope :restricted,   :conditions => {:restricted => true}
  named_scope :unrestricted, :conditions => {:restricted => false}
  named_scope :public, :conditions => "id NOT IN (SELECT DISTINCT message_id FROM subscriptions)"
end

class User < ActiveRecord:Base

  has_many :subscriptions
  has_many :messages, :through => :subscriptions

end

class Subscription < ActiveRecord:Base

  belongs_to :message
  belongs_to :user

end