PostgreSQL find records and sort by left outer join

Question

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?

What exactly do you mean with "not sorted properly"? What would a "proper" sort order be? — user330315
– user330315, Commented Feb 9, 2012 at 9:24
records are not sorted by 'events.created_at DESC' as instructed. — cnikolaou
– cnikolaou, Commented Feb 9, 2012 at 9:27
I'm pretty sure ORDER BY works correctly in Postgres. Please show us an example output of what you think is not correct. Are you aware that events.created_at might contain NULL values due to the outer join? — user330315
– user330315, Commented Feb 9, 2012 at 9:30
@a_horse_with_no_name yes, I am aware. Any idea how to ignore created_at NULL values in the LEFT OUTER JOIN query? — cnikolaou
– cnikolaou, Commented Feb 9, 2012 at 9:51
@a_horse_with_no_name I checked events.created_at column for NULL values and found none. — cnikolaou
– cnikolaou, Commented Feb 9, 2012 at 9:56

cmouse · Accepted Answer · 2012-02-09 10:35:16Z

1

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.

answered Feb 9, 2012 at 10:35

cmouse

6721 gold badge6 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL find records and sort by left outer join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related