3

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.

Rui Afonso Pereira
  • 527
  • 6
  • 14
  • Unrelated, but `SELECT * ...` is a bad style :). And why not just use SQLite or any other SQL DBs? They are all well typed ;) –  May 28 '17 at 20:17
  • @Igor By bad style you mean not selecting only what is required? As per your second question, I have edited the question to specify I am using `persistent` with `postgresql`. – Rui Afonso Pereira May 28 '17 at 20:25
  • Yeah. Why not just use SQL when you need SQL? –  May 28 '17 at 20:34
  • Sorry @Igor, I keep not understanding what you mean by that. Are you suggesting to use just raw SQL for this instead of using Esqueleto? – Rui Afonso Pereira May 28 '17 at 20:36
  • Yep, nice and simple :) –  May 28 '17 at 20:37
  • Just compare you SQL example and your haskell code. This is weird :) And btw, shouldn't the compiler tell you about syntax? –  May 28 '17 at 20:40
  • 1
    @Igor Esqueleto aims to maintain type safety between Haskell and SQL. If you use plain SQL the compiler can not and will not check types are being marshalled correctly between the two worlds. – chi May 28 '17 at 21:05
  • 1
    @Igor, what @chi said is the reason why I want to switch to `esqueleto`. – Rui Afonso Pereira May 28 '17 at 21:19
  • @chi I added some clarifications to my initial question. – Rui Afonso Pereira May 28 '17 at 21:33
  • I can't answer since I have only a very basic experience with Esqueleto, but what's wrong with your code? Does it compile? Does it generate the wrong result? – chi May 28 '17 at 22:15
  • 1
    Anyway, from a quick look at the docs: `sub_select` only returns *one* value, so it looks as the wrong tool. I'd try `in_` and `subList_select` to match the SQL closely. – chi May 28 '17 at 22:19

1 Answers1

0

You'll want something like this

getCommentsForArticle slug = do
  c <- select $ from $ \cmts -> do
         let a = subList_select $ from $ \arts -> do
                 where_ $ arts ^. ArticleSlug ==. val slug
                 return $ arts ^. ArticleId
         where_ $ cmts ^. CommentArticleId `in_` a
         return cmts
  return $ Cmts c
Jezen Thomas
  • 13,619
  • 6
  • 53
  • 91