How to do this without fancy SQL

Rails
1

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

2

Cayce B. 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

3

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

4

Jeff P. 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.

5

Cayce B. 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

6

Jeff P. 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

7

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

8

Jeff P. wrote:

Jeff P. 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

9

On 21 Jun 2008, at 21:18, Jeff P. 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

10

Frederick C. wrote:

On 21 Jun 2008, at 21:18, Jeff P. 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

11

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.

12

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

13

On Sun, 2008-06-22 at 04:12 +0200, Jeff P. wrote:

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:

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

14

Jeff P. 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