FROM clause, used to bring entities into scope.

Internally, this function uses the From datatype. Unlike the old from, this does not take a function as a parameter, but rather a value that represents a JOIN tree constructed out of instances of From. This implementation eliminates certain types of runtime errors by preventing the construction of invalid SQL (e.g. illegal nested-from).

Bring a PersistEntity into scope from a table

select $ from $ table @People 

Since: 3.5.0.0

Select from a subquery, often used in conjuction with joins but can be used without any joins. Because SqlQuery has a ToFrom instance you probably dont need to use this function directly.

select $ p <- from $ selectQuery do p <- from $ table @Person limit 5 orderBy [ asc p ^. PersonAge ] ... 

Since: 3.5.0.0

A left-precedence pair. Pronounced "and". Used to represent expressions that have been joined together.

The precedence behavior can be demonstrated by:

a :& b :& c == ((a :& b) :& c) 

See the examples at the beginning of this module to see how this operator is used in JOIN operations.

An ON clause that describes how two tables are related. This should be used as an infix operator after a JOIN. For example,

select $ from $ table @Person `innerJoin` table @BlogPost `on` (\(p :& bP) -> p ^. PersonId ==. bP ^. BlogPostAuthorId) 

INNER JOIN

Used as an infix operator `innerJoin`

select $ from $ table @Person `innerJoin` table @BlogPost `on` (\(p :& bp) -> p ^. PersonId ==. bp ^. BlogPostAuthorId) 

Since: 3.5.0.0

INNER JOIN LATERAL

A Lateral subquery join allows the joined query to reference entities from the left hand side of the join. Discards rows that don't match the on clause

Used as an infix operator `innerJoinLateral`

See example 6

Since: 3.5.0.0

LEFT OUTER JOIN

Join where the right side may not exist. If the on clause fails then the right side will be NULL'ed Because of this the right side needs to be handled as a Maybe

Used as an infix operator `leftJoin`

select $ from $ table @Person `leftJoin` table @BlogPost `on` (\(p :& bp) -> p ^. PersonId ==. bp ?. BlogPostAuthorId) 

Since: 3.5.0.0

LEFT OUTER JOIN LATERAL

Lateral join where the right side may not exist. In the case that the query returns nothing or the on clause fails the right side of the join will be NULL'ed Because of this the right side needs to be handled as a Maybe

Used as an infix operator `leftJoinLateral`

See example 6 for how to use LATERAL

Since: 3.5.0.0

RIGHT OUTER JOIN

Join where the left side may not exist. If the on clause fails then the left side will be NULL'ed Because of this the left side needs to be handled as a Maybe

Used as an infix operator `rightJoin`

select $ from $ table @Person `rightJoin` table @BlogPost `on` (\(p :& bp) -> p ?. PersonId ==. bp ^. BlogPostAuthorId) 

Since: 3.5.0.0

FULL OUTER JOIN

Join where both sides of the join may not exist. Because of this the result needs to be handled as a Maybe

Used as an infix operator `fullOuterJoin`

select $ from $ table @Person `fullOuterJoin` table @BlogPost `on` (\(p :& bp) -> p ?. PersonId ==. bp ?. BlogPostAuthorId) 

Since: 3.5.0.0

CROSS JOIN

Used as an infix `crossJoin`

select $ do from $ table @Person `crossJoin` table @BlogPost 

Since: 3.5.0.0

CROSS JOIN LATERAL

A Lateral subquery join allows the joined query to reference entities from the left hand side of the join.

Used as an infix operator `crossJoinLateral`

See example 6

Since: 3.5.0.0

UNION SQL set operation. Can be used as an infix function between SqlQuery values.

UNION ALL SQL set operation. Can be used as an infix function between SqlQuery values.

EXCEPT SQL set operation. Can be used as an infix function between SqlQuery values.

INTERSECT SQL set operation. Can be used as an infix function between SqlQuery values.

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

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

Data type defining the From language. This should not constructed directly in application code.

A From is a SqlQuery which returns a reference to the result of calling from and a function that produces a portion of a FROM clause. This gets passed to the FromRaw FromClause constructor directly when converting from a From to a SqlQuery using from

Since: 3.5.0.0

Type class to support direct use of SqlQuery in a set operation tree

Since: 3.5.0.0

WHERE clause: restrict the query's result.

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) 

ORDER BY clause. See also asc and desc.

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

ORDER BY random() clause.

Since: 1.3.10

Ascending order of this field or SqlExpression.

Descending order of this field or SqlExpression.

LIMIT. Limit the number of returned rows.

OFFSET. Usually used with limit.

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

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

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

Since: 2.2.4

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.

Since: 1.2.2

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

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.

Project a field of an entity.

Project a field of an entity that may be null.

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

IS NULL comparison.

For IS NOT NULL, you can negate this with not_, as in not_ (isNothing (person ^. PersonAge))

Warning: Persistent and Esqueleto have different behavior for != Nothing:

In SQL, = NULL and != NULL return NULL instead of true or false. For this reason, you very likely do not want to use !=. Nothing in Esqueleto. You may find these hlint rules helpful to enforce this:

- error: {lhs: v Database.Esqueleto.==. Database.Esqueleto.nothing, rhs: Database.Esqueleto.isNothing v, name: Use Esqueleto's isNothing} - error: {lhs: v Database.Esqueleto.==. Database.Esqueleto.val Nothing, rhs: Database.Esqueleto.isNothing v, name: Use Esqueleto's isNothing} - error: {lhs: v Database.Esqueleto.!=. Database.Esqueleto.nothing, rhs: not_ (Database.Esqueleto.isNothing v), name: Use Esqueleto's not isNothing} - error: {lhs: v Database.Esqueleto.!=. Database.Esqueleto.val Nothing, rhs: not_ (Database.Esqueleto.isNothing v), name: Use Esqueleto's not isNothing}

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

NULL value.

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

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

COUNT(*) value.

COUNT.

COUNT(DISTINCT x).

Since: 2.4.1

BETWEEN.

@since: 3.1.0

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

Same as castNum, but for nullable values.

Since: 2.2.9

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

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 function.

UPPER function. @since 3.3.0

TRIM function. @since 3.3.0

LTRIM function. @since 3.3.0

RTRIM function. @since 3.3.0

LENGTH function. @since 3.3.0

LEFT function. @since 3.3.0

RIGHT function. @since 3.3.0

LIKE operator.

ILIKE operator (case-insensitive LIKE).

Supported by PostgreSQL only.

Since: 2.2.3

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" ++. (%) 

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

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

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.

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

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

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 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].

NOT IN operator.

EXISTS operator. For example:

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

NOT EXISTS operator.

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.

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

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 

In this example, Bar is said to be the BaseEnt(ity), and Foo the child. To model this in Esqueleto, declare:

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

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

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

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.