How to do this without fancy SQL

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

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

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

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.

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

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

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

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

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. :wink:

thanks,
jp

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.

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. :wink:

cheers,
jp

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. :wink:

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" :slight_smile:
. 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. :wink:

SQL is an acquired taste :slight_smile: 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