Yet Another From Idea, Destroy the Writer

[parsonsmatt]

We can write innerJoin relatively easily:

innerJoin :: forall t. PersistEntity t => (SqlExpr (Entity t) -> SqlExpr Bool) -> SqlQuery (SqlExpr (Entity t))
innerJoin k = do
    t <- from $ table @t
    where_ $ k t
    pure t

Well, okay, it's a CROSS JOIN with a WHERE. Usage looks nice.

q = do
    a <- from $ table @A
    b <- innerJoin @B $ \b -> a ^. #id ==. b ^. #a
    c <- innerJoin @C $ \c -> a ^. #id ==. c ^. #a
    d <- innerJoin @D $ \d -> a ^. #id ==. d ^. #a
    ...

With a bigass query, and lots of tables, the on lambda becomes a bit cumbersome. Factoring this out helps a lot.

But...

Well, it's a hack - it only works because FROM a, b WHERE a.id = b.a is equivalent to FROM a INNER JOIN b ON a.id = b.a. We can't do left joins, and it also re-opens the problem for LATERAL if we allow subqueries.

If we had access to the [FromClause] in the SideData record, we could easily do this.

innerJoin k = do
    c <- getFinalClause 
    (_ :& t) <- from $ c `InnerJoin` table @t    
        `on` \(_ :& t) -> k t
    pure t

except, well, we'd need to overwrite the final clause.

Even getting the final clause isn't possible with WriterT. It's fine with StateT.

Except, well, fuck, all the Experimental stuff is doing FromRaw and building the bytestrings directly. So modifying them after the fact isn't really a thing.

Actually maybe it's fine. Anyway I need to explore this a bit more.

[belevy]

I'm not sure that this is really what you want. Joins and where's aren't really peers. Ergonomics would be better focused on the from expression language. You have correctly acknowledge the issue with a monadic from language (without requiring all subqueries to be lateral). I wonder if From could admit a monad safely? At the very least a limited FromBuilder could be made that did but not sure how it would look.

[parsonsmatt]

Yeah, what I'm imagining is something like: Swap WriterT SideData to StateT SideData. Then I can write modifyLastJoin :: (FromClause -> SqlQuery FromClause) -> SqlQuery (). which would let me slap a LeftOuterJoin on.

Something like:

leftOuterJoin k = do
  lastJoin <- popLastJoin 
  from $ lastJoin `LeftOuterJoin` table @t `on` (\(_ :& t) -> k t) 

popLastJoin :: SqlQuery FromClause
popLastJoin = do
  lift $ state $ \sd -> 
    let (old, last) = unsnoc $ sdFromClause sd
    in (last, sd { sdFromClause = old })

It's true that having multiple from with a SubQuery allows you to produce a LATERAL query illegally, but that's already true if you're passing SqlExpr (Entity e) as inputs to functions.

I wonder if LinearTypes could be used here. I doubt it, but it'd be neat. Something like, "this value must not refer to anything not introduced in it's own scope." ST s comes to mind as a related trick, but probably not worth the effort.

[belevy]

It's true that having multiple from with a SubQuery allows you to produce a LATERAL query illegally, but that's already true if you're passing SqlExpr (Entity e) as inputs to functions.

This is probably somethign that would require an indexed monad to fix. Alternatively we can make it always just lateral cross join if there is already a from clause.

I wonder if LinearTypes could be used here. I doubt it, but it'd be neat. Something like, "this value must not refer to anything not introduced in it's own scope." ST s comes to mind as a related trick, but probably not worth the effort.

Beam uses the ST trick to prevent this and it is miserable to use, I prefer discouraging use of from twice.

I am not opposed to changing the writer to a state (it would cleanup the internals of subqueries) but what you are proposing leaves ambiguous references to the results of lastJoin potentially hanging since they will be returned again by leftOuterJoin