Find_by_SQL
In Rails, if you have some model object and you want to find a particular object (or group of objects), you normally use the find function, like so (say, for a blog application):
Posts.find(:all, :published => true)
But what about a more complex selection? Say you store the last publication date of each user, and you require each user to post a new post every few days–on a schedule they specify. Some might want to post every other day, others once a week or once a month.
So your authors model has the attributes last_published_on and post_every_n_days. And you want to select not just the last date and the number of days, but the next date. How do you do this?
You can just use find_by_sql. It takes an SQL statement as a parameter, and returns objects, with all columns specified in your select–even if they’re not part of your model.
So you can write code like this:
@authors = Author.find_by_sql("SELECT last_published_on, post_every_n_days, ADDDATE(last_published_on, post_every_n_days) AS next_publication_date, first_name + ' ' + last_name AS full_name FROM authors")
(Note: ADDDATE is a MySQL function that adds a number of days to a date, and returns the new date.)
Although this function will return a collection of Author models (because you used Author.find), and even though the Author model doesn’t have a next_publication_date or full_name attribute, both of those will be present. So you can do things like:
<% @authors.each do |a| %>
<%= "<p>#{a.full_name}'s next publication date is on #{a.next_publication.date}!</p>" %>
<% end %>
This will print a list of authors (by full name), and their next publication date–like this:
Salmaan Ahmed: 2008-04-17
Jack Furgeson: 2008-05-01
Ming Li: 2008-04-09
Or if you have a simpler case, where you want to group something and use the HAVING clause. Rails’ find function doesn’t allow you to specify having clauses–so you need to use find_by_sql.
Note that the standard find function has several attributes you can use for almost any SQL statement imaginable:
Model.find(:all,
:select => "title, content" #select clause
:join => "authors ON posts.id = authors.id" # JOIN clause
:group => "author_id" # group-by clause
)
<code>
You can read the full API for <code>find and find_by_sql here. Find is useful for almost everything you want; but in some cases, you need to use find_by_sql
Tags: activerecord, framework, introductory, sql Posted in
(No Ratings Yet)
Loading ...
Leave a Reply