Considering the following two models and a GET /articles/:slug/comments
request, I want to retrieve the comments that belong to an article, based on its 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 persistence's rawSql
, we can accomplish this as follows
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 using esqueleto
. This is the part I am struggling with. By reading the docs, sub_select seems to be the 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 can't figure out how to use it nor if it is more appropriate than sub_select.
What am I missing? Is the syntax correct? Thanks.
You'll want something like this