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