Haskell persistent - getting value via foreign key

Question

Let's assume I've got a very simple db with a Foreign Key (for simplicity of the example: one table with a self reference; context - modelling a financial instrument):

Instrument
    ticker String
    name String
    denomination InstrumentId -- FK !!
    domicile Country
    source DataSource
    UniqueT ticker
    deriving Eq Show

I can then get all rows by executing:

getAll :: (MonadIO m, MonadLogger m) => SqlReadT m [Entity Instrument]
getAll = select $ from $ return

I noticed that I can extract specific fields from the results using autogenerated functions, e.g.

getTicker :: Entity Instrument -> String
getTicker = instrumentTicker . entityVal

However, when I try referring to the value referenced via the foreign key, I get:

getDenomination :: Entity Instrument -> Key Instrument
getDenomination = instrumentDenomination . entityVal

My question: how can I refer to the remaining values that correspond to the "Key Instrument" received, e.g. how can I get "name" field of the referenced record ?

EDIT:

I've tried writing a subquery, but no good so far. What I tried was:

getInstrumentByKey :: (MonadIO m, MonadLogger m) => Key Instrument -> SqlBackendT m (Entity Instrument)
getInstrumentByKey key =
    select $ from $ \i ->
        where_ (i ^. InstrumentId ==. key)  -- type error, InstrumentKey is of type "SqlExpr (Value (Key Instrument))", while key is "Key Instrument"
        return i

How can I use the "Key Instrument" arg properly in my subquery ?

Broadly, your two options are to do another lookup by ID (obviously not ideal because it's a second query) or write a join (a self join in this case) using esqueleto. I don't have time to write up an answer now, especially as I'm not expert enough with these libraries to just write the code without actually trying things, but hopefully this gets you going. — Robin Zigmond, Mar 30 '20 at 06:10
@RobinZigmond I like the idea with the self join. I'll give it a go later today. In the meantime I found out one more problem with the subquery - question updated. — LA.27, Mar 30 '20 at 09:12
For your latest question, you just need to use [val](https://hackage.haskell.org/package/esqueleto-3.3.3.0/docs/Database-Esqueleto.html#v:val). However you don't actually need Esqueleto to fetch an entity by ID, Persistent's [get](https://www.yesodweb.com/book/persistent) (see the "Fetching by ID" section) will do this. — Robin Zigmond, Mar 30 '20 at 11:55
@RobinZigmond Can you post your comment as answer so that I can accept it ? — LA.27, Mar 31 '20 at 00:01
Apologies, I've been a bit busy lately. I can try to get an answer written up tonight, although I won't promise it will be done. I'm actually still unsure precisely what code to give you, although I'm pleased that my general hints were enough. — Robin Zigmond, Mar 31 '20 at 11:37
I have expanded my comments a little into an answer, although I confess I copped out of trying to provide actually working code and just included documentation links. — Robin Zigmond, Mar 31 '20 at 21:58

score 1 · Accepted Answer · answered Mar 31 '20 at 21:57

Expanding just a little on what I've Al said in the comments:

Broadly, you have two options for this. One is to do another lookup by ID, as explained in the Persistent documentation (see the section "fetching by ID"). This is not ideal, because it involves a second trip to the database.

But since you are clearly using an SQL database, this is exactly what joins are for. And you're already using Esqueleto, which allows you to "translate" SQL queries - including joins (which you can't express with just Persistent) - into Haskell. In this case you'd be joining your table onto itself, using the denomination field and the ID. Again, the library's documentation is easy to follow.

Haskell persistent - getting value via foreign key

1 Answers1