Enterprise Rails

A View to Materialize

First, we need something worth materializing. We’ll start with our view from the previous chapter, but we’ll make it a bit more complex so that we can explore a variety of caching techniques. Example 12-1 shows an extended version of our view, which incorporates the number of seats available in a theatre as seats_available, and the number of tickets purchased thus far as tickets_purchased. Since the purpose of this view is to show movie showtimes for which we can sell tickets, a filter has been added to the where clause to filter out showtimes that are sold out. Additions to our original view are shown in bold.

Example 12-1. A slightly more complex version of our original view from Chapter 11

create or replace view current_movie_showtimes as
  select m.name,
         m.rating_id,
         m.length_minutes,
         ms.*,
         t.name as theatre_name,
         t.zip_code,
         z.latitude,
         z.longitude,
         a.seats_available,
         coalesce(ptc.purchased_tickets_count, 0) as purchased_tickets_count
    from movie_showtimes ms
    join movies m on (ms.movie_id = m.id)
    join theatres t on (ms.theatre_id = t.id)
    join zip_codes z on (t.zip_code = z.zip)
    join auditoriums a on (ms.room = a.room and ms.theatre_id = a.theatre_id)
    left outer join (
  select count(*) as purchased_tickets_count,
         o.movie_showtime_id
    from orders o,
         purchased_tickets pt
   where pt.order_confirmation_code = o.confirmation_code
group by o.movie_showtime_id
        ) ptc on (ptc.movie_showtime_id = ms.id) where (ms.start_time - now()) < '1 week'::interval and ms.start_time ...