Hi, Let''s say I have two models. One of them we''ll call "articles". The other one is a list of articles that have some special temporary status, like "articles_in_review" or something. Each row of "articles_in_review" just has an "article_id" column and maybe a status attribute and a timestamp. I want to find all of the articles that are "not in review". I know very little about SQL, but I think I could do this with conditions with some sort of a left join or something, but I''m thinking Rails probably has a much cleaner way to do this. I''m guessing you would want to set up specific associations for articles and articles in review, and then somehow use that in your find command, but I can''t seem to come up with it. What''s the right way to do this? thanks, jp -- 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 -~----------~----~----~----~------~----~------~--~---
You only need one model - the "articles_in_review" model is superfluous. Check out the "acts_as_state_machine" plugin. Once implemented, you will have a "state" column on your model that will indicate the state of the article - "draft", "reviewing", "published", "rejected", etc. In your Article model, you could have: def self.not_in_review find(:all, :conditions => "state <> ''reviewing''") end Then, Article.not_in_review will return a collection of articles where status != "reviewing". -- 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 -~----------~----~----~----~------~----~------~--~---
Cayce Balara wrote:> You only need one model - the "articles_in_review" model is superfluous.Thanks, but it was done this way in order to add this new functionality to an existing app with existing "articles". Don''t want to rebuild the db in order to sneak in this new functionality. Is there a clean way to do it in Rails with the models I described? thanks, jp -- 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 -~----------~----~----~----~------~----~------~--~---
Cayce Balara wrote:> You only need one model - the "articles_in_review" model is superfluous.Also, this isn''t the first time I''ve run into something like this. I''d like to know how to do it for the other times when it comes up. To give a more generic example of the problem, take the usual invoicing system sort of thing where you have customers and invoices and products. Let''s say customers habtm invoices, and invoices habtm products. Let''s say I''m adding a feature to the admin part of the site to allow a manager to notify customers of recalls on products. That new feature needs to be able to collect email addresses for customers who bought product xyz between a certain set of dates. What would the find command for something like this look like? Can it be done using the associations instead of building up an SQL query? thanks, jp -- 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 -~----------~----~----~----~------~----~------~--~---
Jeff Pritchard wrote:> Is there a clean way to do it in Rails with the models I described?That is the very point of migrations: script/generate migration add_review_to_article get inside the file that generates, and add add_column :articles, :review_id, :integer then run the migration: rake db:migrate then add tests using the new review. when you deploy, run a command (it might be "cap migrate") to run that migration on your production database. Tip: articles-to-reviews is probably many to many, so look up how to migrate-in a has_and_belongs_to_many association. Rails migrations are a wonderful subject, worthy of their own book. At work, we frequently and safely refactor tables with >10k records in our production databases. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Phlip wrote:> > then add tests using the new review. when you deploy, run a command (it > might be > "cap migrate") to run that migration on your production database.In capistrano 2.x the command is: cap deploy:migrations -- 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 -~----------~----~----~----~------~----~------~--~---
Jeff Pritchard wrote:> > Is there a clean way to do it in Rails with the models I described? > > thanks, > jpI know you''re all trying to be helpful. Thanks for all of the "put it in the main table" answers. I guess I''ll take those answers as a "no" to the question I asked above. thanks, jp -- 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 -~----------~----~----~----~------~----~------~--~---
On 21 Jun 2008, at 21:18, Jeff Pritchard wrote:> > Jeff Pritchard wrote: > >> >> Is there a clean way to do it in Rails with the models I described? >> >> thanks, >> jp > > I know you''re all trying to be helpful. Thanks for all of the "put it > in the main table" answers.The query you want is select * from articles left outer join articles_in_review on article_id = articles.id where articles_in_review.id IS NULL. :include will generate that left outer join for you (:joins does almost that, but creates inner joins, which won''t work here. Fred> > > I guess I''ll take those answers as a "no" to the question I asked > above. > > thanks, > jp > -- > 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 -~----------~----~----~----~------~----~------~--~---
Jeff Pritchard wrote:> Jeff Pritchard wrote: > >> >> Is there a clean way to do it in Rails with the models I described? >> >> thanks, >> jp > > I know you''re all trying to be helpful. Thanks for all of the "put it > in the main table" answers. > > I guess I''ll take those answers as a "no" to the question I asked above. > > thanks, > jpHi Jeff, I''m not sure that you can do what you want with just associations, but I don''t think you have to build the entire SQL either. I have *not* tested this...completely from the hip here: articles = Article.find(:all, :joins => ''left join articles_in_review on articles_in_review.article_id = articles.id'', :conditions => ''articles_in_review.article_id is null'') If I''ve done that correctly, that should be the equivalent to select articles.* from articles left join articles_in_review on articles_in_review.article_id = articles.id where articles_in_review.article_id is null which would return a list of all articles that are not in review. Peace, Phillip -- 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 -~----------~----~----~----~------~----~------~--~---
Frederick Cheung wrote:> On 21 Jun 2008, at 21:18, Jeff Pritchard wrote: > >> in the main table" answers. > The query you want is > select * from articles > left outer join articles_in_review on article_id = articles.id > where articles_in_review.id IS NULL. > > :include will generate that left outer join for you (:joins does > almost that, but creates inner joins, which won''t work here. > > FredThanks Fred and Phillip, Interesting, so I guess one can''t get away from learning some SQL syntax afterall. I''d like to understand this: left outer join articles_in_review on article_id = articles.id where articles_in_review.id IS NULL a little better. Is this basically saying "find me the article_in_review in which article_id = articles.id", and then applying the condition that the id of the returned article_in_review is NULL (in other words, there weren''t any?) This should be adequate to explain why I was hoping there was a way to do this with just the associations. ;) thanks, jp -- 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 -~----------~----~----~----~------~----~------~--~---
> Interesting, so I guess one can''t get away from learning some SQL syntax > afterall.ActiveRecord and SQL are like K&R C and Assembler. You can''t write the former without knowing the latter, because every once in a while you must take the low-level stuff into your own hands. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Phlip wrote:>> Interesting, so I guess one can''t get away from learning some SQL syntax >> afterall. > > ActiveRecord and SQL are like K&R C and Assembler. You can''t write the > former > without knowing the latter, because every once in a while you must take > the > low-level stuff into your own hands.Philip, In a former life I wrote microcontroller code and drivers in C, so I completely understand the analogy...but in this life, I have one job writing middleware in C++ and another writing web apps in RoR, so I was hoping not to have to "write any assembler" in my web apps. ;) cheers, jp -- 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 -~----------~----~----~----~------~----~------~--~---
On Sun, 2008-06-22 at 04:12 +0200, Jeff Pritchard wrote:> Phlip wrote: > >> Interesting, so I guess one can''t get away from learning some SQL syntax > >> afterall. > > > > ActiveRecord and SQL are like K&R C and Assembler. You can''t write the > > former > > without knowing the latter, because every once in a while you must take > > the > > low-level stuff into your own hands. > > Philip, > In a former life I wrote microcontroller code and drivers in C, so I > completely understand the analogy...but in this life, I have one job > writing middleware in C++ and another writing web apps in RoR, so I was > hoping not to have to "write any assembler" in my web apps. ;)---- I''m still trying to decipher K&R You can''t live and die with an SQL db without getting some SQL familiarity because eventually you will need to do something that requires it. Also of significant importance is the knowledge of planning/optimizing/backing up/restoring/when to index, etc. Craig --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Jeff Pritchard wrote:> Interesting, so I guess one can''t get away from learning some SQL syntax > afterall. >Well, technically, you *can* do this without "getting dirty with SQL" :) . You could fetch *all* of the articles and then kick out any that are in review. It could look something like this articles = Article.find(:all, :include => :articles_in_review).reject { |article| article.articles_in_review.any? } [Note that I''m assuming an article could potentially be "in review" more than one time, possibly by multiple "reviewers". If this is not the case, adjust accordingly.] But doing it this way will pull back every single article *and* all the articles_in_review *and* require the Ruby code to loop over the entire collection to see which ones are under review. On the other hand, "getting a little dirty" with SQL off loads that decision to the database server which: 1) can whip through the data considerably faster than Ruby can 2) does not require all of the data to be sent from database server to Ruby, which 3) has a lower memory impact 4) is just a more optimal solution> I''d like to understand this: > left outer join articles_in_review on article_id = articles.id > where articles_in_review.id IS NULL > > a little better. Is this basically saying "find me the > article_in_review in which article_id = articles.id", and then applying > the condition that the id of the returned article_in_review is NULL (in > other words, there weren''t any?) >Basically, yes. In SQL parlance, a LEFT [OUTER] JOIN (the keyword OUTER not typically necessary) means to return all records of the primary table regardless of the existence of a row in the secondary table. So in this case, all articles will be returned regardless of the existence of articles_in_review. Once the join has been completed, the WHERE then further filters the resultset on the condition that the joined artciles_in_review lacks a value for article_id. Since that was the column we joined on, that indicates that the join failed and therefore an articles_in_review record for that article does not exist.> This should be adequate to explain why I was hoping there was a way to > do this with just the associations. ;) >SQL is an acquired taste :) I''ve been doing it in one form or another for many years. Rails does such a good job with most of it that I don''t usually have to think about it. But there are enough places in which something a bit more complicated is required that I construct SQL statements dynamically. I do want to add one note to Fred''s comment about the difference between :include and :join. Whereas using association names in :include will produce LEFT joins and :joins will produce INNER joins, :include also loads the associated objects, while :joins is just there for the SQL processing. In this case it would not have made a difference because you are specifically looking for those that do not have associated objects, so there''d be nothing to load. This may not be the case at other times, and you could possibly be returning and loading more data than is necessary. However, by using :joins and providing the statement explicitly, you get the advantage of knowing it''s a LEFT join without worry about any associated objects being built (and taking up memory unnecessarily).> thanks, > jpNo problem. Peace, Phillip -- 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 -~----------~----~----~----~------~----~------~--~---