I have these 2 tables:
Securities
| id | name |
+----+------+
| 1 | A |
| 2 | B |
Prices
| security_id | price | price_date |
+-------------+-------+------------+
| 1 | 1.1 | 2018-11-01 |
| 1 | 1.2 | 2018-10-01 |
| 1 | 1.3 | 2018-09-01 |
| 2 | 2.2 | 2018-11-01 |
| 3 | 2.3 | 2018-10-01 |
As you can see, a security has multiple prices. The one with the most recent date is the “current” price. I want to get a query that gives me this table:
Securities with Current Price
| id | name | price | price_date |
+----+------+-------+------------+
| 1 | A | 1.1 | 2018-11-01 |
| 2 | B | 2.2 | 2018-11-01 |
I have a PriceRepository
with a current
method like so:
class PriceRepository < Hanami::Repository
def current
prices.
distinct(:security_id).
order { [security_id, price_date.desc] }
end
end
which gives me a
SELECT DISTINCT ON (security_id) * FROM prices ORDER BY security_id, price_date DESC;
query.
Ideally, I’d like to be able to re-use this to join against securities so I can get securities with their current price. The SQL would look like
SELECT * FROM securities
JOIN (
select distinct on (security_id) * from prices order by security_id, price_date desc
) current_prices
ON securities.id = current_prices.security_id
I’m still pretty unfamiliar with Hanami Repository and ROM and I’m having a hard time understanding the interoperability between them. So I have no idea how to achieve this from a SecurityRepository.
Can someone point me in the right direction?