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
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