How to select the most recent record in Rails that's via a join to another table with multiple records

Question

I have a table of employees and another table of employments. A given employee can have one or more employments. I want to return a relation of employees but joining with their most recent employment, based on a created_at date on the employment.

I put a sqlfiddle in place: http://sqlfiddle.com/#!17/b0a3d/2 but that's the basic SQL so it returns a single row for each employee and with the employment data for that employee that corresponds to their latest employment.

NOTE: the sqlfiddle is me thinking about the problem from a SQL perspective, but what I really am after is how to do this via Ruby on Rails. How to join Employee records with Employment records but only pulling the latest Employment record. (hopefully this helps clarify).

So Employee.includes(:employments) basically does what the sqlfiddle does.

I need something a bit fancier to join the employee and employment data (with latest employment).

I welcome any thoughts.

I think I worded this question a bit too vague. I created a new SO question that is much simpler and gets to the heart of my confusion: https://stackoverflow.com/questions/60551675/how-do-i-build-an-arel-query-in-ruby-on-rails-that-joins-on-the-max-of-a-has-man — Dan Sharp, Mar 05 '20 at 18:14

score 0 · Answer 1 · answered Mar 05 '20 at 00:34

0

In Postgres, you can use distinct on to solve this top-1-per-group problem:

select distinct on(e.id) e.id, e.name, m.created, m.active
from employees e
inner join employments m on m.employee_id = e.id;
order by e.id, m.created desc;

In your DB Fiddle, this produces:

id  name created    active
1   Dan  2019-10-23 false
2   Sam  2019-07-11 true
3   Joe  2019-03-13 false

answered Mar 05 '20 at 00:34

GMB

216,147
25
84
135

Thank you GMB! That helps. I guess perhaps I wasn't fully clear, though. I'm hoping to do this in Ruby on Rails, not as pure SQL. – Dan Sharp Mar 05 '20 at 01:07
@DanSharp: ah ok, my bad. The db fiddle was appealing. You should probably edit your question to show your current ruby code, so it is unambiguous what you want. – GMB Mar 05 '20 at 01:21
Adding the `distinct on()` in your rails code should be fairly trivial, though. – Ja͢ck Mar 05 '20 at 03:32

score 0 · Answer 2 · answered Mar 05 '20 at 03:19

0

Does this achieve what you want -

Employee.joins(:employments)
        .select(:id, :name, 'employments.created', 'employments.active')
        .order('employments.created_at desc')

answered Mar 05 '20 at 03:19

Gautam

1,754
1
14
22

score 0 · Answer 3 · answered Mar 05 '20 at 15:58

I would first find the MAX(created) of each employment in Employment table by a sub-query then inner join the result with Employee table.

subquery = """
SELECT employments.employee_id, MAX(created)
FROM employments
GROUP BY employments.employee_id
"""

Employee.joins("INNER JOIN (#{subquery}) AS t ON t.employee_id = employees.id")

How to select the most recent record in Rails that's via a join to another table with multiple records

3 Answers3