Includes with references bug.

[akarmes]

Rails - 4.1 Squeel - master branch

Example models:

class EmployeeSchedule < ActiveRecord::Base
  belongs_to :availability_type, class_name: EmployeeAvailabilityType
end

class EmployeeAvailabilityType < ActiveRecord::Base; end

Simple includes with references works just as expected:

EmployeeSchedule.includes(:availability_type).where do
  availability_type.operational == false
end.references(:availability_type)

#DEBUG development>   SQL (53.5ms)  SELECT "employee_schedules"."id" AS t0_r0, "employee_schedules"."employee_id" AS t0_r1, "employee_schedules"."availability_type_id" AS t0_r2, "employee_schedules"."day" AS t0_r3, "employee_schedules"."start_at" AS t0_r4, "employee_schedules"."end_at" AS t0_r5, "employee_schedules"."created_in_division_id" AS t0_r6, "employee_schedules"."created_by_user_id" AS t0_r7, "employee_schedules"."created_by_employee_id" AS t0_r8, "employee_schedules"."created_at" AS t0_r9, "employee_schedules"."updated_at" AS t0_r10, "employee_schedules"."created_in_company_id" AS t0_r11, "employee_schedules"."created_in_branch_id" AS t0_r12, "employee_availability_types"."id" AS t1_r0, "employee_availability_types"."name" AS t1_r1, "employee_availability_types"."code" AS t1_r2, "employee_availability_types"."workday" AS t1_r3, "employee_availability_types"."color" AS t1_r4, "employee_availability_types"."created_in_division_id" AS t1_r5, "employee_availability_types"."created_by_user_id" AS t1_r6, "employee_availability_types"."created_by_employee_id" AS t1_r7, "employee_availability_types"."created_at" AS t1_r8, "employee_availability_types"."updated_at" AS t1_r9, "employee_availability_types"."operational" AS t1_r10, "employee_availability_types"."default_duration" AS t1_r11, "employee_availability_types"."created_in_company_id" AS t1_r12, "employee_availability_types"."created_in_branch_id" AS t1_r13 FROM "employee_schedules" LEFT OUTER JOIN "employee_availability_types" ON "employee_availability_types"."id" = "employee_schedules"."availability_type_id" WHERE "employee_availability_types"."operational" = 'f'

But with a little bit complex query it produces PG error:

EmployeeSchedule.includes(:availability_type).where do
  (availability_type.operational == false) &
  (availability_type.workday == false)
end.references(:availability_type)

#DEBUG development>   SQL (1.0ms)  SELECT "employee_schedules"."id" AS t0_r0, "employee_schedules"."employee_id" AS t0_r1, "employee_schedules"."availability_type_id" AS t0_r2, "employee_schedules"."day" AS t0_r3, "employee_schedules"."start_at" AS t0_r4, "employee_schedules"."end_at" AS t0_r5, "employee_schedules"."created_in_division_id" AS t0_r6, "employee_schedules"."created_by_user_id" AS t0_r7, "employee_schedules"."created_by_employee_id" AS t0_r8, "employee_schedules"."created_at" AS t0_r9, "employee_schedules"."updated_at" AS t0_r10, "employee_schedules"."created_in_company_id" AS t0_r11, "employee_schedules"."created_in_branch_id" AS t0_r12, "employee_availability_types"."id" AS t1_r0, "employee_availability_types"."name" AS t1_r1, "employee_availability_types"."code" AS t1_r2, "employee_availability_types"."workday" AS t1_r3, "employee_availability_types"."color" AS t1_r4, "employee_availability_types"."created_in_division_id" AS t1_r5, "employee_availability_types"."created_by_user_id" AS t1_r6, "employee_availability_types"."created_by_employee_id" AS t1_r7, "employee_availability_types"."created_at" AS t1_r8, "employee_availability_types"."updated_at" AS t1_r9, "employee_availability_types"."operational" AS t1_r10, "employee_availability_types"."default_duration" AS t1_r11, "employee_availability_types"."created_in_company_id" AS t1_r12, "employee_availability_types"."created_in_branch_id" AS t1_r13 FROM "employee_schedules" LEFT OUTER JOIN "employee_availability_types" ON "employee_availability_types"."id" = "employee_schedules"."availability_type_id" WHERE (("availability_type"."operational" = 'f' AND "availability_type"."workday" = 'f'))
#ERROR development> PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "availability_type"
#LINE 1: ...mployee_schedules"."availability_type_id" WHERE (("availabil...
                                                             ^
#: SELECT "employee_schedules"."id" AS t0_r0, "employee_schedules"."employee_id" AS t0_r1, "employee_schedules"."availability_type_id" AS t0_r2, "employee_schedules"."day" AS t0_r3, "employee_schedules"."start_at" AS t0_r4, "employee_schedules"."end_at" AS t0_r5, "employee_schedules"."created_in_division_id" AS t0_r6, "employee_schedules"."created_by_user_id" AS t0_r7, "employee_schedules"."created_by_employee_id" AS t0_r8, "employee_schedules"."created_at" AS t0_r9, "employee_schedules"."updated_at" AS t0_r10, "employee_schedules"."created_in_company_id" AS t0_r11, "employee_schedules"."created_in_branch_id" AS t0_r12, "employee_availability_types"."id" AS t1_r0, "employee_availability_types"."name" AS t1_r1, "employee_availability_types"."code" AS t1_r2, "employee_availability_types"."workday" AS t1_r3, "employee_availability_types"."color" AS t1_r4, "employee_availability_types"."created_in_division_id" AS t1_r5, "employee_availability_types"."created_by_user_id" AS t1_r6, "employee_availability_types"."created_by_employee_id" AS t1_r7, "employee_availability_types"."created_at" AS t1_r8, "employee_availability_types"."updated_at" AS t1_r9, "employee_availability_types"."operational" AS t1_r10, "employee_availability_types"."default_duration" AS t1_r11, "employee_availability_types"."created_in_company_id" AS t1_r12, "employee_availability_types"."created_in_branch_id" AS t1_r13 FROM "employee_schedules" LEFT OUTER JOIN "employee_availability_types" ON "employee_availability_types"."id" = "employee_schedules"."availability_type_id" WHERE (("availability_type"."operational" = 'f' AND "availability_type"."workday" = 'f'))
# => #<EmployeeSchedule::ActiveRecord_Relation:0x3fc362e7c088>

And with AR syntax it works perfect even without references:

EmployeeSchedule.includes(:availability_type).where(availability_type: { operational: false, workday: false })
# SELECT "employee_schedules"."id" AS t0_r0, "employee_schedules"."employee_id" AS t0_r1, "employee_schedules"."availability_type_id" AS t0_r2, "employee_schedules"."day" AS t0_r3, "employee_schedules"."start_at" AS t0_r4, "employee_schedules"."end_at" AS t0_r5, "employee_schedules"."created_in_division_id" AS t0_r6, "employee_schedules"."created_by_user_id" AS t0_r7, "employee_schedules"."created_by_employee_id" AS t0_r8, "employee_schedules"."created_at" AS t0_r9, "employee_schedules"."updated_at" AS t0_r10, "employee_schedules"."created_in_company_id" AS t0_r11, "employee_schedules"."created_in_branch_id" AS t0_r12, "employee_availability_types"."id" AS t1_r0, "employee_availability_types"."name" AS t1_r1, "employee_availability_types"."code" AS t1_r2, "employee_availability_types"."workday" AS t1_r3, "employee_availability_types"."color" AS t1_r4, "employee_availability_types"."created_in_division_id" AS t1_r5, "employee_availability_types"."created_by_user_id" AS t1_r6, "employee_availability_types"."created_by_employee_id" AS t1_r7, "employee_availability_types"."created_at" AS t1_r8, "employee_availability_types"."updated_at" AS t1_r9, "employee_availability_types"."operational" AS t1_r10, "employee_availability_types"."default_duration" AS t1_r11, "employee_availability_types"."created_in_company_id" AS t1_r12, "employee_availability_types"."created_in_branch_id" AS t1_r13 FROM "employee_schedules" LEFT OUTER JOIN "employee_availability_types" ON "employee_availability_types"."id" = "employee_schedules"."availability_type_id" WHERE (("employee_availability_types"."operational" = 'f' AND "employee_availability_types"."workday" = 'f'))

Also I check this with squeel 1.1.0 and rails 4.0. It works without exceptions, but with deprecation warning:

 WARN development> DEPRECATION WARNING: It looks like you are eager loading table(s) (one of: employee_schedules, employee) that are referenced in a string SQL snippet. For example: 

    Post.includes(:comments).where("comments.title = 'foo'")

Currently, Active Record recognizes the table in the string, and knows to JOIN the comments table to the query, rather than loading comments in a separate query. However, doing this without writing a full-blown SQL parser is inherently flawed. Since we don't want to write an SQL parser, we are removing this functionality. From now on, you must explicitly tell Active Record when you are referencing a table from a string:

    Post.includes(:comments).where("comments.title = 'foo'").references(:comments)

EmployeeSchedule.includes(:availability_type).where do
  availability_type.operational == false
end
# => deprecation warning

EmployeeSchedule.includes(:availability_type).where do
  availability_type.operational == false
end.references(:availability_type)
# => no deprecation warning

EmployeeSchedule.includes(:availability_type).where do
  (availability_type.operational == false) &
  (availability_type.workday == false)
end.references(:availability_type)
# => deprecation warning again

So, I think this bug comes from previous versions of squeel.

[reejosamuel]

Facing the same issue with includes.

[bigxiang]

@90yukke @reejosamuel Working on this. If possible, please use

EmployeeSchedule.includes(:availability_type)
  .where { availability_type.operational == false }
  .where { availability_type.workday == false }
  .references(:availability_type)

as an alternative way first.

[Kriechi]

Got the same problem now... Not sure what changed, but I thought I had it working already on Rails 4.2.0. I'm using danielrhodes/squeel branch right now.

[mckinnsb]

I am also seeing this when using includes with a query which references the included model.