For simple statistics, we can easily write ActiveRecord queries and utilise the data. For more complicated ones, we will need to use SQL.
In my current project, I work with many nonstandard calculations and present results in HTML tables. For simple statistics, we can easily write ActiveRecord queries and utilise the data. For more complicated ones, we will need to use SQL.
I had a few problems with complicated query results:
Read about solution which solves all problems above.
Consider the query:
Use FILTER function if you have postgresql 9.4 it is faster.
The query above may be written as a query on an Order model but, as a result, we will have instances of Order which don’t make any sense.
Let’s make a query object from it:
The result will look like this:
Using these results in view will not look good.
How can we iterate over the query result without using brackets all the time?
Let's create a model where we can do further calculations and make presenting them in view much cleaner. I will use the Virtus gem to map columns into attributes with automatic type mapping and default values.
We declared some attributes which will keep data from the query columns. The private
query_results class method will just execute the query and return results. Finally we made the
self.all class method which will iterate over query results and map every row as a Stats instance. Everything else is handled by Virtus.
Now we can easily use this model in controller like this:
order_stats view we can list all objects with calculated totals like this:
Much cleaner isn’t it?
If you need to format numbers, dates etc you can use presenters, decorators or whatever you want. You can treat it as another model in your application.
Feel free to ask or comment if something is unclear or you see some possible improvements. I will happily improve it.
In my next article I will extend this model with the results of a completely unrelated query to present more statistics on the same view in one html table.
Sometimes you need to save a SQL query or query parameters for the future use. We need that when a user wants to be updated from time to time about latest filtered changes. For example with a weekly newsletter about all adverts for Honda cars produced between 2000 and 2004. But how to save it and use it every week? Read: Persistent queries in Ruby on Rails with PostgreSQL for a solution!