All Case Studies Design Development Interviews Our Way Project Management

SQL Query Result Model

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:

  • how to retrieve data which contains columns not related to a model?
  • how to present it on views?
  • how to calculate additional statistics?
  • how to return default values when there are nulls returned by query?

Read about solution which solves all problems above.

Consider the query:

Use FILTER function if you have postgresql 9.4 it is faster.

I’m using the sql_query gem to keep SQL in .sql.erb files. Why did I decide to do that for most complicated queries? You can read about it in my previous post.

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:

If you only need to return a json response, you can finish here and do everything else in javascript. In this case, we need to present the data in view, the old fashioned rails way ;).

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.

What have we done here?

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:

and in 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!

Why it is impossible to hire quality engineers in London and how to deal with it
Scaling SaaS
READ ALSO FROM Dev
Read also
Need a successful project?
Estimate project or contact us