Separate Query

separate.hs

-- WORKING CODE
getCategoryR :: Handler Html
getCategoryR = do
    allCategories <- runDB $ selectList [] [] :: Handler [Entity Category]
    let badges = []
    categoriesWithExpenseCount <- categoriesWithExpenseCount
    (formWidget, formEnctype) <- generateFormPost categoryForm
    defaultLayout $(widgetFile "category")

categoriesWithExpenseCount :: Handler [(Entity Category, E.Value Int)]
categoriesWithExpenseCount = runDB $ E.select $ E.from $ \(e `E.InnerJoin` c) -> do
    E.on (e E.^. ExpenseCategoryId E.==. c E.^. CategoryId)
    E.groupBy (c E.^. CategoryId)
    let cr = E.count (e E.^. ExpenseAmount)
    return (c, cr)
 
-- WITH SEPARATE QUERY
getCategoryR :: Handler Html
getCategoryR = do
    allCategories <- runDB $ selectList [] [] :: Handler [Entity Category]
    let badges = []
    categoriesWithExpenseCount <- runDB categoriesWithExpenseCountQuery
    (formWidget, formEnctype) <- generateFormPost categoryForm
    defaultLayout $(widgetFile "category")

categoriesWithExpenseCountQuery :: MonadIO m => SqlReadT m [(Entity Category, E.Value Int)]
categoriesWithExpenseCountQuery = E.select $ E.from $ \(e `E.InnerJoin` c) -> do
    E.on (e E.^. ExpenseCategoryId E.==. c E.^. CategoryId)
    E.groupBy (c E.^. CategoryId)
    let cr = E.count (e E.^. ExpenseAmount)
    return (c, cr)