bitemyapp / bitemyapp/esqueleto

Improve GROUP BY

Open
#185 5 comments 0 reactions 0 assignees View on GitHub
Dominant language
Haskell
Stars
399
Forks
107
Avg merge
1d 17h
Merged PRs (30d)
1

Description

Now that @belevy has defeated the Join monster, we have another set of runtime issues that are becoming more prevalent:

`GROUP BY`

In SQL, if you `GROUP BY` a column, then you can't refer to any other columns in the `SELECT`, except by aggregations.

But `esqueleto` currently has absolutely no way of tracking this in the types. [`opaleye`](https://github.com/tomjaguarpaw/haskell-opaleye/blob/master/Doc/Tutorial/TutorialBasic.lhs#L559-L607) handles this with a function `aggregate`, but it's not obvious to me how you'd use this with a `JOIN`. [`beam`](https://hackage.haskell.org/package/beam-core-0.8.0.0/docs/Database-Beam-Query.html#t:QGroupable) has [an abstraction](https://tathougies.github.io/beam/user-guide/queries/aggregates/) that similarly accepts a query and provides a lambda. [`squeal`](https://hackage.haskell.org/package/squeal-postgresql-0.6.0.2/docs/Squeal-PostgreSQL-Query-Table.html#v:groupBy) tracks the aggregation in the type of `TableExpression`.

I think the least invasive way to go is probably to do something like:

```haskell
groupBy :: SqlQuery a -> (a -> GroupExpr) -> (GroupExpr -> SqlQuery b)
```

Usage would look like:

```haskell
groupBy
do
(f :& b) <- from $ Table @Foo
`InnerJoin`
Table @Bar
`on` do
\(f :& b) -> f ^. FooId ==. b ^. BarFooId
where_ $ f ^. FooName `in_` valList []
pure (f :& b)
do
\(f :& b) ->
groupCol (f ^. FooId) b
do
\(fooId, b) ->
pure (fooId, sum_ b)
```

But this structure really strongly reminds me of the [effectful property testing](https://www.parsonsmatt.org/2020/03/11/effectful_property_testing.html) pattern. Taking that as inspiration, maybe the right syntax is more like:

```haskell
select $ do
(a :& b) <- from fromClauses
groupBy (a ^. AGroupingColumn) $ \groupingColumn -> do
having_ $ sum_ (b ^. BSales) >=. val 123
pure (groupingColumn, sum_ (b ^. BSales), countRows)
```

In this case, our type is now:

```
groupBy
:: GroupingColumns
-> (GroupingColumns -> SqlQuery (Aggregating x))
-> SqlQuery x
```

Syntactically, it's nice, but it would need to be the last thing called in the `do` block. We can't require that without indexed monads.

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.