Prev | Current Page 319 | Next

David Berube

"Practical Reporting with Ruby and Rails"

You should
see a screen similar to Figure 11-1.
Figure 11-1. News tracker report
Next, let??™s take a look at this example line by line.
Dissecting the Code
First, let??™s take a look at the single method in the controller (Listing 11-3):
CHAPTER 11 n TRACKING THE NEWS WITH GOOGLE NEWS 230
def index
custom_sql = "SELECT published_at_formatted,
count(*) as count
FROM (SELECT DATE_FORMAT(published_at,
'%m-%d-%y')
AS published_at_formatted
FROM stories) AS grouped_table
GROUP
BY published_at_formatted
;"
@stories = Story.find_by_sql(custom_sql)
end
The SQL statement here pulls out each date on which a story was published and how
many stories where published that day. You use a nested query to group by a function
call. You could group by published_at directly, but then you would get each distinct time.
Since you cannot group by the result of a function call directly, you use a subquery to
return a set of rows with the date properly formatted, and then group by the new calculated
field.
The list of stories is plugged into the @stories variable, which is used by the view
(Listing 11-4), as follows:

Our Company In The Media

<%
story_count_max = @stories.

Pages:
307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331

Books for you

David Berube

"Practical Reporting with Ruby and Rails"

Our Company In The Media