How to do "SELECT ... IN (SELECT ...)" using Esqueleto?

Given the following two models and a request GET /articles/:slug/comments

, I want to get comments related to the article based on it slug

.

Article json sql=articles
    slug        Slug
    title       Text
    description Text
    body        Text
    createdAt   UTCTime default=now()
    updatedAt   UTCTime Maybe default=NULL
    userId      UserId
    UniqueSlug  slug

Comment json sql=comments
    body      Text
    createdAt UTCTime default=now()
    updatedAt UTCTime Maybe default=NULL
    articleId ArticleId
    userId    UserId

      

        
        
        
      

    

Using tenacity rawSql

, we can accomplish the following:

getCommentsForArticle :: Slug -> App (Cmts [Entity Comment])
getCommentsForArticle slug = do
    comments <- runDb $ rawSql stm [toPersistValue slug]
    return (Cmts comments)
        where stm = "SELECT ?? FROM comments \
                    \WHERE article_id IN (\
                        \SELECT id FROM articles WHERE slug = ?)"

      

        
        
        
      

    

However, given that I want to maintain type safety between Haskell and SQL, I want to rewrite this with esqueleto

. This is the part I struggle with. Reading the docs, sub_select seems to be a tool for the job. Here's what I have:

getCommentsForArticle :: Slug -> App (Cmts [Comment])
getCommentsForArticle slug = do
    comments <- E.select $
        E.from $ \cmts -> do
            let subQuery =
                    E.from $ \arts -> do
                        E.where_ $ arts ^. ArticleSlug ==. E.val slug
                        return (arts ^. ArticleId)
            E.where_ $ cmts ^. CommentArticleId ==. E.sub_select subQuery
            return cmts
    return $ Cmts comments

      

        
        
        
      

    

I also noticed the in_ operator , but I cannot figure out how to use it, or if it is more appropriate than sub_select .

What am I missing? Is the syntax correct? Thank.