CODEWITHSUNDEEP
mysqlsqlruby-on-railsactiverecord
Sandworm
Sandworm

Reputation: 131

Finding an object in db and attaching an extra param from another table in ActiveRecord

I have two tables in my db: accounts and transactions. They look more or less like this, with relevant columns and example entries:

accounts

id |     uid     |
------------------
 1 |   "abcde"   |
 2 |   "qwert"   | 

transactions

id | account_id | amount | balance | 
------------------------------------
 1 | 1          | 100    | 100     |
 2 | 1          | 200    | 300     |
 3 | 2          | 500    | 500     |
 4 | 2          | 300    | 800     |

So basically Account has many Transactions, and the transaction tells what the amount was + it shows the balance after adding that transaction to the previous balance. So last transaction tells us what the current balance on the account is.

Now I would like to fetch an account, having only its uid, but with a twist - I'd like to also fetch the balance, which would be the balance column of the last transaction (based on id) that belongs to that account. Ideally this will give me an object of the Account model, with the additional balance param accessible using object[:balance].

I know that this would require a left outer join and a select that adds the balance param to the object, something like select('accounts.*', 'transactions.balance as balance') - plus picking up the last one, so ordering by id and limiting to 1 - but I have no idea how to construct this query (using activerecord methods or pure SQL), considering the fact that I only have access to the uid of the account, not its id which is referenced as the account_id.

Upvotes: 0

Views: 31

Answers (1)

Francisco Marão
Francisco Marão

Reputation: 379

You can try something along these lines:

WITH ranked AS (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY id DESC) AS tr
  FROM transactions AS t inner join accounts a on a.id = t.id
  where a.uid = 'qwert'
)
SELECT * FROM ranked WHERE tr = 1;

Upvotes: 0

Related Questions