How to join tables to get only the last record of has-many association?

Question

I have two models: Company and Transaction (has-many relationship). Transaction model has balance attribute. I have a query to join the models:

scope :joined_transactions, (lambda do
  select('transactions.balance as current_balance')
  .joins('LEFT OUTER JOIN transactions ON transactions.company_id = companies.id')
end)

However, I want to include only the last transaction into this query. As a result Company.joined_transactions.first.current_balance == Company.first.transactions.last.balance should be true.

The only place it's clear that you're talking about Ruby on Rails and ActiveRecord is in your tags. — TiggerToo, Aug 10 '18 at 21:05

score 6 · Answer 1 · answered Apr 19 '18 at 04:56

6

subquery .joins('left join transactions t on t.company_id = companies.id AND t.id = (SELECT MAX(id) FROM transactions WHERE transactions.company_id = t.company_id)')

answered Apr 19 '18 at 04:56

haroldus

111
1
3

score -3 · Answer 2 · edited May 23 '17 at 12:31

-3

I believe this problem was already solved many times. You can do it using subquery or double join.

Please add more info on your current case if this doesn't work for you

edited May 23 '17 at 12:31

Community

1
1

answered Feb 16 '16 at 19:33

faron

1,051
7
9

1

I realize that his intent wasn't clear except in his tags, but neither of those are ActiveRecord solutions. – TiggerToo Aug 10 '18 at 21:04
Rarely do you find a problem in computer science that hasn't been solved many times. – Rambatino May 23 '19 at 01:29

How to join tables to get only the last record of has-many association?

2 Answers2