Joining with Maybe Fields

Question

I have a table with a Maybe Foreign Key. I am trying to join but cannot get it to compile.

CatTable
   name Text

MyTable
   category CatTableId Maybe
   amount Double

My query:

myQuery :: (PersistQuery (SqlPersistT m), MonadLogger m , MonadResourceBase m) =>
                        SqlPersistT m [(E.Value (Maybe (Text)), E.Value (Maybe Double))]
myQuery = do
  E.select $ E.from $  \(t `E.LeftOuterJoin` c) -> do
        E.on (t E.?. MyTableCategory E.==. E.just (c E.^. CatTableId))
        E.groupBy $ E.just (c E.^. CatTableName)
        let sum' = E.sum_ (t E.^. MyTableAmount)
        E.orderBy [E.desc sum']
        return (E.just (c E.^. CatTableName) , sum' )

and I get this type error:

Couldn't match type `KeyBackend SqlBackend CatTable'
              with `Maybe (KeyBackend SqlBackend CatTable)'
Expected type: EntityField
                 CatTable (Maybe (KeyBackend SqlBackend CatTable))
  Actual type: EntityField
                 CatTable (KeyBackend SqlBackend CatTable)
In the second argument of `(^.)', namely `CatTableId'
In the first argument of `just', namely `(c ^. CatTableId)'
In the second argument of `(E.==.)', namely
  `just (c ^. CatTableId)'

Couldn't match type `Maybe (Entity MyTable)' with `Entity MyTable'
Expected type: SqlExpr (Entity MyTable)
  Actual type: SqlExpr (Maybe (Entity MyTable))
In the first argument of `(^.)', namely `t'
In the first argument of `sum_', namely `(t ^. MyTableAmount)'
In the expression: sum_ (t ^. MyTableAmount)

I have tried different combinations of ^. and ?. but no luck. Also tried removing or adding just too. At this point this I am just guessing without understanding how to resolve the error. So appreciate any input on how to join on a field of Maybe. I am presuming the groupBy is probably complicating it as it is not a Maybe in the CatTable but it will be once it gets joined.

 select * from cat_table;
 id|name
 1|A
 2|B
 3|C

 select * from my_table;
 id|category|amount
 1|1|55.0
 2|1|15.0
 3|2|10.0
 4|2|60.0
 5||60.0


select name, sum(amount) from my_table as m left join cat_table as c
       on m.category = c.id 
       group by name;


|60.0
A|70.0
B|70.0

Check out this *esqueleto* based older post [Running join on Maybe Relation](http://stackoverflow.com/questions/15603927/running-join-on-maybe-relation/15613340#15613340) — Gabriel Riba, May 12 '14 at 11:27
@GabrielRiba Thanks! I have managed to make my query work now and will post it as an answer later. Could you please let me know why you chose `^.` as opposed `?.` for a Maybe field? I thought `?.` is used for Maybe fields? and `just` to convert regular fields into Maybes. Or am I misunderstanding the use for `?.`? — Ecognium, May 12 '14 at 15:21
I think that [(?.)](http://hackage.haskell.org/package/esqueleto/docs/Database-Esqueleto.html#v:-63-.) is for outer joins where one entity maybe null, which was not the case in that post — Gabriel Riba, May 12 '14 at 16:00
It is explained at the prolog of the *esqueleto* referenced module in hackage. look for the text "On a LEFT OUTER JOIN the entity on the right hand side may not exist ..." — Gabriel Riba, May 12 '14 at 16:12
@GabrielRiba thanks again. I have posted my answer below. I *think* I understand now: I am able to use either `?.` or `just` -- they seem to be equivalent for the right hand side — Ecognium, May 12 '14 at 21:42

Ecognium · Accepted Answer · 2014-05-12T21:45:42.770

This query works. The difference between the query I had posted in my question and this one is really the ?. I thought I tried that variation but perhaps missed the obvious. ~~I am still unclear when we will use ?. though.~~ . I noticed I also get a working query if removed E.just and used ?. for the CatTable Entity (c).

myQuery :: (PersistQuery (SqlPersistT m), MonadLogger m , MonadResourceBase m) =>
                        SqlPersistT m [(E.Value (Maybe Text), E.Value (Maybe Double))]
myQuery = do
  E.select $ E.from $  \(t `E.LeftOuterJoin` c) -> do
        E.on (t E.^. MyTableCategory E.==. E.just (c E.^. CatTableId))
        E.groupBy $ E.just (c E.^. CatTableName)
        let sum' = E.sum_ (t E.^. MyTableAmount)
        E.orderBy [E.desc sum']
        return (E.just (c E.^. CatTableName) ,sum')

Since you use LeftOuterJoin, its rhs operand is a Maybe Entity because there maybe records of the lhs without rhs correspondants, because of this you have to use the (?.) for the rhs operand field projections. — Gabriel Riba, May 13 '14 at 11:27

Joining with Maybe Fields

1 Answers1