ActiveRecord/PostgreSQL -- Sorting by first association value

Question

I'm having trouble constructing a query that would sort by a column of an associated table in a many-to-many relationship.

Say, for example, a User has_many Titles and a Title has_many Users. Their relationships are in a UserTitle table. How would I write a scope (so that it's chain-able with other queries) that returns Users sorted by the "name" column of the User's first title

<User id: 1, name: 'Bob'>
<User id: 2, name: 'Joe'>
<UserTitle id: 1, user_id: 1, title_id: 2>
<UserTitle id: 2, user_id: 2, title_id: 1>
<UserTitle id: 3, user_id: 2, title_id: 2>
<Title id: 1, name: 'A-Name'>
<Title id: 2, name: 'B-Name'>

If those were the User objects with their associated tables in my DB I would expect the sort query to return first Joe, then Bob (because Joe's first title is the title with A in it, which comes first in the alphabet). Using ruby logic this would be easy, but when it comes to constructing a SQL query using ActiveRecord, I'm not sure how to approach it.

And the "first" title of a user is supposed to be the alphabetically first as well? (You are aware that rows in a table have no natural "order".) Your `UserTitle id` most likely should be distinct (currently all `1`), but that's still hardly a good way to define the "first" title. — Erwin Brandstetter, Nov 21 '16 at 23:27
no, that isn't necessary. also thanks for catching the typo @ErwinBrandstetter — kceballos, Nov 22 '16 at 18:16

score 0 · Answer 1 · edited May 23 '17 at 11:59

One way (of many) would be a CROSS JOIN LATERAL:

SELECT u.*
FROM   "User" u
CROSS  JOIN LATERAL (
   SELECT title_id
   FROM   "UserTitle"
   WHERE  user_id = u.user_id 
   ORDER  BY usertitle_id
   LIMIT  1
   )
JOIN  "Title" t USING (title_id);

The best query depends on undisclosed details of your requirements and environment.

ActiveRecord/PostgreSQL -- Sorting by first association value

1 Answers1