In this article, you'll create a graphical report from Google News RSS data, using a handy utility called FeedTools and a plug-in called CSS Graphs Helper. This article is excerpted from chapter 11 of the book Practical Reporting with Ruby and Rails, written by David Berube (Apress; ISBN: 1590599330).
Tracking the News with Google News - Dissecting the Code (Page 5 of 5 )
First, let's take a look at the single method in the controller (Listing 11-3):
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:
The first two lines of code in the view prepare the data for the third line of code. The complex_bar_graph method expects an array whose elements are each two-element arrays. These two-element arrays consist of a label element followed by a value element. The value element is expected to be in the range of 0 to 100, so you first calculate the maximum count value. Then you use Ruby's built-in map method to transform the array of Story objects into an array of two-element arrays--what complex_bar_graph wants as an argument.
Note Why isn't the data in Listing 11-4 in the controller instead of the view? The reason is that it's view-specific. If you were using, say, the Open Flash Chart component (introduced in Chapter 5), you would need the data in a different format. By separating the data from the presentation, as you've done here, you can change the way the data is graphed without changing the controller.
In this example, you use Ruby's built-in max method, which is part of Enumerable, to find the maximum count. You can use this method on hashes, arrays, or any other vaguely list-like structure. It might be faster to make an additional query in the database, but since you likely have only a few records, you use the Ruby built-in method instead. Generally, you should have database calls only in the controller, but you need to find the maximum input value because of a quirk in the complex_bar_graph method, which is a concern of the view only.
The call to max compares each of the element's count fields, but you convert them to an integer before comparing them. This is because Active Record doesn't automatically detect that the field is a string, so it compares them as strings unless you explicitly convert them.
Google News is a great news aggregator, which can be used to tap into a worldwide array of news sources. Fortunately, as you've seen in this chapter, FeedTools makes accessing it easy, and CSS Graphs Helper is a great way to present that and other data quickly and easily.
The next chapter shows how you can take data from a web application and make it easily accessible on a desktop machine with Windows and Microsoft Office. This way, Windows users can access data using familiar tools.
DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.