Writing queries with Query
¶
To fetch data from a database, Rel8 allows you to write SELECT
queries using
the Query
monad. While this monad might look a little different from
ordinary SQL, it is equal in expressivity, but due to Haskell’s do
notation
we get to benefit from all the means of abstraction available.
Understanding the Query
monad¶
Before we look at special functions for working with Query
, we’ll first take
a moment to understand how the Query
monad works. First, what does the type
Query a
mean? To be a Query a
means to be a SQL SELECT
query that
selects rows of type a
. Usually a
will be an instance of Table Expr
,
such as Expr Text
, or maybe BlogPostComment Expr
.
As Query
is an instance of Monad
means that we already have three
familiar APIs to work with: Functor
, Applicative
, and Monad
.
Functor Query
¶
The Functor
instance gives us access to fmap
, and its type is:
fmap :: (a -> b) -> Query a -> Query b
fmap
uniformly transforms rows of one type into rows of another type. In
SQL, this is corresponds to a projection. For example, if we have a Query
(User Expr)
, we might do fmap userId
to transform this into a Query
(Expr UserId)
.
Applicative Query
¶
The Applicative
instance for Query
gives us:
pure :: a -> Query a
(<*>) :: Query (a -> b) -> Query a -> Query b
pure
constructs a Query
that returns exactly one row - a row containing
the a
that was given. This might seem fairly pointless, but it’s an
important Query
when compared with (<*>)
. The <*>
combines two
Query
s by taking their cartesian product, followed by a projection that
combines each row into a new row.
One example of using the Applicative
operators is to combine two Query
s
into a tuple:
pure (,) <*> queryA <*> queryB
Monad Query
¶
The final type class to discuss is Monad Query
. Monad Query
has two
methods:
return :: a -> Query a
(>>=) :: Query a -> (a -> Query b) -> Query b
return
is the same pure
, so we won’t discuss this further. The much more
interesting operation is >>=
- commonly referred to as “bind”. This operator
allows you to extend a Query
with a new query. In SQL this is also similar
to a cartesian product, but uses the LATERAL
modifier to allow the second
query to refer to columns from the first.
This extension operator allows you to expand each row in the first query into zero, one, or many rows, according to the given function. For example, if we have a database of orders, we might write:
getAllOrders >>= \order -> getUserById (orderUserId order)
This Query
will return, for each Order
, the User
who placed that
order. In this case, this is a one-to-one relationship, so we get back exactly
as many rows as there are orders.
Going in the other direction, we have:
getAllUsers >>= \user -> getOrdersForUser (userId user)
This is a different query, as we start by fetching all User
s, and for each
user find all Order
s they have placed. This Query
has a different
cardinality, as we’re following a one-to-many relationship: any User
may
have zero, one, or many orders.
Haskell has special syntax for working with monads - do
notation. do
notation allows you to write these queries in a simpler form, where we don’t
have to introduce functions. Expanding on the latter query, we could write:
do user <- getAllUsers
order <- getOrdersForUser user
return (user, order)
Now we have a query that, for each User
, fetches all orders for that user.
The final return
means that for each User
and Order
, we’ll return a
single row.
Selecting rows from tables¶
With the more theoretical side of Query
considered, we can start looking at
the more pragmatic side, and how Query
can express some common SQL idioms.
First, one of the most common operations is to select all rows from a base
table. In SQL, this is a SELECT * FROM x
query, and in Rel8 we use each
with a TableSchema
.
Limit and offset¶
The SQL LIMIT
and OFFSET
keywords are available in Rel8 as limit
and offset
. Note that, like SQL, the order of these operations matters.
Usually, the correct thing to do is to first apply an offset with offset
,
and then use limit
to limit the number of rows returned:
limit n . offset m . orderBy anOrdering
These operations are similar to Haskell’s take
and drop
operations.
Filtering queries¶
Rel8 offers a few different ways to filter the rows selected by a query.
Perhaps the most familiar operation is to apply a WHERE
clause to a query.
In Rel8, this is done using where_
, which takes any Expr Bool
, and
returns rows where that Expr
is true. For example, to select all public
blog posts, we could write:
blogPost <- each blogPostSchema
where_ $ blogPostIsPublic blogPost
An alternative way to write WHERE
clauses is to use filter
. This
operator is similar to the guard
function in Control.Monad
, but also
returns the tested row. This allows us to easily chain a filtering operation on
a query. The above query could thus be written as:
blogPost <- filter blogPostIsPublic =<< each blogPostSchema
where_
and filter
allow you to filter rows based on an expression, but
sometimes we want to filter based on another query. For this, Rel8 offers
present
and absent
. For example, if all blog posts have a
list of tags, we could use present
to find all blog posts that have been
tagged as “Haskell”:
blogPost <- each blogPostSchema
present do
filter (("Haskell" ==.) . tagName) =<< tagFromBlogPost blogPost
Notice that this example uses present
with a query that itself uses
filter
. For each blog post, present
causes that row to be selected
only if the associated query finds a tag for that blog post with the tagName
“Haskell”.
Like filter
there is also a chaining variant of present
- with
.
We could rewrite the above query using with
as:
haskellBlogPost <-
each blogPostSchema >>=
with (filter (("Haskell" ==.) . tagName) <=< tagFromBlogPost)
Inner joins¶
Inner joins are SQL queries of the form SELECT .. FROM x JOIN y ON ..
. Rel8
doesn’t offer a special function for these queries, as the same query can be
expressed by selecting from two tables (this is called taking the cartesian
product of two queries) and then filtering the result.
If we wanted to join each blog post with the author of the blog post, we would write the SQL:
SELECT * FROM blog_post JOIN author ON author.id = blog_post.id
The alternative way to write this query with WHERE
is:
SELECT * FROM blog_post, author WHERE author.id = blog_post.id
and this query can be written in Rel8 as:
blogPost <- each blogPostSchema
author <- each authorSchema
where_ $ blogPostAuthorId blogPost ==. authorId author
Hint
A good pattern to adopt is to abstract out these joins as functions. A suggested way to write the above would be to extract out an “author for blog post” function:
blogPost <- each blogPostSchema
author <- authorForBlogPost blogPost
where:
authorForBlogPost :: BlogPost Expr -> Query (Author Expr)
authorForBlogPost blogPost =
filter ((blogPostAuthorId blogPost ==.) . authorId) =<<
each authorSchema
While this is a little more code over all, in our experience this style dramatically increases the readability of queries using joins.
Left (outer) joins with optional
¶
A left join is like an inner join, but allows for the possibility of the join to “fail”. You use left joins when you want to join optional information against a row.
In Rel8, a LEFT JOIN
is introduced by converting an inner join with
optional
. While this approach might seem a little foreign at first, it has a
strong similarity with the Control.Applicative.optional
function, and allows
you to reuse previous code.
To see an example of this, let’s assume that we want to get the latest comment for each blog post. Not all blog posts are popular though, so some blog posts might have no comment at all. To write this in Rel8, we could write:
blogPost <- each blogPostSchema
latestComment <-
optional $ limit 1 $
orderBy (commentCreatedAt >$< desc) $
commentsForBlogPost blogPost
optional
will transform a Query a
into a Query (MaybeTable a)
.
MaybeTable
is similar to the normal Maybe
data type in Haskell, and
represents the choice between a justTable x
and a nothingTable
(like
Just x
and Nothing
, respectively). When you execute a query containing
MaybeTable x
with select
, Rel8 will return Maybe x
. MaybeTable
comes with a library of routines, similar to the functions that can be used to
operate on Maybe
. For more details, see the API documentation.
Hint
optional
converts an inner join into a LEFT JOIN
, but you can also go
the other way - and turn a LEFT JOIN
back into an inner join! To do this,
you can use catMaybeTable
, which will select only the rows when the left
join was successful.
Ordering results¶
Rel8 supports ordering the results returned by a Query
, using SQL’s ORDER
BY
syntax. To specify an ordering, you use orderBy
and supply an
appropriate Order
value.
An Order
is built by combining the order of individual columns, each of
which can be either ascending or descending. To order a single column, you
combine asc
or desc
with Order
s contravariant interface. For
example, if we have a table with a orderId
column, we can order a Query
(Order Expr)
by orderId
with:
orderBy (orderId >$< asc)
To order by multiple columns, combine the individual orders with Order
s
Monoid
instance. We could extend the above example to order by the order
date first (with the most recent orders first) with:
orderBy (mconcat [orderDate >$< desc, orderId >$< asc])
Aggregating queries¶
Todo
Write this
Set operations¶
Todo
Write this