Relationships

• Introduction
• Has Many Relationships
  • Order by
  • Multiple Records
  • Sorting With Multiple Records
• Belongs To Relationships
• Delete Behavior

Introduction

The following sections assume the following database schema being given. It’s the same as in “Your First Project”.

CREATE TABLE posts (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE TABLE comments (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    post_id UUID NOT NULL,
    author TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

ALTER TABLE comments ADD CONSTRAINT comments_ref_post_id FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE;

Has Many Relationships

Given a specific post, we can fetch the post and all its comments like this:

let postId :: Id Post = ...

post <- fetch postId
    >>= fetchRelated #comments

This Haskell code will trigger the following SQL queries to be executed:

SELECT posts.* FROM posts WHERE id = ?  LIMIT 1
SELECT comments.* FROM comments WHERE post_id = ?

In the view we can just access the comments like this:

[hsx|
    <h1>{get #title post}</h1>
    <h2>Comments:</h2>
    {post |> get #comments}
|]

The post |> get #comments returns a list of the comments belonging to the post.

The type of post is Include "comments" Post instead of the usual Post. This way the state of a fetched nested resource is tracked at the type level.

Order by

When we want to order the relationship in a certain way, we can just apply our commonly used orderBy function:

let postId :: Id Post = ...

post <- fetch postId
    >>= pure . modify #comments (orderByDesc #createdAt)
    >>= fetchRelated #comments

This works because the comments field of a Post is just a QueryBuilder before we call fetchRelated.

This query builder is equivalent to:

query @Comment |> filterWhere (#postId, get #id post)

The call to >>= pure . modify #comments (orderByDesc #createdAt) just appends a |> orderByDesc #createdAt like this:

query @Comment |> filterWhere (#postId, get #id post) |> orderByDesc #createdAt

Then the fetchRelated basically just executes this query builder and puts the result back into the comments field of the post record.

Multiple Records

When we want to fetch all the comments for a list of posts, we can use collectionFetchRelated:

posts <- query @Post
    |> fetch
    >>= collectionFetchRelated #comments

This will query all posts with all their comments. The type of posts is [Include "comments" Post].

The above Haskell code will trigger the following two SQL queries to be executed:

SELECT posts.* FROM posts
SELECT comments.* FROM comments WHERE post_id IN (?)

Sorting With Multiple Records

If you want to sort the results after fetching multiple records with collectionFetchRelated

posts <-
    query @Post |> fetch
        >>= collectionFetchRelated #comments
        >>= \posts ->
            posts
                |> sortOn (\post -> post |> get #comment |> get #title)
                |> pure

Belongs To Relationships

Given a specific comment, we can fetch the post this comment belongs to. Like other relationships this is also using fetchRelated:

let comment :: Id Comment = ...

comment <- fetch comment
    >>= fetchRelated #postId

This Haskell code will trigger the following SQL queries to be executed:

SELECT comments.* FROM comments WHERE id = ? LIMIT 1
SELECT posts.* FROM posts WHERE id = ?  LIMIT 1

In the view we can just access the comments like this:

[hsx|
    <h1>Comment to {comment |> get #postId |> get #title}</h1>
    <h2>Comments:</h2>
    {comment |> get #body}
|]

The type of comment is Include "postId" Comment instead of the usual Comment. This way the state of a fetched nested resource is tracked at the type level.

Delete Behavior

Usually, all your relations are secured at the database layer by using foreign key constraints. But that means e.g. deleting a post will fail when there still exists comments.

By default, a new foreign key constraint created via the Schema Designer has no on delete behavior specified. Therefore the foreign key constraint will look like this:

ALTER TABLE comments ADD CONSTRAINT comments_ref_post_id FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE NO ACTION;

See the NO ACTION at the end of the statement? We have to change this do CASCADE to delete all comments when the related post is going to be deleted:

ALTER TABLE comments ADD CONSTRAINT comments_ref_post_id FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE;

Of course, you can change this using the Schema Designer by clicking on the foreign key next to the post_id column in the comments table.