Hi, I have an Articles model. And a Moderations model. And a Users model. Articles have many Users (moderators) through Moderations. Users have many Articles (moderated articles) through Moderations. This works fine so far. I can link up a user with all their moderated articles etc. But, I''d like a find which would select all the Articles, except it hides (does not return) the articles the current user has moderated. I am currently doing this in Ruby in two stages like this: @articles_list = Article.find(:all, :conditions => ["start_date >= ?", today], :order => ''start_date'') @articles_list -= current_user.moderated_articles if current_user And then displaying the articles list. This works, but I fully suspect it is cripplingly slow, and I''d also like to paginate these articles and all the pagination tutorials seem to warn against fetching everything in one go. This seems like good advice. So I am seeking guidance on a join condition that would do what I want. I''m guessing I need an inner join on the article_id on the moderations table, but need to make sure that the user_id of those records are the same as the current user only. So this is confusing me a bit. Many thanks for any help you may offer! - N -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Just a quick try (you haven''t showed up your database schema): @articles = Article.all( :conditions => ["start_date >= ? AND id NOT IN ( SELECT m.article_id from moderations m WHERE m.user_id = ? )", today, current_user.id], :order => ''start_date'') - MaurĂcio Linhares http://alinhavado.wordpress.com/ (pt-br) | http://blog.codevader.com/ (en) On Tue, Apr 7, 2009 at 4:53 PM, Peter Laurens <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Hi, > > I have an Articles model. > And a Moderations model. > And a Users model. > > Articles have many Users (moderators) through Moderations. > Users have many Articles (moderated articles) through Moderations. > > This works fine so far. I can link up a user with all their moderated > articles etc. > > But, I''d like a find which would select all the Articles, except it > hides (does not return) the articles the current user has moderated. I > am currently doing this in Ruby in two stages like this: > > @articles_list = Article.find(:all, :conditions => ["start_date >= ?", > today], :order => ''start_date'') > @articles_list -= current_user.moderated_articles if current_user > > And then displaying the articles list. > > This works, but I fully suspect it is cripplingly slow, and I''d also > like to paginate these articles and all the pagination tutorials seem to > warn against fetching everything in one go. This seems like good advice. > > So I am seeking guidance on a join condition that would do what I want. > I''m guessing I need an inner join on the article_id on the moderations > table, but need to make sure that the user_id of those records are the > same as the current user only. So this is confusing me a bit. > > Many thanks for any help you may offer! > > - N > -- > Posted via http://www.ruby-forum.com/. > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
This is awesome - exactly what I was after and I learned some SQL as well (which is clearly my weakness here). Thanks for taking the time to type that up, you''ve really helped me out. - N -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---