Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Bug: Cannot access Learning Hours Report for volunteers as Supervisor #5979

Open
compwron opened this issue Aug 12, 2024 · 6 comments · May be fixed by #6066
Open

Bug: Cannot access Learning Hours Report for volunteers as Supervisor #5979

compwron opened this issue Aug 12, 2024 · 6 comments · May be fixed by #6066
Labels
Help Wanted This is a special label that enables github to showcase issues that want help Type: Bug

Comments

@compwron
Copy link
Collaborator

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 (

@compwron compwron added Type: Bug Help Wanted This is a special label that enables github to showcase issues that want help labels Aug 12, 2024
@FireLemons FireLemons changed the title Bug: Bug: Cannot access Learning Hours Report for volunteers as Supervisor Aug 21, 2024
@panacotar
Copy link
Contributor

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
Copy link
Collaborator

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
Copy link

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
Copy link
Collaborator

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
Copy link

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
Copy link
Collaborator

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

<% @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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Help Wanted This is a special label that enables github to showcase issues that want help Type: Bug
Projects
Development

Successfully merging a pull request may close this issue.

4 participants