I'm trying to order retrieved records by a field on a left outer join in the following SQL query:
@companies = Company.scoped
@companies = @companies.where("companies.is_deleted = 'f' AND companies.state IN (?)", ["draft", "pending"])
@companies = @companies.includes(:events)
@companies = @companies.order("events.created_at DESC")
SELECT "companies"."id" AS t0_r0, "companies"."name" AS t0_r1, "companies"."reference" AS t0_r2, "companies"."state" AS t0_r3, "companies"."description" AS t0_r4, "companies"."remarks" AS t0_r5 "events"."id" AS t2_r0, "events"."eventable_type" AS t2_r1, "events"."eventable_id" AS t2_r2, "events"."event_type" AS t2_r3, "events"."creator_company_id" AS t2_r4, "events"."creator_user_id" AS t2_r5, "events"."created_at" AS t2_r6
FROM "companies"
LEFT OUTER JOIN "events" ON "events"."eventable_id" = "companies"."id" AND "events"."eventable_type" = 'company'
WHERE "companies"."is_deleted" = 'f' AND companies.state IN ('draft','pending')
ORDER BY events.created_at DESC
But the retrieved records are not sorted properly as expected (i.e. by events.created_at)
Any clue how to successfully do that while keeping performance efficient?
I think you should not be using LEFT JOIN, or if the code generates LEFT JOIN in this case, it is probably wrong.
The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A)," -- http://en.wikipedia.org/wiki/Join_%28SQL%29#Left_outer_join
Thus, if you do not wish to have all records from COMPANIES, even when there is no match on events, you should not really be using left join here. It will result rows with NULL values for the right side table.