bitemyapp / bitemyapp/esqueleto

Implement Window Functions

Open
#229 6 comments 0 reactions 0 assignees View on GitHub
enhancement needs discussion
Dominant language
Haskell
Stars
399
Forks
107
Avg merge
1d 17h
Merged PRs (30d)
1

Description

This is my stream of consciousness thoughts on Window functions.

Window functions should be restricted in use. Therefore they cant be SqlExpr (Value a) since that would allow for their use wherever a Value is allowed.
Solution: `SqlExpr (AggregateValue a)` This would use a new newtype to support SqlSelect. I am not a fan of the new wrapper type but it is forced on us by the fundeps of SqlSelect. I would much rather Value and AggregateValue disappeared when we go from the SqlExpr language to Haskell.

Currently we have a number of aggregate functions that can also be used with a window. These functions return types of `SqlExpr (Value a)`. This seems wrong since you cant use aggregate functions in a where clause. Rather `where_` expressions should only be in terms of `SqlExpr (Value a)` and `having` should be in terms of `SqlExpr (AggregateValue a)`. This change would require work on generalizing the comparison operators.

Should there be two different count_(or other aggregate) functions or should we use a type class to overload the function. The typeclass would be.
```haskell
class SqlCount a where
count_ :: SqlExpr (Value v) -> a
instance SqlCount (SqlExpr (AggregateValue a)) where
count_ = unsafeSqlFunction "COUNT"
instance SqlCount(WindowContext -> SqlExpr (AggregateValue a)) where
count_ = unsafeWindowFunction "COUNT"
```

I am thinking about how to make the best SQL like syntax and my current idea is:
```haskell
count_ (user ^. UserId) over_ (partitionBy (user ^. UserGroup) <> orderBy (user ^. UserName))
```
to achieve this we could have
```haskell
data Over = Over
type WindowContext = Over -> Window
over_ = Over
```
optionally we can just use `()` instead of the new `Over` unit type. This is actually how the nice case_ syntax works `then_ = ()`

The window would be a Monoid
```haskell
data Window = Window
{ windowPartition :: Monoid.Last WindowPartition
, windowOrder :: Monoid.Last OrderBy
, windowFrame :: Monoid.Last WindowFrame
}
```
so instead of writing
```SQL
COUNT(*) OVER ()
```
you would have to write
```haskell
countRows_ over_ mempty
```
Maybe we should make an `AsWindow` class
```haskell
class AsWindow a where
asWindow :: a -> Window
instance AsWindow Window where
asWindow = id
instance AsWindow () where
asWindow = const mempty
```
which could be used to enable
```haskell
countRows_ over_ ()
```

This update would require a change to the SqlExpr to create a new constructor
```haskell
EAggregate :: (IdentInfo -> (TLB.Builder, [PersistValue])) -> SqlExpr (AggregateValue a)
```
This is theoretically not required but will increase the type safety without breaking backwards compat. Then unsafeWindowFunction would handle the logic of converting a Window into an EAggregate value.

Need to come up with a good way to generate WindowFrames. We don't need named windows since we have let statements.

Relevant Issues:
#196

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.