esqueleto-3.5.11.2: Type-safe EDSL for SQL queries on persistent backends.

Safe Haskell | Safe-Inferred
Language | Haskell2010

Database.Esqueleto.Experimental.From.CommonTableExpression

Synopsis

- with :: (ToAlias a, ToAliasReference a, SqlSelect a r) => SqlQuery a -> SqlQuery (From a)
- withRecursive :: (ToAlias a, ToAliasReference a, SqlSelect a r) => SqlQuery a -> UnionKind -> (From a -> SqlQuery a) -> SqlQuery (From a)
- newtype UnionKind = UnionKind {
  - unUnionKind :: Builder
  }

# Documentation

with :: (ToAlias a, ToAliasReference a, SqlSelect a r) => SqlQuery a -> SqlQuery (From a) Source #

`WITH` clause used to introduce a Common Table Expression (CTE). CTEs are supported in most modern SQL engines and can be useful in performance tuning. In Esqueleto, CTEs should be used as a subquery memoization tactic. When writing plain SQL, CTEs are sometimes used to organize the SQL code, in Esqueleto, this is better achieved through function that return `SqlQuery` values.

    select $ do
    cte <- with subQuery
    cteResult <- from cte
    where_ $ cteResult ...
    pure cteResult

WARNING: In some SQL engines using a CTE can diminish performance. In these engines the CTE is treated as an optimization fence. You should always verify that using a CTE will in fact improve your performance over a regular subquery.

Notably, in PostgreSQL prior to version 12, CTEs are always fully calculated, which can potentially significantly pessimize queries. As of PostgreSQL 12, non-recursive and side-effect-free queries may be inlined and optimized accordingly if not declared `MATERIALIZED` to get the previous behaviour. See the PostgreSQL CTE documentation, section Materialization, for more information.

Since: 3.4.0.0

withRecursive :: (ToAlias a, ToAliasReference a, SqlSelect a r) => SqlQuery a -> UnionKind -> (From a -> SqlQuery a) -> SqlQuery (From a) Source #

`WITH` `RECURSIVE` allows one to make a recursive subquery, which can reference itself. Like `WITH`, this is supported in most modern SQL engines. Useful for hierarchical, self-referential data, like a tree of data.

    select $ do
    cte <- withRecursive
             (do
                 person <- from $ table @Person
                 where_ $ person ^. PersonId ==. val personId
                 pure person
             )
             unionAll_
             (\self -> do
                 (p :& f :& p2 :& pSelf) <- from self
                          `innerJoin` $ table @Follow
                          `on` (\(p :& f) ->
                                  p ^. PersonId ==. f ^. FollowFollower)
                          `innerJoin` $ table @Person
                          `on` (\(p :& f :& p2) ->
                                  f ^. FollowFollowed ==. p2 ^. PersonId)
                          `leftJoin` self
                          `on` (\(_ :& _ :& p2 :& pSelf) ->
                                  just (p2 ^. PersonId) ==. pSelf ?. PersonId)
                 where_ $ isNothing (pSelf ?. PersonId)
                 groupBy (p2 ^. PersonId)
                 pure p2
             )
    from cte

Since: 3.4.0.0

newtype UnionKind Source #

Constructors

UnionKind |

Fields

- unUnionKind :: Builder

#### Instances

Instances details

UnionAll_ UnionKind Source # |
Instance details Defined in Database.Esqueleto.Experimental.From.CommonTableExpression Methods unionAll_ :: UnionKind Source #
Union_ UnionKind Source # |
Instance details Defined in Database.Esqueleto.Experimental.From.CommonTableExpression Methods union_ :: UnionKind Source #