1

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 :/

divibisan
  • 11,659
  • 11
  • 40
  • 58
egervari
  • 22,372
  • 32
  • 121
  • 175

1 Answers1

2

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.

Heikki
  • 15,329
  • 2
  • 54
  • 49
  • I think I figured it out. One of the associates was 'user' and not 'reciever' because of rails' defaults. So that did the trick. I'm used to seeing all the mapping configuration in one spot... so getting used to rails is actually a little challenging since it's all scattered in different places (migration and in 2 models rather than just 1 file). Anyway, your above code will unfortunately not work because I want the private messages, not the recipient objects :/ So I had to do it the way I am doing it. – egervari May 03 '11 at 05:26
  • A mention about messages / simplified model code in the question would have given you better answer. ;) – Heikki May 03 '11 at 05:33
  • Yeah, my bad. This scope was just a means to an end - it wasn't supposed to be the final query - just a part of a larger query. – egervari May 03 '11 at 05:49