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)