Bug: Cannot access Learning Hours Report for volunteers as Supervisor

[compwron]

Where on the site did this issue occur?
Supervisor dashboard
What isn't working for you in the Supervisor dashboard?
Cannot access Learning Hours Report for volunteers
Can you provide a few more details about that?
When clicking learning hours I could not find any volunteers

Linda (she/her) - CASA and Flaredown
Jul 26th at 11:25 AM
@yuri
any thoughts here?
New

Yuri
Jul 29th at 12:25 PM
I emailed him. He had 1 volunteer and they had no hours entered. It is a bit silly we show only volunteers with hours.

models/learning_hour.rb

scope :supervisor_volunteers_learning_hours, ->(supervisor_id) {
joins(user: :supervisor_volunteer)
.where(supervisor_volunteers: {supervisor_id: supervisor_id})
.select("users.id as user_id, users.display_name, SUM(learning_hours.duration_minutes + learning_hours.duration_hours * 60) AS total_time_spent")
.group("users.display_name, users.id")
.order("users.display_name")
}
pretty sure the fix is just to make that an left outer join (

[panacotar]

I'm scouting for issues to work on.
I tried changing to left outer join, but it is still the same.
learning_hours will only contain volunteers with hours and joining it with supervisor_volunteer will still only show the ones with hours.
Is the idea to show all of them? Should we render all by default and fetch their learning hours?

[elasticspoon]

I'm scouting for issues to work on. I tried changing to left outer join, but it is still the same. learning_hours will only contain volunteers with hours and joining it with supervisor_volunteer will still only show the ones with hours.

Yea you are right that a direct left outer join replacement would not work. But you could rewrite the query a bit to get it => left outer join supervisor_volunteer to volunteer and left out join that to learning hours.

SELECT stuff we need 
FROM supervisor_volunteers 
  LEFT OUTER JOIN "users" ON id probably 
  LEFT OUTER JOIN "learning_hours" ON idk id again? 
WHERE supervisor_volunteers.supervisor_id = id of current supervisor
... rest of the scope logic

Is the idea to show all of them? Should we render all by default and fetch their learning hours?

Yea, the idea is if a volunteer has no learning hours we ought to still show that.

[lenikadali]

I'm scouting for issues to work on. I tried changing to left outer join, but it is still the same. learning_hours will only contain volunteers with hours and joining it with supervisor_volunteer will still only show the ones with hours.

Yea you are right that a direct left outer join replacement would not work. But you could rewrite the query a bit to get it => left outer join supervisor_volunteer to volunteer and left out join that to learning hours.

SELECT stuff we need 
FROM supervisor_volunteers 
  LEFT OUTER JOIN "users" ON id probably 
  LEFT OUTER JOIN "learning_hours" ON idk id again? 
WHERE supervisor_volunteers.supervisor_id = id of current supervisor
... rest of the scope logic

Is the idea to show all of them? Should we render all by default and fetch their learning hours?

Yea, the idea is if a volunteer has no learning hours we ought to still show that.

Hi @compwron

I wanted to take a stab at this but was stumped by how the query would look. Here are the results of attempting the above query in a Rails console:

irb(main):030> SupervisorVolunteer.left_outer_joins(:users).left_outer_joins(:learning_hours).where(supervisor_id: s.id).select("users.id as user_id, users.display_name, SUM(learning_hours.duration_minutes + learning_hours.duration_hours * 60) AS total_time_spent").group("users.display_name, users.id").order("users.display_name")
An error occurred when inspecting the object: #<ActiveRecord::ConfigurationError: Can't join 'SupervisorVolunteer' to association named 'users'; perhaps you misspelled it?>

I used the Rails guide for left_outer_joins here

[elasticspoon]

You are going to want to use the nested syntax: https://guides.rubyonrails.org/active_record_querying.html#joining-nested-associations-multiple-level

SupervisorVolunteer.left_outer_joins(:users).left_outer_joins(:learning_hours) attempts to do something like

FROM supervisor_volunteers 
  LEFT OUTER JOIN "users" ON sup_volunteers.id = users.sup_volunteers_id
  LEFT OUTER JOIN "learning_hours" ON sup_volunteer.id = learning_hours.sup_volunteers_id

But the way the tables connect is sup_volunteers have_many users and users have_many learning_hours. So you need to connect volunteers to users and then users to hours. not volunteers to users AND volunteers to learning_hours.

Hopefully that makes sense.

[lenikadali]

So I experimented a bit further and realised that since we are in the LearningHour model, the query we want is:

casa(dev)>  LearningHour.left_outer_joins(user: [{ supervisor_volunteer: {supervisor: :learning_hours} }])

If we were using SupervisorVolunteer, then we would use the query below:

casa(dev)> SupervisorVolunteer.left_outer_joins(volunteer: [{ supervisor: :learning_hours }])

So I was thinking to modify the scope like so:

   scope :supervisor_volunteers_learning_hours, ->(supervisor_id) {
-    joins(user: :supervisor_volunteer)
+    left_outer_joins(user: [{ supervisor_volunteer: {supervisor: :learning_hours} }])
       .where(supervisor_volunteers: {supervisor_id: supervisor_id})
       .select("users.id as user_id, users.display_name, SUM(learning_hours.duration_minutes + learning_hours.duration_hours * 60) AS total_time_spent")
       .group("users.display_name, users.id")

I think we want the query using SupervisorVolunteer since the one using LearningHour doesn't show volunteers without learning hours while the one using SupervisorVolunteer does.

casa(dev)> SupervisorVolunteer.left_outer_joins(volunteer: [{ supervisor: :learning_hours }]).where(supervisor_volunteers: {supervisor_id: s.id}).select("users.id as user_id, users.display_name, SUM(learning_hours.duration_minutes + learning_hours.duration_hours * 60) AS total_time_spent").group("users.display_name, users.id")
  SupervisorVolunteer Load (1.7ms)  SELECT users.id as user_id, users.display_name, SUM(learning_hours.duration_minutes + learning_hours.duration_hours * 60) AS total_time_spent FROM "supervisor_volunteers" LEFT OUTER JOIN "users" ON "users"."id" = "supervisor_volunteers"."volunteer_id" LEFT OUTER JOIN "supervisor_volunteers" "supervisor_volunteers_users_join" ON "supervisor_volunteers_users_join"."is_active" = $1 AND "supervisor_volunteers_users_join"."volunteer_id" = "users"."id" LEFT OUTER JOIN "users" "supervisors_users" ON "supervisors_users"."id" = "supervisor_volunteers_users_join"."supervisor_id" LEFT OUTER JOIN "learning_hours" ON "learning_hours"."user_id" = "supervisors_users"."id" WHERE "supervisor_volunteers"."supervisor_id" = $2 GROUP BY users.display_name, users.id /* loading for pp */ LIMIT $3  [["is_active", true], ["supervisor_id", 2], ["LIMIT", 11]]
=> 
[#<SupervisorVolunteer:0x000070191f36dac8 user_id: 4, display_name: "Anton Mosciski", total_time_spent: nil, id: nil>,
 #<SupervisorVolunteer:0x000070191f36d988 user_id: 6, display_name: "Emelina Quitzon", total_time_spent: nil, id: nil>,
 #<SupervisorVolunteer:0x000070191f36d848 user_id: 5, display_name: "Owen Gerlach", total_time_spent: nil, id: nil>,
 #<SupervisorVolunteer:0x000070191f36d708 user_id: 8, display_name: "Linsey Durgan", total_time_spent: nil, id: nil>]
casa(dev)> LearningHour.left_outer_joins(user: [{ supervisor_volunteer: {supervisor: :learning_hours} }]).where(supervisor_volunteers: {supervisor_id: s.id}).select("users.id as user_id, users.display_name, SUM(learning_hours.duration_minutes + learning_hours.duration_hours * 60) AS total_time_spent").group("users.display_name, users.id")
  LearningHour Load (1.6ms)  SELECT users.id as user_id, users.display_name, SUM(learning_hours.duration_minutes + learning_hours.duration_hours * 60) AS total_time_spent FROM "learning_hours" LEFT OUTER JOIN "users" ON "users"."id" = "learning_hours"."user_id" LEFT OUTER JOIN "supervisor_volunteers" ON "supervisor_volunteers"."is_active" = $1 AND "supervisor_volunteers"."volunteer_id" = "users"."id" LEFT OUTER JOIN "users" "supervisors_supervisor_volunteers" ON "supervisors_supervisor_volunteers"."id" = "supervisor_volunteers"."supervisor_id" LEFT OUTER JOIN "learning_hours" "learning_hours_users" ON "learning_hours_users"."user_id" = "supervisors_supervisor_volunteers"."id" WHERE "supervisor_volunteers"."supervisor_id" = $2 GROUP BY users.display_name, users.id /* loading for pp */ LIMIT $3  [["is_active", true], ["supervisor_id", 2], ["LIMIT", 11]]
=> 
[#<LearningHour:0x000070191f494d98 user_id: 4, display_name: "Anton Mosciski", total_time_spent: 225, id: nil>,
 #<LearningHour:0x000070191f494c58 user_id: 5, display_name: "Owen Gerlach", total_time_spent: 105, id: nil>,
 #<LearningHour:0x000070191f494b18 user_id: 6, display_name: "Emelina Quitzon", total_time_spent: 90, id: nil>,
 #<LearningHour:0x000070191f4949d8 user_id: 8, display_name: "Linsey Durgan", total_time_spent: 225, id: nil>]

Not sure how to use SupervisorVolunteer in the LearningHour model scope so if you could let me know how to go about that it would be great 😄

[elasticspoon]

I am pretty sure you can just pass the supervisor volunteers instead of learning hours.

casa/app/views/learning_hours/_supervisor_admin_learning_hours.html.erb

Line 26 in 5f16e91

<% @learning_hours.each do |learning_hour| %>

the view code isn't actually looking for a volunteer or anything like that. just an object that responds to display_name, total_time_spent, etc. So I would think passing the supervisor volunteers should just work.

LMK if it doesnt