**Course Title:** Mastering Ruby on Rails: Building Scalable Web Applications **Section Title:** Advanced Active Record and Querying **Topic:** Working with Complex Database Queries and Aggregations **Introduction** In this topic, we will delve into the world of complex database queries and aggregations in Ruby on Rails. We will explore how to use Active Record's powerful querying capabilities to fetch data from your database, perform aggregations, and even implement soft deletes and versioning in your models. **Understanding Querying in Active Record** Active Record provides a simple and intuitive way to query your database using the `where`, `where_in`, `where_not`, `order`, `limit`, `joins`, and `includes` methods. However, as your database grows in complexity, you may need to perform more advanced queries to fetch the data you need. **Aggregations** Aggregations are used to perform calculations on a set of data, such as summing up the total value of a column or counting the number of records that match a certain condition. In Rails, you can use the `group` and `sum` methods to perform aggregations. ```ruby # models/user.rb class User < ApplicationRecord def self.total_earnings where(status: 'active').sum(:earnings) end end ``` In this example, we define a class method `total_earnings` that fetches all active users and sums up their earnings. **Joins** Joins are used to combine data from multiple tables in your database. In Rails, you can use the `joins` method to perform joins. ```ruby # models/user.rb class User < ApplicationRecord has_many :orders, join_table: 'users_orders' end # models/order.rb class Order < ApplicationRecord belongs_to :user end ``` In this example, we define a has_many relationship between the `User` and `Order` models, and use the `joins` method to perform a join between the two tables. **Subqueries** Subqueries are used to nest one query inside another. In Rails, you can use the `where` method with a subquery to perform a subquery. ```ruby # models/user.rb class User < ApplicationRecord def self.users_with_high_earnings where(earnings: (select('MAX(earnings)').from('users').where(status: 'active'))) end end ``` In this example, we define a class method `users_with_high_earnings` that fetches all users with earnings higher than the maximum earnings of all active users. **Soft Deletes** Soft deletes are used to mark records as deleted without actually removing them from the database. In Rails, you can use the `destroy` method with the `:soft_delete` option to perform a soft delete. ```ruby # models/user.rb class User < ApplicationRecord def self.destroy_user(user) user.destroy(:soft_delete => true) end end ``` In this example, we define a class method `destroy_user` that soft deletes a user record. **Versioning** Versioning is used to track changes to a record over time. In Rails, you can use the `update_column` method with the `:version` option to perform a version update. ```ruby # models/user.rb class User < ApplicationRecord def self.update_user(user) user.update_column(:version, user.version + 1) end end ``` In this example, we define a class method `update_user` that increments the version number of a user record. **Conclusion** In this topic, we covered the basics of complex database queries and aggregations in Ruby on Rails. We explored how to use Active Record's powerful querying capabilities to fetch data from your database, perform aggregations, and even implement soft deletes and versioning in your models. **Practical Takeaways** * Use the `group` and `sum` methods to perform aggregations. * Use the `joins` method to combine data from multiple tables. * Use the `where` method with a subquery to perform a subquery. * Use the `destroy` method with the `:soft_delete` option to perform a soft delete. * Use the `update_column` method with the `:version` option to perform a version update. **Additional Resources** * [Active Record Querying Documentation](https://guides.rubyonrails.org/active_record_querying.html) * [Rails Guides: Querying](https://guides.rubyonrails.org/active_record_querying.html) * [Rails Guides: Aggregations](https://guides.rubyonrails.org/active_record_querying.html#aggregations) **Leave a Comment or Ask for Help** If you have any questions or need further clarification on any of the topics covered in this topic, please leave a comment below.