Help me understand how to filter complex data

rubyonrails-talk
1

So I am working on this project but I am having trouble with a find statement. My code is mostly working but it falls apart when more entries are added.

I am working on a submission manager. I want a table of submissions for editors of a specific genre for which they haven't commented. When they comment I want it removed from table so that only the submissions they need to work on are showing. I tried two methods that produce the same results. One I used a find statement and another I tried a named scope. Right now I can filter out the submissions of the specific genre and then remove ones that have been commented on but soon as a second editor comments on them they show up again.

Here is my code for the named scope

(In the submission model)

named_scope :not_commented_by, lambda { |u| { :include => :comments, :conditions => ['comments.editor_id != ? OR comments.id IS NULL', u.id] } }

(In the User Controller)

@prose = Genre.find_by_name('Prose').submissions.not_commented_by (@user)

Here is my code for the find statement

(In the user controller)

@prose = Genre.find_by_name('Prose').submissions.find(:all, :include => :comments, :conditions => ['comments.editor_id != ? OR comments.id IS NULL', @user.id])

Can anyone explain why this would stop working when a second comment is made? Anyone have suggestions for implementing this better?

Thank you for your time, Christian

2

Here is my code for the named scope

(In the submission model)

named_scope :not_commented_by, lambda { |u| { :include => :comments, :conditions => ['comments.editor_id != ? OR comments.id IS NULL', u.id] } }

include and & conditions is a slightly gnarly business. Once two people have commented, you have 2 rows in your comments table:

submission_id editor_id 1 1 1 2

A find with an include like that boils down to

select * from submissions left outer join comments on submissions,id = submission_id where ...

Before conditions are applied, a row appears for each comment that can be joined to a submission, ie you get one row with comment made by editor 1, and one row with the comment made by editor 2. Your conditions filter out one of these, leaving the second row.

dropping down to the sql level, one way of writing this is

select submissions.* from submissions left outer join comments on submissions.id = submission_id AND editor_id =1 where comments.id IS NULL;

The extra condition on the comments join ensures that we ignore comments made by other people and the where clause picks out those cases where the db couldn't find a comment

Fred