CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-5
DNorthrup
DNorthrup

Reputation: 847

Rails 5 - Query association for name of 'parent' and count of 'child'

Models

Users have clients, which have projects which have tasks.

I'm trying to build an array with [Name, Count] to send to a Google Chart - I have all of them but one resolved:

def task_by_project(user)
    task_arr = user.projects.joins(:tasks).where("tasks.completed = ?", "0").uniq.collect { |d| [d.name, d.tasks.count] }.to_a
    task_arr.unshift(['Projects', 'Uncompleted Tasks'])
    return task_arr
end

This query is returning 11 entries. Which is actually -all- of the entries for this user. It's bypassing the where completely. There is 7 uncompleted 4 completed.

Within the view I am rendering it slightly different with a loop: 

    <% current_user.projects.uniq.each do |project| %>
      <%= link_to project.name + " || " + project.tasks.where(completed: 0).count.to_s + " tasks remaining currently", project, :class => "collection-item" %>
    <% end %>  
  </div>

I'm fairly confident it has to do with my misunderstanding of how to query an association this deep.

The END RESULT is I need Project's Name, and the Count of how many Tasks it has.

I do this via query by joining Task back to Project on Project.ID - But I'm not sure how to do that with an entire collection.

(The purpose of the 'unshift' is because other-wise I end up with a result format of [[['Projects', 'Tasks'], [['First Record, 2] ... ]] which isn't GCharts format. Flatter killed it off -too- much)

Upvotes: 0

Views: 1083

Answers (2)

kaspernj
kaspernj

Reputation: 1263

Maybe something like this?

projects = user.projects
  .select("projects.name, COUNT(uncompleted_tasks.id) AS uncompleted_tasks_count")
  .joins("LEFT OUTER JOIN tasks AS uncompleted_tasks ON uncompleted_tasks.project_id = projects.id AND uncompleted_tasks.completed = 0")
  .group("projects.id")
  .map { |project| [project.name, project.uncompleted_tasks_count] }

Upvotes: 1

max
max

Reputation: 102045

Use .pluck to get the column values instead

user.projects.left_joins(:tasks).where("tasks.completed = 0")
    .distinct
    .pluck('users.id, users.name, tasks.count')
    .map { |a| a.pop(2) }

.pluck is far more efficient as it just queries the columns passed to pluck and returns an array of arrays instead of loading a bunch of models into memory.

 # => [[1, 'Frank', 0], [2, 'Muhammad', 5], [3, 'Jane', 3]]

However unless your name column is unique you should select the user id to weed out duplicates. .map { |a| a.pop(2) } gets rid of the id in the resulting arrays.

Pulling records out of the database and calling .uniq on them is a huge antipattern. You're pulling tons of data out of the database and then sorting out the duplicates in Ruby. This can lead to severe performance issues.

Also a better way to handle states is by using a ActiveRecord::Enum:

class Task
  enum :status, [:incomplete, :complete, :deleted]
end

Which will let you query:

Task.incomplete
Task.completed

And gives you interogration and mutation methods:

task.complete?
task.complete!

You can use it like so:

user.projects.left_joins(:tasks).where(tasks: Task.incomplete)
    .distinct
    .pluck('users.id, users.name, tasks.count')
    .map { |a| a.pop(2) }

Upvotes: 0

Related Questions