[SOLVED] Haskell persistent - getting value via foreign key

Haskell persistent - getting value via foreign key

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 ?

Solution

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.