[SOLVED] Getting the result of aggregate functions in Esqueleto

Getting the result of aggregate functions in Esqueleto

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.

Solution

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!