Filtering data using find and named scopes

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

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.

See the reply I gave the first time you asked this (
http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/72ad9e8771d8e353#
)

Fred

#3

Thank you for your response and patience. I apologize for the double post but I thought since I forgot the [Rails] tag that my post was rejected.

named_scope :not_commented_by, lambda { |user| { :include=> :comments, :conditions => ["(submissions.id = comments.submission_id AND comments.editor_id != ?) OR comments.id IS NULL", [user.id]] } }

I don’t think this represents what you were trying to say:

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

but I am not sure how to implement this statement into a find statement or named scope. I am trying to make it so it doesn’t pull comments of the page of the current editor if that editor has commented already. So in essence being a to do list of submissions.

I appreciate your help though I do now at least see what the issue is. However I am still stumped on how to solve it.

I was on the right track before because I was trying to narrow doing the submission to the comment but it never seemed to work for me. I get an error

#4

I think my last post may have been confusing and after re reading it I would like to clarify.

So I understand I I am pulling multiple submissions for each comment since I am including all comments. I need to somehow filter these comments for my editor before I then remove the ones which have been commented by the specific editor. I am still unclear on how to do this but I think it has to do with combining two find statements.

Thanks!

#5

After hours of trying this is my best guess,

@presort = Genre.find_by_name(“Prose”).submissions.find(:all, :include=> :comments, :conditions => [“comments.editor_id = ? AND submissions.id = comments.submission_id”, @user.id])

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

Anyone have a solution for this issue?

#6

Thank you for your response and patience. I apologize for the double post
but I thought since I forgot the [Rails] tag that my post was rejected.

named_scope :not_commented_by, lambda { |user| { :include=> :comments,
:conditions => ["(submissions.id = comments.submission_id AND
comments.editor_id != ?) OR comments.id IS NULL", [user.id]] } }

I don't think this represents what you were trying to say:

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

rails can't quite generate that for you automatically. The :joins
option lets you specify an arbitrary fragment of sql

Fred

#7

you can join
you can use "detect" or "find_all" after pulling the data for first
time

these are array methods provided by ror

#8

Fred's pretty much nailed this one - apologies for leading you astray
(that's what I get for writing untested SQL before morning coffee...).

So for your example, the scope on Submission would be:

named_scope :not_commented_by, lambda { |u| { :joins => "LEFT OUTER
JOIN comments ON comments.submission_id = submissions.id AND
comments.user_id = #{u.id}", :conditions => 'comments.id IS NULL' } }

Hope this helps!

--Matt Jones

#9

I tried using the join you suggested ":joins => “LEFT OUTER
JOIN comments ON comments.submission_id = submissions.id AND
comments.user_id = #{u.id}” and even tried modifying it a bit to make it work and it gives an error.

Specifically

Mysql::Error: Unknown column 'comments.user_id' in 'on clause'

I will try learning more about joins but it seems the issue is using comments.user_id in the join will not work. I am not sure how to approach it if this is the case.

Thanks.

#10

isn't it just that column is called comments.editor_id not user_id ?

Fred