← Back to Upcase

Advanced ActiveRecord Querying has_many-associations Excercise


(Ankur Patel) #1

Hi I was working on fixing the last spec of the querying-has_many-associations repo for finding managers who have employees and sorting by their location’s name.

This is the error I ran into when trying to run the query PG::InvalidColumnReference: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

The query it is trying to run is as following:

SELECT DISTINCT "people".* FROM "people"
INNER JOIN "locations" ON "locations"."id" = "people"."location_id"
INNER JOIN "people" "employees_people" ON "employees_people"."manager_id" = "people"."id"
ORDER BY "locations"."name" ASC, "people"."name" ASC

The query seems right to be but not sure why it cant do distinct on people and it seems I need to also select location.name in order for this query to work. Below is the source code as to how I am trying to solve this exercise. Any help is appreciated.

class Person < ActiveRecord::Base
  belongs_to :location
  belongs_to :role
  belongs_to :manager, class_name: "Person", foreign_key: :manager_id
  has_many :employees, class_name: "Person", foreign_key: :manager_id

  def self.order_by_location_name
    joins(:location)
      .merge(Location.order(:name))
      .order(:name)
  end

  def self.with_employees
    joins(:employees)
      .order(:name)
      .distinct
  end

  def self.with_employees_order_by_location_name
    order_by_location_name
      .merge(with_employees)
  end
end

(Ankur Patel) #2

Actually never mind I solved it by looking at another ActiveRecord video and I had to use a subquery to solve it.