Rails - Nov 2009 - Help me understand how to filter complex data

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

On Nov 14, 4:05 am, upisdown
<trimp...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
>
> (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