Some concepts are extremely easy to explain in words, yet difficult to extract from a database. For example, on a ticket-purchasing site, it’s pretty obvious that you need a way to quickly show visitors “current movies in my area.” That is certainly an easy concept, but let’s examine what it takes to get that kind of information out of our database.
“Current” means we need to be looking in the
movie_showtimes table. We might define current
to mean movies starting within a week.
Knowing the showtime isn’t enough. We need to get the movie information, too. A showtime without the movie name isn’t very useful. While we’re at it, we probably need to know the rating and the length of the movie as well.
The request “in my area” means we need to know where the visitor is
and where the movie theatre is. We can use the PL/pgSQL distance procedure
miles_between_lat_long that we wrote in Chapter 6, but to do so we need data
theatres table (the zip code)
and from the
zip_codes table (the
latitude and longitude).
This represents a query with four tables:
zip_codes. We could write
an ActiveRecord query that could get us what we want:
MovieShowtime.find(:all, :include => [:movie, :theatre], :conditions => " movie_showtimes.start_time - now() < '1 week'::interval and movie_showtimes.start_time > now() " )
The first problem we notice, as far as ActiveRecord die-hards are concerned, is that we’ve got some SQL peaking through ...