Database.Esqueleto.Internal.Language

This is an internal module, anything exported by this module may change without a major version bump. Please use only Database.Esqueleto if possible.

The pretty face

from :: From a => (a -> SqlQuery b) -> SqlQuery b

FROM clause: bring entities into scope.

This function internally uses two type classes in order to provide some flexibility of how you may call it. Internally we refer to these type classes as the two different magics.

The innermost magic allows you to use from with the following types:

• expr (Entity val), which brings a single entity into scope.
• expr (Maybe (Entity val)), which brings a single entity that may be NULL into scope. Used for OUTER JOINs.
• A JOIN of any other two types allowed by the innermost magic, where a JOIN may be an InnerJoin, a CrossJoin, a LeftOuterJoin, a RightOuterJoin, or a FullOuterJoin. The JOINs have left fixity.

The outermost magic allows you to use from on any tuples of types supported by innermost magic (and also tuples of tuples, and so on), up to 8-tuples.

Note that using from for the same entity twice does work and corresponds to a self-join. You don't even need to use two different calls to from, you may use a JOIN or a tuple.

The following are valid examples of uses of from (the types of the arguments of the lambda are inside square brackets):

from $ \person -> ...
from $ \(person, blogPost) -> ...
from $ \(p `LeftOuterJoin` mb) -> ...
from $ \(p1 `InnerJoin` f `InnerJoin` p2) -> ...
from $ \((p1 `InnerJoin` f) `InnerJoin` p2) -> ...

The types of the arguments to the lambdas above are, respectively:

person
  :: ( Esqueleto query expr backend
     , PersistEntity Person
     , PersistEntityBackend Person ~ backend
     ) => expr (Entity Person)
(person, blogPost)
  :: (...) => (expr (Entity Person), expr (Entity BlogPost))
(p `LeftOuterJoin` mb)
  :: (...) => InnerJoin (expr (Entity Person)) (expr (Maybe (Entity BlogPost)))
(p1 `InnerJoin` f `InnerJoin` p2)
  :: (...) => InnerJoin
                (InnerJoin (expr (Entity Person))
                           (expr (Entity Follow)))
                (expr (Entity Person))
(p1 `InnerJoin` (f `InnerJoin` p2)) ::
  :: (...) => InnerJoin
                (expr (Entity Person))
                (InnerJoin (expr (Entity Follow))
                           (expr (Entity Person)))

Note that some backends may not support all kinds of JOINs.

newtype Value a

A single value (as opposed to a whole entity). You may use (^.) or (?.) to get a Value from an Entity.

Constructors

Value

Fields

• unValue :: a

Instances

Monad Value

Defined in Database.Esqueleto.Internal.Internal

Methods

(>>=) :: Value a -> (a -> Value b) -> Value b

(>>) :: Value a -> Value b -> Value b

return :: a -> Value a

fail :: String -> Value a

Functor Value

Since: 1.4.4

Defined in Database.Esqueleto.Internal.Internal

Methods

fmap :: (a -> b) -> Value a -> Value b

(<$) :: a -> Value b -> Value a

Applicative Value

Defined in Database.Esqueleto.Internal.Internal

Methods

pure :: a -> Value a

(<*>) :: Value (a -> b) -> Value a -> Value b

liftA2 :: (a -> b -> c) -> Value a -> Value b -> Value c

(*>) :: Value a -> Value b -> Value b

(<*) :: Value a -> Value b -> Value a

Eq a => Eq (Value a)

Defined in Database.Esqueleto.Internal.Internal

Methods

(==) :: Value a -> Value a -> Bool

(/=) :: Value a -> Value a -> Bool

Ord a => Ord (Value a)

Defined in Database.Esqueleto.Internal.Internal

Methods

compare :: Value a -> Value a -> Ordering

(<) :: Value a -> Value a -> Bool

(<=) :: Value a -> Value a -> Bool

(>) :: Value a -> Value a -> Bool

(>=) :: Value a -> Value a -> Bool

max :: Value a -> Value a -> Value a

min :: Value a -> Value a -> Value a

Show a => Show (Value a)

Defined in Database.Esqueleto.Internal.Internal

Methods

showsPrec :: Int -> Value a -> ShowS

show :: Value a -> String

showList :: [Value a] -> ShowS

ToSomeValues (SqlExpr (Value a))

Defined in Database.Esqueleto.Internal.Internal

Methods

toSomeValues :: SqlExpr (Value a) -> [SomeValue]

PersistField a => SqlSelect (SqlExpr (Value a)) (Value a)

You may return any single value (i.e. a single column) from a select query.

Defined in Database.Esqueleto.Internal.Internal

Methods

sqlSelectCols :: IdentInfo -> SqlExpr (Value a) -> (Builder, [PersistValue])

sqlSelectColCount :: Proxy (SqlExpr (Value a)) -> Int

sqlSelectProcessRow :: [PersistValue] -> Either Text (Value a)

sqlInsertInto :: IdentInfo -> SqlExpr (Value a) -> (Builder, [PersistValue])

newtype ValueList a

A list of single values. There's a limited set of functions able to work with this data type (such as subList_select, valList, in_ and exists).

Constructors

ValueList a

Instances

Eq a => Eq (ValueList a)

Defined in Database.Esqueleto.Internal.Internal

Methods

(==) :: ValueList a -> ValueList a -> Bool

(/=) :: ValueList a -> ValueList a -> Bool

Ord a => Ord (ValueList a)

Defined in Database.Esqueleto.Internal.Internal

Methods

compare :: ValueList a -> ValueList a -> Ordering

(<) :: ValueList a -> ValueList a -> Bool

(<=) :: ValueList a -> ValueList a -> Bool

(>) :: ValueList a -> ValueList a -> Bool

(>=) :: ValueList a -> ValueList a -> Bool

max :: ValueList a -> ValueList a -> ValueList a

min :: ValueList a -> ValueList a -> ValueList a

Show a => Show (ValueList a)

Defined in Database.Esqueleto.Internal.Internal

Methods

showsPrec :: Int -> ValueList a -> ShowS

show :: ValueList a -> String

showList :: [ValueList a] -> ShowS

data SomeValue where

A wrapper type for for any expr (Value a) for all a.

Constructors

SomeValue :: SqlExpr (Value a) -> SomeValue

class ToSomeValues a where

A class of things that can be converted into a list of SomeValue. It has instances for tuples and is the reason why groupBy can take tuples, like groupBy (foo ^. FooId, foo ^. FooName, foo ^. FooType).

Methods

toSomeValues :: a -> [SomeValue]

Instances

ToSomeValues (SqlExpr (Value a))

Defined in Database.Esqueleto.Internal.Internal

Methods

toSomeValues :: SqlExpr (Value a) -> [SomeValue]

(ToSomeValues a, ToSomeValues b) => ToSomeValues (a, b)

Defined in Database.Esqueleto.Internal.Internal

Methods

toSomeValues :: (a, b) -> [SomeValue]

(ToSomeValues a, ToSomeValues b, ToSomeValues c) => ToSomeValues (a, b, c)

Defined in Database.Esqueleto.Internal.Internal

Methods

toSomeValues :: (a, b, c) -> [SomeValue]

(ToSomeValues a, ToSomeValues b, ToSomeValues c, ToSomeValues d) => ToSomeValues (a, b, c, d)

Defined in Database.Esqueleto.Internal.Internal

Methods

toSomeValues :: (a, b, c, d) -> [SomeValue]

(ToSomeValues a, ToSomeValues b, ToSomeValues c, ToSomeValues d, ToSomeValues e) => ToSomeValues (a, b, c, d, e)

Defined in Database.Esqueleto.Internal.Internal

Methods

toSomeValues :: (a, b, c, d, e) -> [SomeValue]

(ToSomeValues a, ToSomeValues b, ToSomeValues c, ToSomeValues d, ToSomeValues e, ToSomeValues f) => ToSomeValues (a, b, c, d, e, f)

Defined in Database.Esqueleto.Internal.Internal

Methods

toSomeValues :: (a, b, c, d, e, f) -> [SomeValue]

(ToSomeValues a, ToSomeValues b, ToSomeValues c, ToSomeValues d, ToSomeValues e, ToSomeValues f, ToSomeValues g) => ToSomeValues (a, b, c, d, e, f, g)

Defined in Database.Esqueleto.Internal.Internal

Methods

toSomeValues :: (a, b, c, d, e, f, g) -> [SomeValue]

(ToSomeValues a, ToSomeValues b, ToSomeValues c, ToSomeValues d, ToSomeValues e, ToSomeValues f, ToSomeValues g, ToSomeValues h) => ToSomeValues (a, b, c, d, e, f, g, h)

Defined in Database.Esqueleto.Internal.Internal

Methods

toSomeValues :: (a, b, c, d, e, f, g, h) -> [SomeValue]

data InnerJoin a b infixl 2

Data type that represents an INNER JOIN (see LeftOuterJoin for an example).

Constructors

a `InnerJoin` b infixl 2

Instances

IsJoinKind InnerJoin

Defined in Database.Esqueleto.Internal.Internal

Methods

smartJoin :: a -> b -> InnerJoin a b

reifyJoinKind :: InnerJoin a b -> JoinKind

FromPreprocess (InnerJoin a b) => From (InnerJoin a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (InnerJoin a b)

data CrossJoin a b infixl 2

Data type that represents a CROSS JOIN (see LeftOuterJoin for an example).

Constructors

a `CrossJoin` b infixl 2

Instances

IsJoinKind CrossJoin

Defined in Database.Esqueleto.Internal.Internal

Methods

smartJoin :: a -> b -> CrossJoin a b

reifyJoinKind :: CrossJoin a b -> JoinKind

FromPreprocess (CrossJoin a b) => From (CrossJoin a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (CrossJoin a b)

data LeftOuterJoin a b infixl 2

Data type that represents a LEFT OUTER JOIN. For example,

select $
from $ \(person `LeftOuterJoin` pet) ->
  ...

is translated into

SELECT ...
FROM Person LEFT OUTER JOIN Pet
...

See also: from.

Constructors

a `LeftOuterJoin` b infixl 2

Instances

IsJoinKind LeftOuterJoin

Defined in Database.Esqueleto.Internal.Internal

Methods

smartJoin :: a -> b -> LeftOuterJoin a b

reifyJoinKind :: LeftOuterJoin a b -> JoinKind

FromPreprocess (LeftOuterJoin a b) => From (LeftOuterJoin a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (LeftOuterJoin a b)

data RightOuterJoin a b infixl 2

Data type that represents a RIGHT OUTER JOIN (see LeftOuterJoin for an example).

Constructors

a `RightOuterJoin` b infixl 2

Instances

IsJoinKind RightOuterJoin

Defined in Database.Esqueleto.Internal.Internal

Methods

smartJoin :: a -> b -> RightOuterJoin a b

reifyJoinKind :: RightOuterJoin a b -> JoinKind

FromPreprocess (RightOuterJoin a b) => From (RightOuterJoin a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (RightOuterJoin a b)

data FullOuterJoin a b infixl 2

Data type that represents a FULL OUTER JOIN (see LeftOuterJoin for an example).

Constructors

a `FullOuterJoin` b infixl 2

Instances

IsJoinKind FullOuterJoin

Defined in Database.Esqueleto.Internal.Internal

Methods

smartJoin :: a -> b -> FullOuterJoin a b

reifyJoinKind :: FullOuterJoin a b -> JoinKind

FromPreprocess (FullOuterJoin a b) => From (FullOuterJoin a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (FullOuterJoin a b)

data OnClauseWithoutMatchingJoinException

Exception thrown whenever on is used to create an ON clause but no matching JOIN is found.

Constructors

OnClauseWithoutMatchingJoinException String

Instances

Eq OnClauseWithoutMatchingJoinException

Defined in Database.Esqueleto.Internal.Internal

Methods

(==) :: OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException -> Bool

(/=) :: OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException -> Bool

Ord OnClauseWithoutMatchingJoinException

Defined in Database.Esqueleto.Internal.Internal

Methods

compare :: OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException -> Ordering

(<) :: OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException -> Bool

(<=) :: OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException -> Bool

(>) :: OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException -> Bool

(>=) :: OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException -> Bool

max :: OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException

min :: OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException -> OnClauseWithoutMatchingJoinException

Show OnClauseWithoutMatchingJoinException

Defined in Database.Esqueleto.Internal.Internal

Methods

showsPrec :: Int -> OnClauseWithoutMatchingJoinException -> ShowS

show :: OnClauseWithoutMatchingJoinException -> String

showList :: [OnClauseWithoutMatchingJoinException] -> ShowS

Exception OnClauseWithoutMatchingJoinException

Defined in Database.Esqueleto.Internal.Internal

Methods

toException :: OnClauseWithoutMatchingJoinException -> SomeException

fromException :: SomeException -> Maybe OnClauseWithoutMatchingJoinException

displayException :: OnClauseWithoutMatchingJoinException -> String

data OrderBy

Phantom type used by orderBy, asc and desc.

data DistinctOn

Phantom type used by distinctOn and don.

data Update typ

Phantom type for a SET operation on an entity of the given type (see set and '(=.)').

data Insertion a

Phantom type used by insertSelect.

data LockingKind

Different kinds of locking clauses supported by locking.

Note that each RDBMS has different locking support. The constructors of this datatype specify only the syntax of the locking mechanism, not its semantics. For example, even though both MySQL and PostgreSQL support ForUpdate, there are no guarantees that they will behave the same.

Since: 2.2.7

Constructors

ForUpdate

FOR UPDATE syntax. Supported by MySQL, Oracle and PostgreSQL.

Since: 2.2.7

ForUpdateSkipLocked

FOR UPDATE SKIP LOCKED syntax. Supported by MySQL, Oracle and PostgreSQL.

Since: 2.2.7

ForShare

FOR SHARE syntax. Supported by PostgreSQL.

Since: 2.2.7

LockInShareMode

LOCK IN SHARE MODE syntax. Supported by MySQL.

Since: 2.2.7

class PersistField a => SqlString a

Phantom class of data types that are treated as strings by the RDBMS. It has no methods because it's only used to avoid type errors such as trying to concatenate integers.

If you have a custom data type or newtype, feel free to make it an instance of this class.

Since: 2.4.0

Instances

SqlString ByteString

Since: 2.3.0

Defined in Database.Esqueleto.Internal.Internal

SqlString Text

Since: 2.3.0

Defined in Database.Esqueleto.Internal.Internal

SqlString Text

Since: 2.3.0

Defined in Database.Esqueleto.Internal.Internal

SqlString Html

Since: 2.3.0

Defined in Database.Esqueleto.Internal.Internal

a ~ Char => SqlString [a]

Since: 2.3.0

Defined in Database.Esqueleto.Internal.Internal

SqlString a => SqlString (Maybe a)

Since: 2.4.0

Defined in Database.Esqueleto.Internal.Internal

class ToBaseId ent where

Class that enables one to use toBaseId to convert an entity's key on a query into another (cf. toBaseId).

Associated Types

type BaseEnt ent :: *

Methods

toBaseIdWitness :: Key (BaseEnt ent) -> Key ent

The guts

data JoinKind

(Internal) A kind of JOIN.

Constructors

InnerJoinKind

INNER JOIN

CrossJoinKind

CROSS JOIN

LeftOuterJoinKind

LEFT OUTER JOIN

RightOuterJoinKind

RIGHT OUTER JOIN

FullOuterJoinKind

FULL OUTER JOIN

Instances

Eq JoinKind

Defined in Database.Esqueleto.Internal.Internal

Methods

(==) :: JoinKind -> JoinKind -> Bool

(/=) :: JoinKind -> JoinKind -> Bool

Show JoinKind

Defined in Database.Esqueleto.Internal.Internal

Methods

showsPrec :: Int -> JoinKind -> ShowS

show :: JoinKind -> String

showList :: [JoinKind] -> ShowS

class IsJoinKind join where

(Internal) Functions that operate on types (that should be) of kind JoinKind.

Methods

smartJoin :: a -> b -> join a b

(Internal) smartJoin a b is a JOIN of the correct kind.

reifyJoinKind :: join a b -> JoinKind

(Internal) Reify a JoinKind from a JOIN. This function is non-strict.

Instances

IsJoinKind FullOuterJoin

Defined in Database.Esqueleto.Internal.Internal

Methods

smartJoin :: a -> b -> FullOuterJoin a b

reifyJoinKind :: FullOuterJoin a b -> JoinKind

IsJoinKind RightOuterJoin

Defined in Database.Esqueleto.Internal.Internal

Methods

smartJoin :: a -> b -> RightOuterJoin a b

reifyJoinKind :: RightOuterJoin a b -> JoinKind

IsJoinKind LeftOuterJoin

Defined in Database.Esqueleto.Internal.Internal

Methods

smartJoin :: a -> b -> LeftOuterJoin a b

reifyJoinKind :: LeftOuterJoin a b -> JoinKind

IsJoinKind CrossJoin

Defined in Database.Esqueleto.Internal.Internal

Methods

smartJoin :: a -> b -> CrossJoin a b

reifyJoinKind :: CrossJoin a b -> JoinKind

IsJoinKind InnerJoin

Defined in Database.Esqueleto.Internal.Internal

Methods

smartJoin :: a -> b -> InnerJoin a b

reifyJoinKind :: InnerJoin a b -> JoinKind

class BackendCompatible sup sub where

This class witnesses that two backend are compatible, and that you can convert from the sub backend into the sup backend. This is similar to the HasPersistBackend and IsPersistBackend classes, but where you don't want to fix the type associated with the PersistEntityBackend of a record.

Generally speaking, where you might have:

foo ::
  ( PersistEntity record
  , PeristEntityBackend record ~ BaseBackend backend
  , IsSqlBackend backend
  )

this can be replaced with:

foo ::
  ( PersistEntity record,
  , PersistEntityBackend record ~ backend
  , BackendCompatible SqlBackend backend
  )

This works for SqlReadBackend because of the instance BackendCompatible SqlBackend SqlReadBackend, without needing to go through the BaseBackend type family.

Likewise, functions that are currently hardcoded to use SqlBackend can be generalized:

-- before:
asdf :: ReaderT SqlBackend m ()
asdf = pure ()

-- after:
asdf' :: BackendCompatible SqlBackend backend => ReaderT backend m ()
asdf' = withReaderT projectBackend asdf

Since: persistent-2.7.1

Methods

projectBackend :: sub -> sup

data PreprocessedFrom a

(Internal) Phantom type used to process from (see fromStart).

class From a

(Internal) Class that implements the tuple from magic (see fromStart).

Minimal complete definition

from_

Instances

FromPreprocess (SqlExpr (Maybe (Entity val))) => From (SqlExpr (Maybe (Entity val)))

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (SqlExpr (Maybe (Entity val)))

FromPreprocess (SqlExpr (Entity val)) => From (SqlExpr (Entity val))

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (SqlExpr (Entity val))

(From a, From b) => From (a, b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (a, b)

FromPreprocess (FullOuterJoin a b) => From (FullOuterJoin a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (FullOuterJoin a b)

FromPreprocess (RightOuterJoin a b) => From (RightOuterJoin a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (RightOuterJoin a b)

FromPreprocess (LeftOuterJoin a b) => From (LeftOuterJoin a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (LeftOuterJoin a b)

FromPreprocess (CrossJoin a b) => From (CrossJoin a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (CrossJoin a b)

FromPreprocess (InnerJoin a b) => From (InnerJoin a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (InnerJoin a b)

(From a, From b, From c) => From (a, b, c)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (a, b, c)

(From a, From b, From c, From d) => From (a, b, c, d)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (a, b, c, d)

(From a, From b, From c, From d, From e) => From (a, b, c, d, e)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (a, b, c, d, e)

(From a, From b, From c, From d, From e, From f) => From (a, b, c, d, e, f)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (a, b, c, d, e, f)

(From a, From b, From c, From d, From e, From f, From g) => From (a, b, c, d, e, f, g)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (a, b, c, d, e, f, g)

(From a, From b, From c, From d, From e, From f, From g, From h) => From (a, b, c, d, e, f, g, h)

Defined in Database.Esqueleto.Internal.Internal

Methods

from_ :: SqlQuery (a, b, c, d, e, f, g, h)

class FromPreprocess a

(Internal) Class that implements the JOIN from magic (see fromStart).

Minimal complete definition

fromPreprocess

Instances

(PersistEntity val, BackendCompatible SqlBackend (PersistEntityBackend val)) => FromPreprocess (SqlExpr (Maybe (Entity val)))

Defined in Database.Esqueleto.Internal.Internal

Methods

fromPreprocess :: SqlQuery (SqlExpr (PreprocessedFrom (SqlExpr (Maybe (Entity val)))))

(PersistEntity val, BackendCompatible SqlBackend (PersistEntityBackend val)) => FromPreprocess (SqlExpr (Entity val))

Defined in Database.Esqueleto.Internal.Internal

Methods

fromPreprocess :: SqlQuery (SqlExpr (PreprocessedFrom (SqlExpr (Entity val))))

(FromPreprocess a, FromPreprocess b, IsJoinKind join) => FromPreprocess (join a b)

Defined in Database.Esqueleto.Internal.Internal

Methods

fromPreprocess :: SqlQuery (SqlExpr (PreprocessedFrom (join a b)))

when_ :: expr (Value Bool) -> () -> expr a -> (expr (Value Bool), expr a)

Syntax sugar for case_.

Since: 2.1.2

then_ :: ()

Syntax sugar for case_.

Since: 2.1.2

else_ :: expr a -> expr a

Syntax sugar for case_.

Since: 2.1.2

where_ :: SqlExpr (Value Bool) -> SqlQuery ()

WHERE clause: restrict the query's result.

on :: SqlExpr (Value Bool) -> SqlQuery ()

An ON clause, useful to describe how two tables are related. Cross joins and tuple-joins do not need an on clause, but InnerJoin and the various outer joins do.

If you don't include an on clause (or include too many!) then a runtime exception will be thrown.

As an example, consider this simple join:

select $
from $ \(foo `InnerJoin` bar) -> do
  on (foo ^. FooId ==. bar ^. BarFooId)
  ...

We need to specify the clause for joining the two columns together. If we had this:

select $
from $ \(foo `CrossJoin` bar) -> do
  ...

Then we can safely omit the on clause, because the cross join will make pairs of all records possible.

You can do multiple on clauses in a query. This query joins three tables, and has two on clauses:

select $
from $ \(foo `InnerJoin` bar `InnerJoin` baz) -> do
  on (baz ^. BazId ==. bar ^. BarBazId)
  on (foo ^. FooId ==. bar ^. BarFooId)
  ...

Old versions of esqueleto required that you provide the on clauses in reverse order. This restriction has been lifted - you can now provide on clauses in any order, and the SQL should work itself out. The above query is now totally equivalent to this:

select $
from $ \(foo `InnerJoin` bar `InnerJoin` baz) -> do
  on (foo ^. FooId ==. bar ^. BarFooId)
  on (baz ^. BazId ==. bar ^. BarBazId)
  ...

groupBy :: ToSomeValues a => a -> SqlQuery ()

GROUP BY clause. You can enclose multiple columns in a tuple.

select $ from \(foo `InnerJoin` bar) -> do
  on (foo ^. FooBarId ==. bar ^. BarId)
  groupBy (bar ^. BarId, bar ^. BarName)
  return (bar ^. BarId, bar ^. BarName, countRows)

With groupBy you can sort by aggregate functions, like so (we used let to restrict the more general countRows to SqlSqlExpr (Value Int) to avoid ambiguity---the second use of countRows has its type restricted by the :: Int below):

r <- select $ from \(foo `InnerJoin` bar) -> do
  on (foo ^. FooBarId ==. bar ^. BarId)
  groupBy $ bar ^. BarName
  let countRows' = countRows
  orderBy [asc countRows']
  return (bar ^. BarName, countRows')
forM_ r $ \(Value name, Value count) -> do
  print name
  print (count :: Int)

Need more columns?

The ToSomeValues class is defined for SqlExpr and tuples of SqlExprs. We only have definitions for up to 8 elements in a tuple right now, so it's possible that you may need to have more than 8 elements.

For example, consider a query with a groupBy call like this:

groupBy (e0, e1, e2, e3, e4, e5, e6, e7)

This is the biggest you can get with a single tuple. However, you can easily nest the tuples to add more:

groupBy ((e0, e1, e2, e3, e4, e5, e6, e7), e8, e9)

orderBy :: [SqlExpr OrderBy] -> SqlQuery ()

ORDER BY clause. See also asc and desc.

Multiple calls to orderBy get concatenated on the final query, including distinctOnOrderBy.

rand :: SqlExpr OrderBy

Deprecated: Since 2.6.0: rand ordering function is not uniform across all databases! To avoid accidental partiality it will be removed in the next major version.

ORDER BY random() clause.

Since: 1.3.10

asc :: PersistField a => SqlExpr (Value a) -> SqlExpr OrderBy

Ascending order of this field or SqlExpression.

desc :: PersistField a => SqlExpr (Value a) -> SqlExpr OrderBy

Descending order of this field or SqlExpression.

limit :: Int64 -> SqlQuery ()

LIMIT. Limit the number of returned rows.

offset :: Int64 -> SqlQuery ()

OFFSET. Usually used with limit.

distinct :: SqlQuery a -> SqlQuery a

DISTINCT. Change the current SELECT into SELECT DISTINCT. For example:

select $ distinct $
  from \foo -> do
  ...

Note that this also has the same effect:

select $
  from \foo -> do
  distinct (return ())
  ...

Since: 2.2.4

distinctOn :: [SqlExpr DistinctOn] -> SqlQuery a -> SqlQuery a

DISTINCT ON. Change the current SELECT into SELECT DISTINCT ON (SqlExpressions). For example:

select $
  from \foo ->
  distinctOn [don (foo ^. FooName), don (foo ^. FooState)] $ do
  ...

You can also chain different calls to distinctOn. The above is equivalent to:

select $
  from \foo ->
  distinctOn [don (foo ^. FooName)] $
  distinctOn [don (foo ^. FooState)] $ do
  ...

Each call to distinctOn adds more SqlExpressions. Calls to distinctOn override any calls to distinct.

Note that PostgreSQL requires the SqlExpressions on DISTINCT ON to be the first ones to appear on a ORDER BY. This is not managed automatically by esqueleto, keeping its spirit of trying to be close to raw SQL.

Supported by PostgreSQL only.

Since: 2.2.4

don :: SqlExpr (Value a) -> SqlExpr DistinctOn

Erase an SqlExpression's type so that it's suitable to be used by distinctOn.

Since: 2.2.4

distinctOnOrderBy :: [SqlExpr OrderBy] -> SqlQuery a -> SqlQuery a

A convenience function that calls both distinctOn and orderBy. In other words,

distinctOnOrderBy [asc foo, desc bar, desc quux] $ do
  ...

is the same as:

distinctOn [don foo, don  bar, don  quux] $ do
  orderBy  [asc foo, desc bar, desc quux]
  ...

Since: 2.2.4

having :: SqlExpr (Value Bool) -> SqlQuery ()

HAVING.

Since: 1.2.2

locking :: LockingKind -> SqlQuery ()

Add a locking clause to the query. Please read LockingKind documentation and your RDBMS manual.

If multiple calls to locking are made on the same query, the last one is used.

Since: 2.2.7

sub_select :: PersistField a => SqlQuery (SqlExpr (Value a)) -> SqlExpr (Value a)

Deprecated: sub_select sub_select is an unsafe function to use. If used with a SqlQuery that returns 0 results, then it may return NULL despite not mentioning Maybe in the return type. If it returns more than 1 result, then it will throw a SQL error. Instead, consider using one of the following alternatives: - subSelect: attaches a LIMIT 1 and the Maybe return type, totally safe. - subSelectMaybe: Attaches a LIMIT 1, useful for a query that already has a Maybe in the return type. - subSelectCount: Performs a count of the query - this is always safe. - subSelectUnsafe: Performs no checks or guarantees. Safe to use with countRows and friends.

Execute a subquery SELECT in an SqlExpression. Returns a simple value so should be used only when the SELECT query is guaranteed to return just one row.

Deprecated in 3.2.0.

(^.) :: forall typ val. (PersistEntity val, PersistField typ) => SqlExpr (Entity val) -> EntityField val typ -> SqlExpr (Value typ) infixl 9

Project a field of an entity.

(?.) :: (PersistEntity val, PersistField typ) => SqlExpr (Maybe (Entity val)) -> EntityField val typ -> SqlExpr (Value (Maybe typ))

Project a field of an entity that may be null.

val :: PersistField typ => typ -> SqlExpr (Value typ)

Lift a constant value from Haskell-land to the query.

isNothing :: PersistField typ => SqlExpr (Value (Maybe typ)) -> SqlExpr (Value Bool)

IS NULL comparison.

just :: SqlExpr (Value typ) -> SqlExpr (Value (Maybe typ))

Analogous to Just, promotes a value of type typ into one of type Maybe typ. It should hold that val . Just === just . val.

nothing :: SqlExpr (Value (Maybe typ))

NULL value.

joinV :: SqlExpr (Value (Maybe (Maybe typ))) -> SqlExpr (Value (Maybe typ))

Join nested Maybes in a Value into one. This is useful when calling aggregate functions on nullable fields.

withNonNull :: PersistField typ => SqlExpr (Value (Maybe typ)) -> (SqlExpr (Value typ) -> SqlQuery a) -> SqlQuery a

Project an SqlExpression that may be null, guarding against null cases.

countRows :: Num a => SqlExpr (Value a)

COUNT(*) value.

count :: Num a => SqlExpr (Value typ) -> SqlExpr (Value a)

COUNT.

countDistinct :: Num a => SqlExpr (Value typ) -> SqlExpr (Value a)

COUNT(DISTINCT x).

Since: 2.4.1

not_ :: SqlExpr (Value Bool) -> SqlExpr (Value Bool)

(==.) :: PersistField typ => SqlExpr (Value typ) -> SqlExpr (Value typ) -> SqlExpr (Value Bool) infix 4

(>=.) :: PersistField typ => SqlExpr (Value typ) -> SqlExpr (Value typ) -> SqlExpr (Value Bool) infix 4

(>.) :: PersistField typ => SqlExpr (Value typ) -> SqlExpr (Value typ) -> SqlExpr (Value Bool) infix 4

(<=.) :: PersistField typ => SqlExpr (Value typ) -> SqlExpr (Value typ) -> SqlExpr (Value Bool) infix 4

(<.) :: PersistField typ => SqlExpr (Value typ) -> SqlExpr (Value typ) -> SqlExpr (Value Bool) infix 4

(!=.) :: PersistField typ => SqlExpr (Value typ) -> SqlExpr (Value typ) -> SqlExpr (Value Bool) infix 4

(&&.) :: SqlExpr (Value Bool) -> SqlExpr (Value Bool) -> SqlExpr (Value Bool) infixr 3

(||.) :: SqlExpr (Value Bool) -> SqlExpr (Value Bool) -> SqlExpr (Value Bool) infixr 2

between :: PersistField a => SqlExpr (Value a) -> (SqlExpr (Value a), SqlExpr (Value a)) -> SqlExpr (Value Bool)

BETWEEN.

@since: 3.1.0

(+.) :: PersistField a => SqlExpr (Value a) -> SqlExpr (Value a) -> SqlExpr (Value a) infixl 6

(-.) :: PersistField a => SqlExpr (Value a) -> SqlExpr (Value a) -> SqlExpr (Value a) infixl 6

(/.) :: PersistField a => SqlExpr (Value a) -> SqlExpr (Value a) -> SqlExpr (Value a) infixl 7

(*.) :: PersistField a => SqlExpr (Value a) -> SqlExpr (Value a) -> SqlExpr (Value a) infixl 7

random_ :: (PersistField a, Num a) => SqlExpr (Value a)

Deprecated: Since 2.6.0: random_ is not uniform across all databases! Please use a specific one such as random_, random_, or random_

round_ :: (PersistField a, Num a, PersistField b, Num b) => SqlExpr (Value a) -> SqlExpr (Value b)

ceiling_ :: (PersistField a, Num a, PersistField b, Num b) => SqlExpr (Value a) -> SqlExpr (Value b)

floor_ :: (PersistField a, Num a, PersistField b, Num b) => SqlExpr (Value a) -> SqlExpr (Value b)

min_ :: PersistField a => SqlExpr (Value a) -> SqlExpr (Value (Maybe a))

max_ :: PersistField a => SqlExpr (Value a) -> SqlExpr (Value (Maybe a))

sum_ :: (PersistField a, PersistField b) => SqlExpr (Value a) -> SqlExpr (Value (Maybe b))

avg_ :: (PersistField a, PersistField b) => SqlExpr (Value a) -> SqlExpr (Value (Maybe b))

castNum :: (Num a, Num b) => SqlExpr (Value a) -> SqlExpr (Value b)

Allow a number of one type to be used as one of another type via an implicit cast. An explicit cast is not made, this function changes only the types on the Haskell side.

Caveat: Trying to use castNum from Double to Int will not result in an integer, the original fractional number will still be used! Use round_, ceiling_ or floor_ instead.

Safety: This operation is mostly safe due to the Num constraint between the types and the fact that RDBMSs usually allow numbers of different types to be used interchangeably. However, there may still be issues with the query not being accepted by the RDBMS or persistent not being able to parse it.

Since: 2.2.9

castNumM :: (Num a, Num b) => SqlExpr (Value (Maybe a)) -> SqlExpr (Value (Maybe b))

Same as castNum, but for nullable values.

Since: 2.2.9

coalesce :: PersistField a => [SqlExpr (Value (Maybe a))] -> SqlExpr (Value (Maybe a))

COALESCE function. Evaluates the arguments in order and returns the value of the first non-NULL SqlExpression, or NULL (Nothing) otherwise. Some RDBMSs (such as SQLite) require at least two arguments; please refer to the appropriate documentation.

Since: 1.4.3

coalesceDefault :: PersistField a => [SqlExpr (Value (Maybe a))] -> SqlExpr (Value a) -> SqlExpr (Value a)

Like coalesce, but takes a non-nullable SqlExpression placed at the end of the SqlExpression list, which guarantees a non-NULL result.

Since: 1.4.3

lower_ :: SqlString s => SqlExpr (Value s) -> SqlExpr (Value s)

LOWER function.

upper_ :: SqlString s => SqlExpr (Value s) -> SqlExpr (Value s)

UPPER function. Since: 3.3.0

trim_ :: SqlString s => SqlExpr (Value s) -> SqlExpr (Value s)

TRIM function. Since: 3.3.0

ltrim_ :: SqlString s => SqlExpr (Value s) -> SqlExpr (Value s)

LTRIM function. Since: 3.3.0

rtrim_ :: SqlString s => SqlExpr (Value s) -> SqlExpr (Value s)

RTRIM function. Since: 3.3.0

length_ :: (SqlString s, Num a) => SqlExpr (Value s) -> SqlExpr (Value a)

LENGTH function. Since: 3.3.0

left_ :: (SqlString s, Num a) => (SqlExpr (Value s), SqlExpr (Value a)) -> SqlExpr (Value s)

LEFT function. Since: 3.3.0

right_ :: (SqlString s, Num a) => (SqlExpr (Value s), SqlExpr (Value a)) -> SqlExpr (Value s)

RIGHT function. Since: 3.3.0

like :: SqlString s => SqlExpr (Value s) -> SqlExpr (Value s) -> SqlExpr (Value Bool) infixr 2

LIKE operator.

ilike :: SqlString s => SqlExpr (Value s) -> SqlExpr (Value s) -> SqlExpr (Value Bool) infixr 2

ILIKE operator (case-insensitive LIKE).

Supported by PostgreSQL only.

Since: 2.2.3

(%) :: SqlString s => SqlExpr (Value s)

The string %. May be useful while using like and concatenation (concat_ or ++., depending on your database). Note that you always have to type the parenthesis, for example:

name `like` (%) ++. val "John" ++. (%)

concat_ :: SqlString s => [SqlExpr (Value s)] -> SqlExpr (Value s)

The CONCAT function with a variable number of parameters. Supported by MySQL and PostgreSQL.

(++.) :: SqlString s => SqlExpr (Value s) -> SqlExpr (Value s) -> SqlExpr (Value s) infixr 5

The || string concatenation operator (named after Haskell's ++ in order to avoid naming clash with ||.). Supported by SQLite and PostgreSQL.

castString :: (SqlString s, SqlString r) => SqlExpr (Value s) -> SqlExpr (Value r)

Cast a string type into Text. This function is very useful if you want to use newtypes, or if you want to apply functions such as like to strings of different types.

Safety: This is a slightly unsafe function, especially if you have defined your own instances of SqlString. Also, since Maybe is an instance of SqlString, it's possible to turn a nullable value into a non-nullable one. Avoid using this function if possible.

subList_select :: PersistField a => SqlQuery (SqlExpr (Value a)) -> SqlExpr (ValueList a)

Execute a subquery SELECT in an SqlExpression. Returns a list of values.

valList :: PersistField typ => [typ] -> SqlExpr (ValueList typ)

Lift a list of constant value from Haskell-land to the query.

justList :: SqlExpr (ValueList typ) -> SqlExpr (ValueList (Maybe typ))

Same as just but for ValueList. Most of the time you won't need it, though, because you can use just from inside subList_select or Just from inside valList.

Since: 2.2.12

in_ :: PersistField typ => SqlExpr (Value typ) -> SqlExpr (ValueList typ) -> SqlExpr (Value Bool)

IN operator. For example if you want to select all Persons by a list of IDs:

SELECT *
FROM Person
WHERE Person.id IN (?)

In esqueleto, we may write the same query above as:

select $
from $ \person -> do
where_ $ person ^. PersonId `in_` valList personIds
return person

Where personIds is of type [Key Person].

notIn :: PersistField typ => SqlExpr (Value typ) -> SqlExpr (ValueList typ) -> SqlExpr (Value Bool)

NOT IN operator.

exists :: SqlQuery () -> SqlExpr (Value Bool)

EXISTS operator. For example:

select $
from $ \person -> do
where_ $ exists $
         from $ \post -> do
         where_ (post ^. BlogPostAuthorId ==. person ^. PersonId)
return person

notExists :: SqlQuery () -> SqlExpr (Value Bool)

NOT EXISTS operator.

set :: PersistEntity val => SqlExpr (Entity val) -> [SqlExpr (Update val)] -> SqlQuery ()

SET clause used on UPDATEs. Note that while it's not a type error to use this function on a SELECT, it will most certainly result in a runtime error.

(=.) :: (PersistEntity val, PersistField typ) => EntityField val typ -> SqlExpr (Value typ) -> SqlExpr (Update val) infixr 3

(+=.) :: (PersistEntity val, PersistField a) => EntityField val a -> SqlExpr (Value a) -> SqlExpr (Update val) infixr 3

(-=.) :: (PersistEntity val, PersistField a) => EntityField val a -> SqlExpr (Value a) -> SqlExpr (Update val) infixr 3

(*=.) :: (PersistEntity val, PersistField a) => EntityField val a -> SqlExpr (Value a) -> SqlExpr (Update val) infixr 3

(/=.) :: (PersistEntity val, PersistField a) => EntityField val a -> SqlExpr (Value a) -> SqlExpr (Update val) infixr 3

case_ :: PersistField a => [(SqlExpr (Value Bool), SqlExpr (Value a))] -> SqlExpr (Value a) -> SqlExpr (Value a)

CASE statement. For example:

select $
return $
case_
   [ when_
       (exists $
       from $ \p -> do
       where_ (p ^. PersonName ==. val "Mike"))
     then_
       (sub_select $
       from $ \v -> do
       let sub =
               from $ \c -> do
               where_ (c ^. PersonName ==. val "Mike")
               return (c ^. PersonFavNum)
       where_ (v ^. PersonFavNum >. sub_select sub)
       return $ count (v ^. PersonName) +. val (1 :: Int)) ]
   (else_ $ val (-1))

This query is a bit complicated, but basically it checks if a person named "Mike" exists, and if that person does, run the subquery to find out how many people have a ranking (by Fav Num) higher than "Mike".

NOTE: There are a few things to be aware about this statement.

• This only implements the full CASE statement, it does not implement the "simple" CASE statement.
• At least one when_ and then_ is mandatory otherwise it will emit an error.
• The else_ is also mandatory, unlike the SQL statement in which if the ELSE is omitted it will return a NULL. You can reproduce this via nothing.

Since: 2.1.2

toBaseId :: ToBaseId ent => SqlExpr (Value (Key ent)) -> SqlExpr (Value (Key (BaseEnt ent)))

Convert an entity's key into another entity's.

This function is to be used when you change an entity's Id to be that of another entity. For example:

Bar
  barNum Int
Foo
  bar BarId
  fooNum Int
  Primary bar

For this example, declare:

instance ToBaseId Foo where
  type BaseEnt Foo = Bar
  toBaseIdWitness = FooKey

Now you're able to write queries such as:

select $
from $ (bar `InnerJoin` foo) -> do
on (toBaseId (foo ^. FooId) ==. bar ^. BarId)
return (bar, foo)

Note: this function may be unsafe to use in conditions not like the one of the example above.

Since: 2.4.3

(<#) :: (a -> b) -> SqlExpr (Value a) -> SqlExpr (Insertion b)

Apply a PersistField constructor to SqlExpr Value arguments.

(<&>) :: SqlExpr (Insertion (a -> b)) -> SqlExpr (Value a) -> SqlExpr (Insertion b)

Apply extra SqlExpr Value arguments to a PersistField constructor

subSelect :: PersistField a => SqlQuery (SqlExpr (Value a)) -> SqlExpr (Value (Maybe a))

Execute a subquery SELECT in a SqlExpr. The query passed to this function will only return a single result - it has a LIMIT 1 passed in to the query to make it safe, and the return type is Maybe to indicate that the subquery might result in 0 rows.

If you find yourself writing joinV . subSelect, then consider using subSelectMaybe.

If you're performing a countRows, then you can use subSelectCount which is safe.

If you know that the subquery will always return exactly one row (eg a foreign key constraint guarantees that you'll get exactly one row), then consider subSelectUnsafe, along with a comment explaining why it is safe.

Since: 3.2.0

subSelectMaybe :: PersistField a => SqlQuery (SqlExpr (Value (Maybe a))) -> SqlExpr (Value (Maybe a))

Execute a subquery SELECT in a SqlExpr. This function is a shorthand for the common joinV . subSelect idiom, where you are calling subSelect on an expression that would be Maybe already.

As an example, you would use this function when calling sum_ or max_, which have Maybe in the result type (for a 0 row query).

Since: 3.2.0

subSelectCount :: (Num a, PersistField a) => SqlQuery ignored -> SqlExpr (Value a)

Performs a COUNT of the given query in a subSelect manner. This is always guaranteed to return a result value, and is completely safe.

Since: 3.2.0

subSelectList :: PersistField a => SqlQuery (SqlExpr (Value a)) -> SqlExpr (ValueList a)

Execute a subquery SELECT in a SqlExpr that returns a list. This is an alias for subList_select and is provided for symmetry with the other safe subselect functions.

Since: 3.2.0

subSelectForeign

Arguments

:: (BackendCompatible SqlBackend (PersistEntityBackend val1), PersistEntity val1, PersistEntity val2, PersistField a)

=> SqlExpr (Entity val2)

An expression representing the table you have access to now.

-> EntityField val2 (Key val1)

The foreign key field on the table.

-> (SqlExpr (Entity val1) -> SqlExpr (Value a))

A function to extract a value from the foreign reference table.

-> SqlExpr (Value a)

Performs a sub-select using the given foreign key on the entity. This is useful to extract values that are known to be present by the database schema.

As an example, consider the following persistent definition:

User
  profile ProfileId

Profile
  name    Text

The following query will return the name of the user.

getUserWithName =
    select $
    from $ user ->
    pure (user, subSelectForeign user UserProfile (^. ProfileName)

Since: 3.2.0

subSelectUnsafe :: PersistField a => SqlQuery (SqlExpr (Value a)) -> SqlExpr (Value a)

Execute a subquery SELECT in a SqlExpr. This function is unsafe, because it can throw runtime exceptions in two cases:

1. If the query passed has 0 result rows, then it will return a NULL value. The persistent parsing operations will fail on an unexpected NULL.
2. If the query passed returns more than one row, then the SQL engine will fail with an error like "More than one row returned by a subquery used as an expression".

This function is safe if you guarantee that exactly one row will be returned, or if the result already has a Maybe type for some reason.

For variants with the safety encoded already, see subSelect and subSelectMaybe. For the most common safe use of this, see subSelectCount.

Since: 3.2.0