How to do this without fancy SQL

[11175]

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

[11175]

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".

[11175]

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

[Phlip]

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.

[11175]

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

[11175]

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, jp

[Frederick_Cheung]

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

[11175]

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, jp

Hi 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

[11175]

Frederick Cheung 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.

Fred

Thanks 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

[Phlip]

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.

[11175]

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

[Craig_White2 (Craig White)]

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.

[11175]

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, jp

No problem.

Peace, Phillip