git.iko.soy/rel8
on 2ba99ea help sign in
rel8/docs/cookbook.rst
167 lines 4.1 kB history raw
1Cookbook
2========
4This cookbook exists to help you easily form Rel8 queries. It's main purpose is
5to help those familiar with SQL to translate their queries into Rel8
6``Query``\s.
8``SELECT * FROM table``
9-----------------------
11To select from a table, use ``each``.
13Inner joins
14-----------
16To perform an inner join against two queries, use ``where_`` with a join
17condition. For example, the following SQL:
19.. code-block:: sql
21 SELECT * FROM table_a JOIN table_b ON table_a.x = table_b.y
23can be written as::
25 myQuery = do
26 a <- each tableA
27 b <- each tableB
28 where_ $ tableAX a ==. tableBY b
30Left (outer) joins
31------------------
33A ``LEFT JOIN`` query is performed by using ``optional``.
35For example, the query:
37.. code-block:: sql
39 SELECT * FROM table_a LEFT JOIN table_b ON table_a.x = table_b.y
41can be written as::
43 myQuery = do
44 a <- each tableA
46 maybeB <- optional do
47 b <- each tableB
48 where_ $ tableAX a ==. tableBY b
50 return (a, maybeB)
52Note that ``maybeB`` here will be a ``MaybeTable``, which is the Rel8
53``Query``-equivalent of ``Maybe``. This allows you to observe if a left join
54has succeeded or failed.
56Ordering results
57----------------
59A ``Query`` by default has no ordering - just like in SQL. If you rows back in
60a certain order, you can use ``orderBy`` with an ``Order``.
62For example, the query:
64.. code-block:: sql
66 SELECT * FROM my_table ORDER BY my_table.id ASC, my_table.x DESC NULLS FIRST
68can be written as::
70 myQuery =
71 orderBy (mconcat [ myTableId >$< asc, myTableX >$< nullsFirst desc ]) $
72 each myTableSchema
74Note that we use ``>$<`` (from ``Data.Functor.Contravariant``) to select table
75columns, and we can use `mconcat` to combine orderings.
77If all columns of a table have an ordering, you can also use ``ascTable`` and
78``descTable``. For example::
80 myQuery = orderBy ascTable $ each myTableSchema
82Aggregations
83------------
85Aggregations in Rel8 work by using ``aggregate``, which takes a ``Query
86(Aggregate a)`` and gives you back a ``Query a``.
88The following query:
90.. code-block:: sql
92 SELECT sum(foo), count(distinct bar) FROM table_a
94can be written as::
96 myQuery = aggregate do
97 a <- each tableA
98 return $ liftF2 (,) (sum (foo a)) (countDistinct (bar a))
100where ``liftF2`` comes from ``Data.Functor.Apply`` from the ``semigroupoids``
101library.
103Combining aggregations
104----------------------
106As ``Aggregate`` is an instance of ``Apply`` (which is very similar to
107``Applicative``), individual aggregations can be combined. For example, one way
108to take the average rating would be to write the query:
110.. code-block:: sql
112 SELECT sum(rating.score) / count(rating.score) FROM rating
114In Rel8, we can write this as::
116 myQuery = aggregate do
117 rating <- each ratingSchema
118 return $ liftF2 (/) (sum (score rating)) (count (score rating))
120You can also use ``RebindableSyntax`` and ``ApplicativeDo``::
122 {-# language ApplicativeDo, RebindableSyntax #-}
124 import Data.Functor.Apply ((<.>))
126 myQuery = aggregate do
127 rating <- each ratingSchema
128 return do
129 scoreSum <- sum (score rating)
130 numberOfRatings <- count (score rating)
131 return (scoreSum / numberOfRatings)
132 where (<*>) = (<.>)
134For large aggregations, this can often make queries easier to read.
136Tree-like queries
137-----------------
139Rel8 has a fairly unique feature in that it's able to return not just lists of
140rows, but can also return *tree*\s.
142To understand what this means, we'll consider a small example query for blog
143posts. We want our query to return:
1451. The latest 5 blog posts.
1462. For each blog post, all tags.
1473. For each blog post, the latest 3 comments.
149In Rel8, we can write this query as::
151 latestBlogPosts = do
152 post <- each postSchema
154 tags <- aggregate do
155 tag <- each tagSchema
156 where_ (tagPostId tag ==. postId post)
157 return (listAgg (tagName tag))
159 latestComments <-
160 many $
161 limit 3 $
162 orderBy (commentCreatedAt >$< desc) do
163 comment <- each commentSchema
164 where_ (commentPostId comment ==. postId post)
166 return (post, tags, latestComments)