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.
Database.Esqueleto.Experimental in version 4.0.0.0 of the
library. The
Experimental module has a dramatically improved
means for introducing tables and entities that provides more power and
less potential for runtime errors.
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)
...