Advanced Querying with Active Record

Some tips on using Active Record in an Advanced Way.

Joining an association of an association

User belongs_to Location Location belongs_to Region

User.joins(location: :region)

this can become a method within user.rb

def self.with_regions
  joins(location: :region)
end

then it can be combined to do some complex ordering…

def self.order_alphabetically
  with_regions.order("regions.name, locations.name, name")
end

DISTINCT PROBLEMS WHEN QUERYING A HAS_MANY RELATIONSHIP

Damn, this is what I needed long ago. Ordering on a has_many query, PG ALWAYS PUKES. Until now…

Location.by_region_and_location_name
  Location Load (2.1ms)  SELECT "locations".* FROM "locations" INNER JOIN "regions" ON "regions"."id" = "locations"."region_id"  ORDER BY "regions"."name" ASC, "locations"."name" ASC

2.3.0 :014 > Location.billable
  Location Load (1.3ms)  SELECT DISTINCT "locations".* FROM "locations" INNER JOIN "people" ON "people"."location_id" = "locations"."id" INNER JOIN "roles" ON "roles"."id" = "people"."role_id" WHERE "roles"."billable" = 't'

Both these queries work… combine them and they fail.

2.3.0 :015 > Location.billable.by_region_and_location_name
  Location Load (6.5ms)  SELECT DISTINCT "locations".* FROM "locations" INNER JOIN "people" ON "people"."location_id" = "locations"."id" INNER JOIN "roles" ON "roles"."id" = "people"."role_id" INNER JOIN "regions" ON "re
  gions"."id" = "locations"."region_id" WHERE "roles"."billable" = 't'  ORDER BY "regions"."name" ASC, "locations"."name" ASC
  ActiveRecord::StatementInvalid: PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
  LINE 1: ...gion_id" WHERE "roles"."billable" = 't'  ORDER BY "regions"....
                                                               ^
                                                               : SELECT DISTINCT "locations".* FROM "locations" INNER JOIN "people" ON "people"."location_id" = "locations"."id" INNER JOIN "roles" ON "roles"."id" = "people"."role_id" INNER JOIN "regions" ON "regions"."id" = "location
                                                               s"."region_id" WHERE "roles"."billable" = 't'  ORDER BY "regions"."name" ASC, "locations"."name" ASC

But here is the saving grace… from

 Location.from(Location.billable, :locations).by_region_and_location_name

Find the records using .from. It makes the distinct call. This is your starting point. Then sort the results.

 Location Load (1.4ms)  SELECT "locations".* FROM (SELECT DISTINCT "locations".* FROM "locations" INNER JOIN "people" ON "people"."location_id" = "locations"."id" INNER JOIN "roles" ON "roles"."id" = "people"."role_id"
 WHERE "roles"."billable" = 't') locations INNER JOIN "regions" ON "regions"."id" = "locations"."region_id"  ORDER BY "regions"."name" ASC, "locations"."name" ASC

Post Content