SQL
Yesod

Loading m:n relations in Yesod with Esqueleto

Posted on Sep 29, 2016 by Alexej Bondarenko

In times of NoSQL databases SQL is still a good choice for many domains. Surprisingly, even if the name RDMS (r = Relational) implies relations, SQL is pretty hard to handle for m:n relations. Graph databases (Neo4j, OrientDB, ArangoDB, ...) are much easier in terms of understanding connected data. I think the main reason for this lies in the wrong understandung of JOIN tables, which we have to use for m:n relations.

Furthermore I think all relations should be abstracted as own SQL tables. This way any application remains expandable and maintainable. By extracting relations to its own "Entities" you are able to keep you documents/entities/nodes free from connecting information like "belongsTo". With this background it is pretty obvious that using m:n JOIN tables is a very common use case. How can this be handled in a type safe way with Esqueleto?

Let's have a look at a simple domain model, like this blog. We have posts and categories. Each blog post can be posted under many categories. See? m:n, just like that. Lets define a simple Yesod (persist) schema:

Post
  uuid Text
  title Text
  content Textarea
  UniquePostUuid uuid
  Primary uuid
  deriving Show

Category
  uuid Text
  title Text
  UniqueCategoryUuid uuid
  Primary uuid
  deriving Show

BlogCategoryRelation
  blogId BlogId
  categoryId CategoryId
  UniqueBlogCategoryRelation blogId categoryId

Some words about this domain model. I am using text based uuids just for two reasons: simplicity and to demonstrate that we don't have to stick to our default numeric ID. (This solution has some pitfalls, like creating uuids in software instead of the underlying database).

We defined a Post and a Category model. Furthermore we defined a Relation between them by introducing a new Entity BlogCategoryRelation. A good chosen name for the relation name is pretty important. We will see why, later.

Lets spin off a Handler which will return a single blog post by its postId (or a slug, or whatever you like). First, adding a new route:

/blog/post/#PostId BlogPostR GET

... and the Handler:

getBlogPostR :: BlogId -> Handler Html
getBlogPostR postId = do
  post <- runDB $ getBy404 $ UniqueBlogPostUuid postId
  defaultLayout $ do
        setTitleI postTitle
        $(widgetFile "blog/post")

Alright, pretty straight forward. A single query to database, render default layout and use post to add data to our template. Now, what about categories? We need to find out which entries for this blog are stored in the BlogCategoryRelation table and load categories by the given related ID. We can do this by adding two imports from Esqueleto and compose our query:

import qualified Database.Esqueleto as E
import                Database.Esqueleto ((^.))


getBlogPostR :: BlogId -> Handler Html
getBlogPostR postId = do
  post <- runDB $ getBy404 $ UniqueBlogPostUuid postId
  categories <- runDB
             $ E.select
             $ E.from $ \(category `E.InnerJoin` postCategoryRelation) -> do
               E.on $ category ^. CategoryId E.==. postCategoryRelation ^. PostCategoryRelationCategoryId
               E.where_ $ postCategoryRelation ^. PostCategoryRelationPostId E.==. E.val postId
               E.orderBy $ [E.asc (category ^. CategoryTitle)]
               return (category)
  defaultLayout $ do
    setTitleI postTitle
    $(widgetFile "blog/post")

As you can see, we INNER JOIN the relation with the category table and filter for the given postId. Plus, we are only interested in category entities ordered by title. This example illustrates why relations in SQL appear complicated. Esqueleto can help a lot. But for me, the naming of the relation table is crucial. Sure, you will get longer names but you can easily see what is happening and which table is addressed. (Try this example by omitting the Relation appendix in the entity definition).

Hint: In the line category E.InnerJoin postCategoryRelation Esqueleto does not know which tables you are going to use, they are "guessed" later by observation of the used attributes in on and where.

I hope this post helps you in the daily business or your specific project. If you have troubles to understand, additional information about this topic or just any other questions, just drop some lines in the section below.