SQL Attributes
This gem is here to help you with fetching record specific data from the database using subqueries. You could also use this gem as an alternative for counter caches but of course you should think about this carefully since it can be read heavy.
Installation
Add this line to your application's Gemfile:
gem 'sql_attributes'
The of course run bundle install
.
Usage
It starts by defining an SQL attribute on a class:
class Author < ApplicationRecord
sql_attribute :books_count, <<~SQL
SELECT COUNT(*)
FROM books
WHERE books.author_id = books.id
SQL
sql_attribute :total_pages, <<~SQL
SELECT SUM(books.pages)
FROM books
WHERE books.author_id = books.id
SQL
# Note that this aggregation method `GROUP_CONCAT` is different for other databases like Postgres
sql_attribute :publisher_names, <<~SQL
SELECT DISTINCT GROUP_CONCAT(publishers.name, ' - ')
FROM publishers
INNER JOIN books ON books.publisher_id = publishers.id
WHERE books.author_id = authors.id
GROUP BY books.author_id
SQL
end
Before you can access the attribute, you have to include it to the SQL query. An error will be raised if you dont:
authors = Author.all
authors.map(&:books_count) # => raises SqlAttributes::NotLoaded
authors.map(&:total_pages) # => raises SqlAttributes::NotLoaded
You can tell ActiveRecord / Arel to include a specific attribute by using the with_<NAME>
helpers:
authors = Author.with_books_count.all
authors.map(&:books_count) # => [1, 2]
authors.map(&:total_pages) # => raises SqlAttributes::NotLoaded
These methods are chainable and can be combined with normal scopes:
authors = Author.where(publisher_id: 42).with_books_count.with_total_pages.all
authors.map(&:books_count) # => [1, 2]
authors.map(&:total_pages) # => [300, 500]
You can also load the attributes with the with_sql_attributes
helper:
authors = Author.with_sql_attributes(:books_count, :publisher_names)
authors.map(&:books_count) # => [1, 2]
authors.map(&:total_pages) # => raises SqlAttributes::NotLoaded
If you don't pass any argument, it will load all SQL attributes:
authors = Author.with_sql_attributes
authors.map(&:books_count) # => [1, 2]
authors.map(&:total_pages) # => [300, 500]
Releasing new version
Publishing a new version is handled by the publish workflow. This workflow publishes a GitHub release to rubygems and GitHub package registry with the version defined in the release.
Contributing
Bug reports and pull requests are welcome on GitHub at https://github.com/Drieam/sql_attributes.
License
The gem is available as open source under the terms of the MIT License.
Credits
A big inspration is this blog about The SQL Alternative To Counter Caches
.
The most important example from this blog:
class Author < ApplicationRecord
scope :with_counts, -> {
select <<~SQL
authors.*,
(
SELECT COUNT(books.id) FROM books
WHERE author_id = authors.id
) AS books_count
SQL
}
end
With this gem, this can be rewritten to
class Author < ApplicationRecord
sql_attribute :books_count, <<~SQL
SELECT COUNT(books.id)
FROM books
WHERE author_id = authors.id
SQL
end
Also some ideas where 'stolen' from this blog about Dynamic ActiveRecord columns
.