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.