Impossible to use NULL results from outer joins (in where clause)

[rehno-lindeque]

Joins that require testing for a NULL id don't appear to be possible. For example, from http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS NULL

written as

select $
from $ \(ta `LeftOuterJoin` tb) -> do
on (ta ^. TableAName ==. tb ^. TableBName)
where_ (tb ^. TableBId ==. val Nothing)
return ta

would give the error

Couldn't match expected type `KeyBackend
                                SqlBackend TableA'
            with actual type `Maybe a0'
In the first argument of `val', namely `Nothing'
In the second argument of `(==.)', namely `val Nothing'

[meteficha]

persistent doesn't support NULL ids. If for some reason you need some sort of identification number that may be NULL, the easiest fix would be creating a field for it with type Maybe Int64. So this actually isn't an esqueleto bug.

[meteficha]

BTW, that KeyBackend SqlBackend TableA is actually the type TableAId (which is funny since your example uses TableBId). It says that a TableAId can't be unified with Maybe a.

[rehno-lindeque]

Oh yes I'm sorry about that confusion - I just tried to get the example to look similar to my real code/error and I was traveling :).

I think there's some misunderstanding here though: the id field in the database is in fact NOT NULL, but SQL semantics lets you test for null keys when doing outer join to see whether a match could be found on the left-hand side or right-hand side of an outer join. (The venn diagram examples in that link I posted demonstrate this). This is actually pretty important for a lot of code that I'd like to port over to haskell... do you think there's any chance esqueleto could get this? It does seem pretty challenging to implement. Thinking about it, I imagine the same problem will crop up in the final return sub-expression of any outer join where non-null fields could end up being null in the result of a query.

EDIT: here's a slightly easier breakdown of those kinds of selects (stolen from stackoverflow)

[meteficha]

Oh, I see, I didn't realize you were trying to write an OUTER JOIN. You may "ask" for the outer entity to be wrapped in Maybe by using ?. instead of ^.:

select $
from $ \(ta `LeftOuterJoin` mtb) -> do
on $ just (ta ^. TableAName) ==. mtb ?. TableBName
where_ $ isNothing (tb ?. TableBId)
return ta

The code above is untested, let me know if it explodes :).

[rehno-lindeque]

Ah awesome! Thank you so much. I'll try it right away