← Back to Upcase

Advanced ActiveRecord Querying - Aggregations


(Upcase ) #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.


(Andy Waite) #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)


(Andy Waite) #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


(turino) #6

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


(turino) #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


(Rakesh Sukla) #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


(Jalen Ong) #9

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


(Alex Martin) #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.


(rderik) #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.


(rderik) #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)