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 (

[DariusPirvulescu]

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.