Rails Framework is one of the greatest supporters for Rapid Application Development (RAD) which tends to abstract and simplify the web architecture so that Rails abstracts away the database through the Active Record which is the Object-relational mapping (ORM) for rails.
The Active Record is the layer of the system responsible for representing business data and logic. Active Record facilitates the creation and use of business objects whose data requires persistent storage to a database.
So, It helps in a way to manage all relations consistency and mapping in the class model without the need to write SQL statement to retrieve any data or the usage of CRUD (create, read, update, and delete) methods in general.
The significant problem
When you start your new web application, you will focus mainly to build the model and to make sure that everything goes well, then you’ll have lots of users and you’ll start collecting and generating lots of data about those users and your application’s operation in general.
After you start having a lot of users and different teams to manage the operations, marketing, and business development. These teams will start asking for more data analysis and reports to be generated. How many new users signed up every day? From which countries? What features, pages are heavily used and which ones are unused? And other sophisticated kinds of reports and analytics. Moreover, your users will start to ask for new features and some analytics may be and reports according to the business application you are building.
The normal method to do that by start developing the model methods to retrieve this data, restructure, and aggregate them or doing another data analysis functions, developing the views to render these results to this result stakeholders. And this can have acceptable response time and performance in the beginning when you have a small number of users, with time and after generating a lot of data, this kind of reports will consume a lot of your production environment resources and you will not be able to use the traditional way of implementation and calculate all things on the fly.
There are many ways in rails to optimize the data retrieval performance, for example, caching, eager loading, custom query execution, …etc. In this article, we will focus on Database views as there is no direct support from Rails Active Record domain-specific language (DSL) to create views like it contains for main entities models. While it is important to mention that, database layer is not the only resources consumer, web application performance can be affected by many reasons. I assume here that you did some analysis and conclude that you have a bottleneck at data retrieval methods.
Most relational databases support the concept of views, you can query a view just like any other database table, but the fields and values in a view are defined based on the query used to create the view. I will not discuss here the definition of the views as I suppose that if you reached this article you are already familiar with it and you have a good background of relational databases in general.
So, what will we do to enhance our reporting and analytics performance using the database views? here are the steps.
Prepare your environment
I tested view creation using Rails 4.1.16, Ruby 2.3.3, and PostgreSQL installed on Apple MAC, also deployed similar algorithm on Heroku cloud and it worked as expected.
Prerequisites and supporting gem
We need to install the scenic gem. You can do that by adding the following line to your gem file.
Scenic is very great gem to use as it handles the versions of views if you need to update them later and it also makes it easier to add the view creation on the schema.rb
You can visit scenic documentation to learn more about it as well.
Generating the View migration
You will do that similarly like any migration, while the syntax will be like the below after adding scenic.
rails g scenic:view Summary
Please remember that Rails is handling the Plural of the name so it will be created as summaries view name at the database layer.
Understanding the command output
After running this command, you will have two files created one for the migration and one for SQL view creation with
The migration file will look like:
class CreateSummaries < ActiveRecord::Migration def change create_view :summaries end end
And SQL file will be like and it will be located under newly created folder called views and DB folder
And this file is the magic which you will use to add your view code.
Adding your SQL query to the SQL file
At this step, you will not need to handle any SQL creation function of the view as scenic will handle that, you will only need to write the SQL query which you need to generate your reports or the data you need to optimize, for example, here as we need to do some summaries for users for example, how many posts they have, how many comments they did, how many followers they wrote.
We will add the simple query to our SQL file
select users_tb.users_id as user_id, comments_count, posts_count, followers_count from ( select users.id as user_id from users order by users.name) as users_tb left join ( select comments_tb.users_id as user_id, comments_count, posts_count, followers_count from ( select user_id, count(id) as comments_count from comments group by user_id order by user_id) as comments_tb on users_tb.user_id = comments_tb.company_id left join ( select user_id, count(id) as posts_count from posts group by user_id order by user_id) as posts_tb on users_tb.user_id = posts_tb.company_id left join ( select user_id, count(id) as followers_count from followers group by user_id order by user_id) as followers_tb on users_tb.user_id = followers_tb.company_id) as counts_tb on users_tb.user_id = counts_tb.user_id
You will need to test your query and ensure that it runs correctly and returns the data you need, you can do that by running rails db at the console and then add your query
Run the migration task
As any migration, you will write the following command to run the migration task
After that, you see that your migration has been added normally and then you can test your view at your database layer by normal command.
select * from summaries;
Integrate the view with the Rails app
At this step, we need to have the same abstraction the rails provided to retrieve the data from the view as well integrate it with our main User Model so it can be easily related to our model.
To do that, we will create the view class, at your model folder add a new file, called summary.rb or your view name according to your implementation and usage
In the model, you will write the class which will do this level of abstraction.
class Summary < ActiveRecord::Base self.primary_key = "user_id" belongs_to :user end
The user_id here is used as reference key with your main User model, which is an important note to consider if you will do some relation with your main model, you may not need that if you will view the aggregation view without getting other data from the main model.
And at your User Model, you will need to add this line as well
class User < ActiveRecord::Base has_one :summary end
Now, if you open your console and run rails c, the type, for example, Summary.all
It will get you all the data at the created view similarly as you do with any other model.
You can now use the database view in your controller or active views as well, for example, you write a simple command like.
This will get you all the summaries with enhanced performance without any need to do the calculations and counting functions every time. You can also do some delegations to call the attributes directly from the main model.
Assume you have this for thousands
Help to do more
The content you read is available for free. If you’ve liked any of the articles at this site, please take a second to help us write more and more articles based on real experiences and maintain them for you and others. Your support will make it possible for us.
of users or millions. What will be the situation then with the traditional way?
I believe that creating database views is a good pattern to be used in Ruby on Rails applications, and this can be used in different aggeration and data calculations and manipulations functions.
I hope that you can try it and let me know what was the effect of using it on your applications.