What is a correct way of doing COUNT(id) in Esqueleto and Yesod

Question

I'm trying to figure out how to write the following query using Esqueleto

SELECT COUNT("person"."id")
FROM "person"
WHERE (("person"."admin" = 't' OR "person"."vip" = 't') // 't' as in True
      OR "person"."karma" >= 5000 AND "person"."hellbanned" = 'f')

Here's how my model is defined

Person
    admin Bool
    vip Bool
    karma Int
    hellbanned Bool

I've managed to get almost everything, except for the COUNT part

select $
  from $ \p -> do
    where_
      ((p ^. PersonAdmin ==. val True) ||. (p ^. PersonVip ==. val True)
      &&. (p ^. PersonKarma >=. val 5000) &&. (p ^. PersonHellbanned ==. val False))
    return $ p ^. PersonId

I managed to find a countRows function, however I haven't managed to find a way to combine these two in a way that typechecks.

I'm also not sure if I need all those p ^. in each branch of the where clause, or if those can be collapsed together somehow?

Did you try using [the count function](http://haddocks.fpcomplete.com/fp/7.4.2/20130829-168/persistent/Database-Persist-Class.html#v:count). — Michael Snoyman, Jun 04 '14 at 19:33
There is the [countRows](https://www.stackage.org/haddock/lts-9.6/esqueleto-2.5.3/Database-Esqueleto.html#v:countRows) function in `Database.Esqueleto`. — mb21, Oct 03 '17 at 13:20

score 3 · Answer 1 · answered Jun 03 '14 at 17:04

Here's some old code I have that does a count, I don't remember much about this, but hopefully it helps!

selectCount
  :: (From SqlQuery SqlExpr SqlBackend a)
  => (a -> SqlQuery ()) -> Persist Int
selectCount q = do
  res <- select $ from $ (\x -> q x >> return countRows)
  return $ fromMaybe 0 $ (\(Value a) -> a) <$> headMay res

getCount :: RepositoryUri -> Persist Int
getCount ruri =
  selectCount $ \(r `InnerJoin` rs) -> do
    on     $ r ^. R.RepositoryId  ==. rs ^. Repo
    where_ $ r ^. R.Uri ==. val ruri

score 2 · Answer 2 · answered May 28 '16 at 18:30

I've found Adam Bergmark's answer quite useful, but I think it should be more informative:

import Import hiding ( Value )
import Data.Maybe ( maybeToList
                  , listToMaybe )
import Database.Esqueleto
import Database.Esqueleto.Internal.Language (From)

selectCount
  :: (From SqlQuery SqlExpr SqlBackend a)
  => (a -> SqlQuery ()) -> YesodDB App Int
selectCount q = do
  res <- select $ from $ (\x -> q x >> return countRows)
  return $ fromMaybe 0 . listToMaybe . fmap (\(Value v) -> v) $ res

This worked so well (after 1-2 hours wasted) :) Why isn't this just build in ? I don't understand... — Alebon, Dec 04 '16 at 07:16

What is a correct way of doing COUNT(id) in Esqueleto and Yesod

2 Answers2