Getting the result of aggregate functions in Esqueleto

Question

Say I have the following model:

Person
    stackOverflowUser Bool
    age Int Maybe

Using Esqueleto (& Yesod), say I want to get the average age of Stack Overflow users. I'd like to make a function with the type signature:

userAge :: Handler (Maybe Int)

So far, I have the following:

userAge :: Handler [Value (Maybe Int)]
userAge = runDB $
    select $
    from $ \person -> do
    where_ (person ^. PersonStackOverflowUser ==. val True)
    return joinV $ avg_ (person ^. PersonAge)

That gets me [Value (Maybe Int)], but I need to get down to "Maybe Int". I tried doing

mapM_ unValue userAge

But for some reason, doing so raised a type error, giving me [Maybe ()] rather than [Maybe Int]... Additionally, I think that the last line in the above code should have:

person ?. PersonAge

rather than

person ^. PersonAge

since PersonAge can be NULL, but changing it gives me a type error since:

avg_ :: (PersistField a, PersistField b) => expr (Value a) -> expr (Value (Maybe b))
(^.) :: (PersistEntity val, PersistField typ) => expr (Entity val) -> EntityField val typ -> expr (Value typ)
(?.) :: (PersistEntity val, PersistField typ) => expr (Maybe (Entity val)) -> EntityField val typ -> expr (Value (Maybe typ))

This is probably easier than I'm making it out to be, but I can't find examples anywhere online for using aggregate functions in Esqueleto, and I'm pretty new to Haskell so I'm having trouble figuring it out.

I suppose I could just use raw SQL, but if it's possible I'd like to do this using Esqueleto.

score 3 · Accepted Answer · answered Sep 18 '14 at 11:47

Got it! Finally wrapped my head around the type errors and came up with this:

import Safe (headMay)
import Control.Monad (join)
import Database.Esqueleto
-- other misc Yesod imports

userAge :: Handler (Maybe Int)
userAge = do
    a <- runDB $ select $
                 from $ \person -> do
                 where_ (person ^. PersonStackOverflowUser ==. val True)
                 return $ joinV $ avg_ (person ^. PersonAge)
    return $ join (headMay (map unValue a))

The "person ^. PersonAge" doesn't appear to cause any issues; I tested it on null and non-null values. I suppose the "?." operator is reserved for other situations.

Hopefully this saves someone else some time figuring it out!

Getting the result of aggregate functions in Esqueleto

1 Answers1