미래 지향적인 웹 앱 구축: The Codest의 전문가 팀이 제공하는 인사이트
The Codest가 최첨단 기술로 확장 가능한 대화형 웹 애플리케이션을 제작하고 모든 플랫폼에서 원활한 사용자 경험을 제공하는 데 탁월한 성능을 발휘하는 방법을 알아보세요. Adobe의 전문성이 어떻게 디지털 혁신과 비즈니스를 촉진하는지 알아보세요...
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.
자세히 읽어보세요:
– 새로운 현실을 맞이할 시간입니다. 원격 근무의 시대가 한 달 전부터 시작되었습니다.
– Need to use the common JS frameworks in your Rails app? Stimulus.js may be an alternative