Getting the result of aggregate functions in Esqueleto
Let's say I have the following model:
Person
stackOverflowUser Bool
age Int Maybe
Using Esqueleto (& Yesod), let's say I want to get the average age of the users. I would like to make a function with a 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)
This gets me [Value (Maybe Int)], but I need to go to "Maybe Int". I tried doing
mapM_ unValue userAge
But somehow it caused a type error, giving me [Maybe ()] and not [Maybe Int] ... Also, I think the last line in the above code should have:
person ?. PersonAge
but not
person ^. PersonAge
as PersonAge can be NULL, but changing it gives me a type error:
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 what I am doing, but I cannot find examples anywhere on the internet to use aggregated functions in Esqueleto and I am fairly new to Haskell so I have a hard time figuring it out.
I suppose I could just use raw SQL, but if possible I would like to do it with Esqueleto.
source to share
Got! Finally, wrapping around type errors and came up with the following:
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))
"Person ^ PersonAge" does not cause any problems; I tested it with null and non-null values. I suppose that "?". the operator is reserved for other situations.
Hope this saves someone else some time figuring this out!
source to share