Composing Database.Esqueleto Queries, Conditional Joins and Counting

Composing Database.Esqueleto queries, conditional joins and counting

For LIMIT and COUNT, hammar's answer is entirely correct so I'll not delve into them. I'll just reiterate that once you use select you'll not be able to change the query in any way again.

For JOINs, currently you are not able to do a INNER JOIN with a query that was defined in a different from (nor (FULL|LEFT|RIGHT) OUTER JOINs). However, you can do implicit joins. For example, if you have defined:

baseQuery = 
  from $ \(p `InnerJoin` b) -> do 
  on (p ^. PersonId ==. b ^. BlogPostAuthorId)
  where_ (p ^. PersonName `like` val "J%")
  return (p, b)

Then you may just say:

commentsQuery = 
  from $ \c -> do
  (p, b) <- baseQuery
  where_ (b ^. BlogPostId ==. c ^. CommentBlogPostId)
  return (p, b, c)

Esqueleto then will generate something along the lines of:

SELECT ...
FROM Comment, Person INNER JOIN BlogPost
ON    Person.id = BlogPost.authorId
WHERE Person.name LIKE "J%"
AND   BlogPost.id = Comment.blogPostId

Not pretty but gets the job done for INNER JOINs. If you need to do a OUTER JOIN then you'll have to refactor your code so that all the OUTER JOINs are in the same from (note that you can do an implicit join between OUTER JOINs just fine).

Counting rows with esqueleto

It turns out that the Esqueleto code above is correct. The error was in another part of the code where a lack of restrictions caused type-ambiguity.

`?` placeholder for SQL `IN` condition with persistent's `rawSql`

I don't think there is a way to do it with persistent.
postrgresql-simple (assuming that we are talking about Postgres here) which is used by persistent does have special In construct which is correctly translated into In (..) in SQL, but persistent doesn't seem to use it.
One may hope that a workaround is to use PersistDbSpecific constructor which takes ByteString as an argument (so we could manually render and pass something like (123,456,789)) but unfortunately it is converted into SQL via Unknown which is then rendered via Escape which not only escapes the string but also encloses it into quotes which makes our SQL invalid. If persistent were using Plain (which in my view would make much more sence) this approach would work, but unfortunately it is not the case.

---

Related Topics