| Safe Haskell | None |
|---|---|
| Language | Haskell2010 |
Database.Esqueleto.Experimental.From.CommonTableExpression
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
Constructors
| UnionKind | |
Fields | |