CODEWITHSUNDEEP
ruby-on-rails
Jelle
Jelle

Reputation: 339

newbie: performing queries on collection of results

I have two models: projects and todos. In the projects index, I want to show an overview of projects, consisting of the project name and the number of todo items in the project with status "do", "doing", or "done". (eg: do:12 | doing:2 | done:25 ). In my projects controller, i can retrieve all projects, but i additionally need to find out how many todo items with each status are involved in each project. I have fixed this by defining additional database queries in the project index view:

Todo.where("project_id = ?", project.id).where("status = ?", "done").count)

This does not seem the right (MVC) way to solve this. What would be a better way? How can i perform additional queries on subsets of a collection of results.

I have tried to include all relevant code below:

class Project < ActiveRecord::Base
has_many :todos,  dependent: :destroy
end


class Todo < ActiveRecord::Base
acts_as_list
belongs_to :project
end

The schema for the models are:

create_table "projects", force: true do |t|
   t.string   "name"
   t.datetime "created_at"
   t.datetime "updated_at"
 end

 create_table "todos", force: true do |t|
   t.string   "name"
   t.string   "description"
   t.string   "status"
   t.datetime "created_at"
   t.datetime "updated_at"
   t.integer  "position"
   t.integer  "project_id"
  end

the projects controller:

class ProjectsController < ApplicationController
  before_action :set_project, only: [:show, :edit, :update, :destroy]

  def index
   @projects = Project.all

  end

Upvotes: 0

Views: 53

Answers (3)

Slawek
Slawek

Reputation: 583

I would rather use additional columns for counters.

create_table "projects", force: true do |t|
   t.string   "name"
   t.datetime "created_at"
   t.datetime "updated_at"
   t.integer "doing_counter"
   t.integer "done_counter"
 end

After that I would use callbacks, after_save and after_destroy on Todo model

class Todo < ActiveRecord::Base
  acts_as_list
  belongs_to :project
  after_save :update_counters
  after_destroy :update_counters
  def update_counters
       self.project.update_attribute(:doing_counter, self.project.todos.where('status=?', 'doing').count)
       self.project.update_attribute(:done_counter, self.project.todos.where('status=?', 'done').count)
  end
end

== performance tunning

class Todo < ActiveRecord::Base
  acts_as_list
  belongs_to :project
  after_create :update_counters
  after_update :update_counters_if_changed
  after_destroy :update_counters

  def update_counters_if_changed
       update_counters if status_changed?
  end

  def update_counters
       self.project.update_attribute(:doing_counter, self.project.todos.where('status=?', 'doing').count)
       self.project.update_attribute(:done_counter, self.project.todos.where('status=?', 'done').count)
  end
end

Upvotes: 1

techvineet
techvineet

Reputation: 5111

The cleaner way would be to make a scopes

class Todo < ActiveRecord::Base
acts_as_list
belongs_to :project
scope :do, -> { where(status: 'do') }
scope :doing, -> { where(status: 'doing') }
scope :done, -> { where(status: 'done') }
end

and from Project

project.todos.do.count
project.todos.doing.count...

Upvotes: 1

dax
dax

Reputation: 10997

you could try something like this in your project model

def todo_count(type)
  #get todos of a given status
  todo = self.todos.where("status = ?", type.to_s)
  #count them
  todo.count
end

and call it in your view like this:

<%= @project.todo_count(do) %> #to get the count of `do` items

Upvotes: 0

Related Questions