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 "a@example.com")
          return user
        )

In our example, we have used InnerJoin on two tables. We have specified this with p InnerJoin q. We then added the criteria using the on, where_, groupBy, orderBy and count functions, which translate to the corresponding SQL keywords (ON, GROUPBY, ORDERBY and COUNT).