Haskell Cookbook

How it works...

In this recipe, we did a self-join to get the referral count for the users. The query using esqueleto is as follows:

   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)

The query looks very similar to SQL itself. Esqueleto uses a monadic DSL for writing queries in the tune of SQL. For example a  select * from users query will become the following in Esqueleto:

        select (from $ \user -> return user)

If we are searching for a particular user, then we can write the following:

 select (from $ \user -> do where_ (user ^. UserEmail ==. val ...