I am trying my first join and the sql that it's generating is very odd.
I have a Recipient belongs to a User. I am trying to query all the recipients by a user that are also not read and not deleted:
scope :unread, where(:is_read => false).where(:is_deleted => false)
scope :unread_by_user_id, lambda { |id| unread.joins(:user).merge(User.by_id(id)) }
This is the sql it generates:
SELECT `recipients`.* FROM `recipients` INNER JOIN `users` ON `users`.`id` IS NULL WHERE `recipients`.`is_read` = 0 AND `recipients`.`is_deleted` = 0 AND `users`.`id` = 475
Is there any way I can get rid of the "IS NULL"? That's not supposed to be there :(
I have tried searching google, and it's actually really amazing that 95% of examples out there do not talk about joins. The few examples of joins that I do find use the & syntax that has become depreciated. The documentation for this is actually quite bad compared to other things. Very odd indeed.
Anyway, I can't get this to work. It's definitely not a good day when you've been developing software for 19 years and can't get sql to join on a single table :( I can write queries in sql with 15 joins no problem manually. I guess that's the price you pay sometimes when you go through and learn new frameworks. It's not this weird though in Hibernate :/
Assuming you have your user fetched already:
user.recipients.unread
Recipients association already limits recipients to that user. Having a separate scope for that doesn't make sense to me.
--edit
This works if your User model has has_many :recipients association defined.