How to do it...

1. Create a new project called using-esqueleto with a simple stack template:

        stack new using-esqueleto simple

2. Add dependencies on the persistent, persistent-template, persistent-sqlite, text, and mtl libraries in the build-depends sub-section of the executable section. Also add esqueleto to the same sub-section:

 executable using-esqueleto
   hs-source-dirs:      src
   main-is:             Main.hs
   default-language:    Haskell2010
   build-depends:       base >= 4.7 && < 5
                       , persistent
                       , persistent-template
                       , persistent-sqlite
                       , text
                       , mtl
                       , esqueleto

3. Open src/Main.hs. We will be adding our source here.

4. Add the extensions required for invoking Persistent and persistent-template functions:

 {-# LANGUAGE EmptyDataDecls             #-}
 {-# LANGUAGE FlexibleContexts           #-}
 {-# LANGUAGE FlexibleInstances          #-}
 {-# LANGUAGE GADTs                      #-}
 {-# LANGUAGE GeneralizedNewtypeDeriving #-}
 {-# LANGUAGE MultiParamTypeClasses      #-}
 {-# LANGUAGE OverloadedStrings          #-}
 {-# LANGUAGE QuasiQuotes                #-}
 {-# LANGUAGE TemplateHaskell            #-}
 {-# LANGUAGE TypeFamilies               #-}
 {-# LANGUAGE DeriveGeneric              #-}

5. Add the declaration for the Main module:

 module Main where

6. Add the required imports:

 import Database.Persist.TH
 import Data.Text as T hiding (count, groupBy)
 import Database.Persist.Sqlite (runSqlite)
 import Control.Monad.Reader
 import Control.Monad.IO.Class
 import Database.Esqueleto

7. Create the model for the database. The following model represents a referral system in which one user can refer other users. This is usually used to award a user who can help pull in more users:

 share [mkPersist sqlSettings, mkMigrate "migrateAll"]  
 [persistLowerCase| 
 User
     email           Text
     UniqueEmail     email
     referredBy      UserId Maybe
     verified        Bool
     deriving Show
 |]

The preceding model represents a user with a unique email address. Users can register themselves, or can be referred by other users. Merely registering does not help; the user also has to validate their address (usually by clicking on the link sent for verification).

8. Write a query to get users with referral greater than 0. Note that only verified users count:

 getAllRefCounts :: MonadIO m => SqlPersistT m [(Value Text,  
 Value Int)]
 getAllRefCounts = 
    select $ from $ \(p `InnerJoin` r) -> do
      on (r ^. UserReferredBy ==. just (p ^. UserId))
      where_ (r ^. UserVerified ==. val True)
      groupBy (p ^. UserEmail, p ^. UserId)
      let cr = count (r ^. UserId )
      orderBy [ desc cr ]
      return (p ^. UserEmail, cr)

9. Add data to the referral system. Add users referred by others. One user hasn't verified their email yet:

 createData :: MonadIO m => SqlPersistT m ()
 createData = do
   a <- insert $ User "a@example.com" Nothing True
   b <- insert $ User "b@example.com" (Just a) True
   insert $ User "c@example.com" (Just a) True
   insert $ User "d@example.com" (Just b) True
   insert $ User "e@example.com" Nothing True
   insert $ User "f@example.com" (Just a) False
   return ()

 main :: IO ()
 main = runSqlite ":memory:" $ do
   runMigration migrateAll
   createData
   referrals <- getAllRefCounts
   liftIO $ putStrLn "Referral counts"
   liftIO $ print referrals

10. Build and execute the project:

        stack build
        stack exec -- using-esqueleto

11. You should see the following output: