CODEWITHSUNDEEP
haskellyesodpersistentesqueleto
Jakub Arnold
Jakub Arnold

Reputation: 87220

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

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?

Upvotes: 9

Views: 673

Answers (2)

FtheBuilder
FtheBuilder

Reputation: 1437

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

Upvotes: 2

Adam Bergmark
Adam Bergmark

Reputation: 7536

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

Upvotes: 3

Related Questions