Count number of associations with a status in Ruby on Rails

rails count associated records
activerecord count where
rails group by
rails where multiple values
rails joins
activerecord count group by
rails pluck
rails select count

I have a model named Project and Project has many Tasks

Task can have 3 different status(integer).

I want to get a list of Projects with counts of associated Tasks in status = 1, 2 and 3.

The best i can get to is have a method on Project

def open_tasks
  self.tasks.where(:status => 1).count
end

But this will make another SQL for each count and it is very bad performance when loading 100 projects.

Is there a way to get it out in one SQL statement?

I can think of a couple of ways to do this...

  1. (It's not a single sql statement but two, still quite performant though)... Task.where(status: 1).group(:project_id).count will give you a hash where the keys are project ids and the values are the task counts. You can then combine this with the list of projects.

  2. You can use the ActiveRecord counter_cache to save in the project records a value for the number of open tasks. ActiveRecord will automatically update this for you. I believe you will need to add an association to the project model like this:

# app/models/project.rb
# needs to include a column called open_task_count
class Project < ActiveRecord::Base
  has_many :open_tasks, class_name: Task, -> { where status: 1 }
end

class Task < ActiveRecord::Base
  belongs_to :project, counter_cache: true
end

count (ActiveRecord::Calculations::ClassMethods), Ruby on Rails latest stable (v5.2.3) - 0 notes - Class: When using named associations, count returns the number of DISTINCT items for the model you're� To learn more about the different types of associations, read the next section of this guide. That’s followed by some tips and tricks for working with associations, and then by a complete reference to the methods and options for associations in Rails. 2 The Types of Associations. In Rails, an association is a connection between two Active

Project.select(
  'projects.*',
  '(SELECT COUNT(tasks.*) FROM tasks WHERE tasks.project_id = projects.id AND tasks.status = 0) AS status_0_count',
  '(SELECT COUNT(tasks.*) FROM tasks WHERE tasks.project_id = projects.id AND tasks.status = 1) AS status_1_count'
).left_joins(:tasks)

Although there are more elegant ways (like lateral joins and CTEs) subqueries work on most DBs. If statuses is an ActiveRecord::Enum you can construct the subqueries by looping over the enum mapping:

class Project < ApplicationRecord
  has_many :tasks
  def self.with_task_counts
    # constucts an array of SQL strings
    statuses = Task.statuses.map do |key, int|
      sql = Task.select('COUNT(*)')
             .where('tasks.project_id = projects.id')
             .where(status: key)
             .to_sql
      "(#{sql}) AS #{key}_tasks_count"
    end
    select(
      'projects.*',
      *statuses # * turns the array into a list of args
    ).left_joins(:tasks)
  end
end

In Rails 4 you can still do a LEFT OUTER JOIN by using a SQL string:

class Project
  def self.left_joins_tasks(*args)
    deprecator = ActiveSupport::Deprecation.new("5.0", "MyApp")
    deprecator.deprecation_warning("left_joins_tasks is deprecated, use `.left_joins(:tasks)` instead")
    joins('LEFT OUTER JOIN tasks ON tasks.project_id = projects.id')
  end
end

Using .joins works as well but gives an INNER join so rows with no tasks are filtered out. You can also use .includes.

ActiveRecord::Calculations, How to use eager loading to reduce the number of database queries needed for data retrieval. How to use SELECT COUNT (*) AS count_all, status AS status. Both Model.all.size and Model.all.count generate a count query in Rails 4 and above. The real advantage of size is that it doesn't generate the count query if the association is already loaded. In Rails 3 and below, I believe Model.all is not a relation, hence all the records are already loaded. This answer might be out of date and I suggest

I ended up using the counter_culture gem.

https://github.com/magnusvk/counter_culture

Active Record Query Interface — Ruby on Rails Guides, Model.first returns nil if no matching record is found. Now what if that number could vary, say as an argument from somewhere, or perhaps from the user's level status Active Record lets you eager load any number of associations with a single This section uses count as an example method in this preamble, but the � How to Start Using Counter Caches in Rails. BY Dan Moore. April 15, 2020. It is widespread to have parent-child associations in Rails applications. On the parent side is a :has_many association, and on the child side is a :belongs_to association.

Active Record Query Interface, Make sure that the grouped field is also selected. Grouping is especially useful for counting the occurrence - in this case - of categories . Product.select(:category ). ruby-on-rails documentation: .group and .count. Example. We have a Product model and we want to group them by their category.. Product.select(:category).group(:category) This will query the database as follows:

Ruby on Rails, Many Rails developers don't understand what causes ActiveRecord to actually execute a SQL query. Let's look at three common cases: misuse of the count method loaded record array. That's just a simple Ruby method on Array. This also happens when you call scopes on associations. Imagine� MVC - Understanding Model in Ruby on Rails. Creating simple model and class instance method - Duration: 15:53. PeopleCanCode 26,925 views

3 ActiveRecord Mistakes That Slow Down Rails Apps: Count, Where , Group by a foreign key, count and sort by :count in 5.2.2.1 and 5.2.3 gemfile( true) do source "https://rubygems.org" git_source(:github) { |repo| do |t| t.integer :group_id t.integer :count, default: 0 end create_table :groups kamipo added a commit to kamipo/rails that referenced this issue on Jun 17, 2019. Pick the value(s) from the named column(s) in the current relation. This is short-hand for relation.limit(1).pluck(*column_names).first, and is primarily useful when you have a relation that's already narrowed down to a single row.

Comments
  • #1 gives a count of all the tasks not counts per status. #2 would update a single counter cache column and also just give a total count.
  • did you update your question? Or perhaps I didn't read it fully. Anyway, you can get the counts of tasks per project and per status value with Task.group(:project_id, :status).count
  • Forgot to say using rails 4. left_joins came in 5 i belive
  • @Jepzen yeah in Rails 4 you need to use .joins with a string to get the same result. blog.bigbinary.com/2016/03/24/…
  • If you are on PG you can use filter instead of a subquery. stackoverflow.com/questions/59327440/…