More complex join with Hanami relation & ROM


#1

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?