3

In all the examples I have seen the results from esqueleto are projected into a list of tuples or to entities records.

For example:

previousLogItems <- select $ from $ \li -> do
        orderBy [desc (li ^. LogItemId)]
        limit 10
        return (li ^. LogItemId, li ^. LogItemTitle)

Is there any way in esqueleto to project a subset of the columns to custom records (different than the entity) instead of tuples? This being done without an extra projection from tuples to custom records.

As an example, let's say that it would be inefficient to get all the data from the database so we only want to project columns WindowTitle and BeginTime from the database into a custom record with adequate names for those columns.

Update

Example of not working code:

data Custom = Custom
  { title :: Text
  , id    :: Int
  } deriving (Eq, Show, Generic)

daily :: Servant.Handler [Custom]
daily = do
  lis <- liftIO $ runDB $
            select $ from $ \li -> do
                    orderBy [desc (li ^. LogItemId)]
                    limit 25
                    return (Custom (li ^. LogItemTitle) (li ^. LogItemId))
  return lis

Error:

• Couldn't match expected type ‘Text’
              with actual type ‘SqlExpr (Database.Esqueleto.Value Text)’
• In the first argument of ‘Custom’, namely ‘(li ^. LogItemTitle)’
  In the first argument of ‘return’, namely
    ‘(Custom (li ^. LogItemTitle) (li ^. LogItemId))’
  In a stmt of a 'do' block:
    return (Custom (li ^. LogItemTitle) (li ^. LogItemId))

Update

Example of not working code:

daily :: Servant.Handler [Custom]
daily = do
  lis <- liftIO $ runDB $
            select $ from $ \li -> do
                    orderBy [desc (li ^. LogItemId)]
                    limit 25
                    return (Custom <$> (li ^. LogItemTitle) <*> (li ^. LogItemId))
  return lis

Error:

• Couldn't match type ‘Database.Esqueleto.Value Text’ with ‘Text’
  Expected type: SqlExpr Text
    Actual type: SqlExpr (Database.Esqueleto.Value Text)
• In the second argument of ‘(<$>)’, namely ‘(li ^. LogItemTitle)’
  In the first argument of ‘(<*>)’, namely
    ‘Custom <$> (li ^. LogItemTitle)’
  In the first argument of ‘return’, namely
    ‘(Custom <$> (li ^. LogItemTitle) <*> (li ^. LogItemId))’


• Couldn't match type ‘Database.Esqueleto.Value (Key LogItem)’
                 with ‘Int’
  Expected type: SqlExpr Int
    Actual type: SqlExpr (Database.Esqueleto.Value (Key LogItem))
• In the second argument of ‘(<*>)’, namely ‘(li ^. LogItemId)’
  In the first argument of ‘return’, namely
    ‘(Custom <$> (li ^. LogItemTitle) <*> (li ^. LogItemId))’
  In a stmt of a 'do' block:
    return (Custom <$> (li ^. LogItemTitle) <*> (li ^. LogItemId))
Răzvan Flavius Panda
  • 21,730
  • 17
  • 111
  • 169
  • Can't we here perform an `fmap`? – Willem Van Onsem May 02 '18 at 21:55
  • I think you might have to instantiate [SqlSelect](https://hackage.haskell.org/package/esqueleto-2.5.3/docs/Database-Esqueleto-Internal-Sql.html#t:SqlSelect). I'd maybe look at the way the tuple instances are defined and go from there. – DarthFennec May 02 '18 at 22:02
  • @WillemVanOnsem I am asking of a way to specifically return custom records directly. Without the inefficiency of going tuple and then to the custom type. – Răzvan Flavius Panda May 02 '18 at 22:03
  • Yeah, but I was thinking something like `Custom <$> (li ^. LogItemTitle) <*> (li ^. LogItemId)`. But right now I can not test it :( – Willem Van Onsem May 02 '18 at 22:04
  • @WillemVanOnsem I don't think lifting it will be enough. `select :: (SqlSelect a r, MonadIO m) => SqlQuery a -> SqlReadT m [r]`, from what I can tell there's an actual translation from the return type `a` to an SQL expression to the final type `r`. – DarthFennec May 02 '18 at 22:15
  • @WillemVanOnsem I tried your example and it is also not working. See last update. – Răzvan Flavius Panda May 02 '18 at 22:21

1 Answers1

2

What esqueleto is actually doing here is a little complicated. Here is the type for select:

select :: (SqlSelect a r, MonadIO m) => SqlQuery a -> SqlReadT m [r]

This takes an SqlQuery a (a monad wrapping the value you return), and returns an SqlReadT m [r] (a monad wrapping a list of results). When you return your Custom type, the following happens:

  1. esqueleto converts your a type to persistent's internal SQL representation
  2. persistent executes the query and gets back a results list
  3. esqueleto converts the results list from persistent's internal SQL representation to [r]

To make this work for custom types, you'll need to instantiate SqlSelect, and define conversion functions to and from the persistent types:

data Custom' = Custom' (Value Text) (Value Int)

data Custom = Custom
  { title :: Text
  , id    :: Int
  }

instance SqlSelect Custom' Custom where
  sqlSelectCols esc (Custom' a b) = (mconcat [ta, ", ", tb], va ++ vb)
    where
      (ta, va) = sqlSelectCols esc a
      (tb, vb) = sqlSelectCols esc b
  sqlSelectColCount _ = 2
  sqlSelectProcessRow [PersistText a, PersistInt64 b] = Right $ Custom a b
  sqlSelectProcessRow _ = Left "Error: Incorrect rows to translate to Custom"

(note that I wasn't actually able to test any of the above, so it might have bugs)

One thing to note is that in the above example, the a and r types are not the same (Custom' vs Custom). This is because inside of a select, all of the values you're working with are of type Value, not their actual types.

DarthFennec
  • 2,650
  • 17
  • 24