Returning `Maybe (Entity a)` from Esqueleto `LeftOuterJoin`

Question

From a contrived config/models in a scaffolded site:

Inventory
  name        Text
  description Text
Container
  name        Text
ContainerSlot
  container   ContainerId
  item        InventoryId Maybe

Now, using Esqueleto, I want to use LeftOuterJoin to get the slots in a container, with the actual inventory empty if it has not been assigned.

selectContainerSlots containerKey = do
  stuff <- select $ from $ \(cs `LeftOuterJoin` i) -> do
    on $ cs ^. ContainerSlotItem ==. just (i ^. InventoryId)
    where_ $ cs ^. ContainerSlotContainer ==. val containerKey
    return (cs, i)
  return $ uncurry buildStuff <$> stuff

I would expect buildStuff to need the following signature due to the "outer" nature of the join:

buildStuff :: Entity ContainerSlot -> Maybe (Entity Inventory) -> Result

but find that it needs the following:

buildStuff :: Entity ContainerSlot -> Entity Inventory -> Result

Which causes runtime failures when (predictably) the the Inventory fields are filled with NULL values.

PersistMarshalError "field id: int64 Expected Integer, received: PersistNull"

Is there a way to project the Entity Inventory as an Maybe (Entity Inventory)?

score 2 · Accepted Answer · answered Apr 02 '17 at 03:19

This could probably be marked a duplicate of Outer Joins with Esqueleto; however the difference is in the projection.

When dealing with any outer joins, all tables which may come back null should have all projections done with the ?. syntax. This will force the table's entities to become Maybe (Entity a) So the solution to the above is

selectContainerSlots containerKey = do
  stuff <- select $ from $ \(cs `LeftOuterJoin` i) -> do
    on $ cs ^. ContainerSlotItem ==. i ?. InventoryId
    where_ $ cs ^. ContainerSlotContainer ==. val containerKey
    return (cs, i)
  return $ uncurry buildStuff <$> stuff

Additionally, if more than one table is chained; e.g.

select $ from $ \(cs `LeftOuterJoin` (i `InnerJoin` is)) -> do

Then both i and is (inventory SKU table) should be projected with that syntax:

  on $ i ?. InventoryId ==. is ?. InventorySkuItem
  on $ cs ^. ContainerSlotItem ==. i ?. InventoryId

Returning `Maybe (Entity a)` from Esqueleto `LeftOuterJoin`

1 Answers1