Advanced ActiveRecord Querying - Aggregations

Exercise Questions
1

This topic is for the Aggregations exercise in the Advanced ActiveRecord Querying trail. Post any questions, comments, or pointers you have to share with other Upcase subscribers.

4

I’m finding this exercise confusing to follow. Looking at the provided test:

  [
    [50_000, 10_000],
    [50_000, -5_000],
    [45_000, 20_000]
  ].each do |(salary, spread)|
    manager = create(:person, salary: salary, name: "spread-#{spread}")
    create(:person, manager: manager, salary: salary - spread * 1)
    create(:person, manager: manager, salary: salary - spread * 2)
    create(:person, manager: manager, salary: salary - spread * 3)
  end

I see this should result in 3 managers, each with 3 employees each.

But it looks like the final employee will have a negative salary? (45000 - 3 * 20000)

5

@christoomey can you confirm if this ‘featured solution’ is correct, as it contains a commented-out line:

https://exercises.upcase.com/exercises/aggregations/solutions/rbetina

6

It’s not correct with the order line commented out. The test passes, but only accidentally.

7

I don’t think it’s a problem that an employee has a negative salary. True, that’s unlikely in the real world, but that probably doesn’t matter for this spec. It is a problem that you can make this spec pass simply by ordering managers by their salaries and completely ignoring employee salaries.

Also, it’s unclear to me how this data should be ordered:

manager1: $10
employee1a: $8
employee1b: $3

manager2: $10
employee2a: $6
employee2b: $4

manager3: $10
employee3a: $9
employee3b: $2

8

Can anyone explain why the following code is not working for 1st spec ?

def self.maximum_salary_by_location
  joins(:location).group("location.name").maximum(:salary)
end
9

@rakeshsukla53, it should be group("locations.name")

10

@rakeshsukla53 @JalenOng Person#maximum_salary_by_location shouldn’t return groups based on a location’s name. Double-check your spec to ensure you’re grouping salaries by the desired property.

11

Hello,

I’m a little late to the party and not sure if anyone uses this forums.

But I’m trying to complete this exercise using custom queries as suggested by the Video.
Here is my code for solving the first part, highest paying job by location.

  def self.maximum_salary_by_location
    Location.
      joins(
      "INNER JOIN (" +
      Person.
      select("location_id as loc_id, max(salary) as max_salary").
      group("location_id").
      to_sql +
      ") salaries " \
      "ON salaries.loc_id = locations.id"
    ).tap { |o| p o.to_sql }
  end

I’m getting this from the method:
#<ActiveRecord::Relation [#<Location id: 1, name: "highest-50000", region_id: 1>, #<Location id: 2, name: "highest-60000", region_id: 2>]>

this is the Generated query:

"SELECT \"locations\".* FROM \"locations\" INNER JOIN (SELECT location_id as loc_id, max(salary) as max_salary FROM \"people\" GROUP BY \"people\".\"location_id\") salaries ON salaries.loc_id = locations.id"

It is not including the max_salaries field form the INNER JOIN

If anyone can give me a pointer to the right direction it will be much appreciated.

12

Never mind, I was overcomplicating it I woke up fresh today and find the solution just by joining on locations id and getting the max on that join.

    joins(:location).
      group("locations.id").
      maximum(:salary)