So we have this project in Ruby on Rails where we keep track of several reports. Now we need to do some visualizations with all those reports.
We analyzed several solutions. One was using Google Fusion Tables but we soon realized it was not an easy task: we need to keep our records synchronized with Google. Well, maybe not very hard: have a background process that keeps track of the last date of synchronization and gets all the records whose updated_at date is greater then that, and synchronize. The problem is that some of our records can be deleted, so we would need to mark them as deleted or think of something else, which complicates the main logic of our application. Also our application is pretty simple and complicating it with background processes wasn't our ideal solution.
Then we though about just using the Google Visualization API. It works by sending a query to an endpoint which returns some javascript code that includes, amongst other things, a table to be used in the visualization. The query can be anything that the end-point understands but Google has it's own query language, which is used by Google Spreadsheets, for example.
So we started by making one endpoint that just ignores the query and sends what we needed for one of our visualizations. We basically issued an ActiveRecord query, with some sums, custom selects, groups by and joins, and transformed the results into the necessary javascript code. But then we needed another visualization and we had to write another endpoint with yet another query. Soon we realized it would really be nice to have just one endpoint that supports receiving a query, and much better if that query comes in Google's query language syntax, since people should be familiar with it and we can give the documentation URL to our developers and tell them "This is how you can query our endpoint".
This is how rgviz and rgviz-rails were born. The first one just implements a query language parser. The second one executes queries with ActiveRecord. So if you have a model named Person and you want to provide an endpoint to query Person data, you just need to write this in a method of your controller:
render :rgviz => Person
One of the nicest things about this is that you can also use associations in your query. So for example if a Person belongs to a City, you could write a query like:
select city_name, avg(age) group by city_name
Another nice thing is that the pivot clause is fully supported. And the endpoint will always execute your query in just one SQL query, and an efficient one.