将来を見据えたウェブ・アプリケーションの構築:The Codestのエキスパート・チームによる洞察
The Codestが、最先端技術を駆使してスケーラブルでインタラクティブなウェブアプリケーションを作成し、あらゆるプラットフォームでシームレスなユーザー体験を提供することにどのように秀でているかをご覧ください。The Codestの専門知識がどのようにデジタルトランスフォーメーションとビジネス...
Performance is one of most important aspects to take into account when developing web applications. Analyzing how data is fetched from a database is a good starting point when looking for enhancements. In this article, you’ll find examples on how to improve the performance by using aggregate functions and filtering data on a database level.
This article is inspired by a real problem I’ve once been tasked with. Dealing with it taught me a lot, and I still have it as a reference in my mind. I think examples are a good learning resource, they can clarify a lot. In this article, I’d like to share with you some examples of using the Active Record query methods.
In order not to introduce domain-specific details, I’ll use a sample application for a library to illustrate examples. It’s all fairly simple, as shown in the diagram below. We have four tables: authors, books, users and rentals. One user can borrow many books and one book can be borrowed by many users, so we need a joining table to model many-to-many relationships. It’s the rentals table in our case. We also store some additional info there, which are the dates of borrowing and return. An author can have many books assigned to their name. A book also has an attribute defining its genre.
The task was to prepare statistics for a single user, so we could tell how many books from each genre were borrowed. My first thought was to fetch all books which have been borrowed by the user, group them by their genre and then do the mapping, so each genre has a number of books assigned instead of a list. Here is what I came up with:
Hash[Book.joins(:rentals).where(rentals: { user: user }).group_by(&:genre).map { |genre, books| [genre, books.size] }]
While this approach works and looks clean, it doesn’t use all the possibilities that Active Record query methods offer. Thanks to them, we’re able to filter and aggregate data on a database level without using raw SQL directly in our コード. Operating on a db level also increases our efficiency.
In the example above, we can use the group method instead of the Ruby’s groupby method. It will apply tthe GROUPBY clause to the tSQL query. Furthermore, the mapping and size method can be replaced with a count aggregation function. In the end, we are left with a query that looks like this:
Book.joins(:rentals).where(rentals: { user: user }).group(:genre).count(:books)
It looks even simpler!
Below you’ll find some other ways of using the query methods which I find worth knowing.
TASK: Filter users who have never borrowed a book or did it more than a year ago.
We could fetch all users by including the associated rentals and then filter them using the select method.
User.includes(:rentals).select do |user|
user.rentals.empty? || user.rentals.none? { |rental| rental.start_date >= Date.today - 1.year }
end
But, of course, there is no need to fetch everything. By using the query methods, we can filter it out on the db level. First, let’s select users who have borrowed some books in the last year and then exclude them from the final selection.
This is how the subquery will look like:
User.joins(:rentals).where(rentals: { start_date: (Date.today - 1.year).Date.today })
And now, let’s put it all together:
User.where.not(id: [User.joins(:rentals).where(rentals: { start_date: (Date.today - 1.year)..Date.today })])
TASK: Get authors with one or zero borrowed books
Doing it with the select method is super simple, but again – there is no need to operate on such a big set of data as the db can filter it for us:
Author.includes(:books).select { |author| author.books.size <= 1 }
Then, filtering authors with zero books assigned is quick and easy:
Author.left_joins(:books).where(books: { id: nil })
It’s important to remember one thing while using the left_joins (and outer joins in general). If there are records in the left table (here: authors) that don’t have corresponding records in the right table (here: books), then in result the right table columns will be populated with nil values.
As we also need authors with one book assigned in the system, there are a few more operations to do. We’ll have to do the grouping, counting and adding a condition. Here is how to put it all together:
Author.left_joins(:books).group(:id).having("count(*) <= ?", 1)
The condition comes after the aggregation function, so we have to use the HAVING clause, instead of the WHERE clause to specify it.
The Active Record query methods are worth checking when thinking about application performance. They can simplify your code and make it work faster. I hope that the shared examples will help you in exploring the possibilities the query methods have to offer.
続きを読む
– Railsアプリで一般的なJSフレームワークを使用する必要がありますか?Stimulus.jsが代替になるかもしれません。