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 ...