Haskell Esqueleto - update with join

Question

Can I make an update query with join in Haskell? Maybe something like this:

putPromotionInStoreItemsFromStoreBySItemId :: SItemId -> SItemPromoId -> YesodDB App ()
putPromotionInStoreItemsFromStoreBySItemId siId pId = update $ \(si `InnerJoin` s `InnerJoin` ssi) -> do
  on $ ssi ^. SItemStore ==. s ^. StoreId
  on $ s ^. StoreId ==. si ^. SItemStore
  set ssi [ SItemPromotion =. val pId ]
  where_ $     si ^. SItemId ==. val siId 
           &&. isNothing (ssi ^. SItemDisabled)

Answer 1

With Esqueleto's update function, no. Esqueleto does not support joins in updates.

How can you tell?

Consider the type of update:

update :: (MonadIO m, PersistEntity val, BackendCompatible SqlBackend (PersistEntityBackend val), SqlBackendCanWrite backend) => (SqlExpr (Entity val) -> SqlQuery ()) -> ReaderT backend m ()

The function supplied to update accepts a SqlExpr (Entity val) and returns a SqlQuery.

Can you use InnerJoin to define this function? No. InnerJoin is the constructor for the InnerJoin type. InnerJoin and SqlExpr ... do not unify because SqlExpr is a different type entirely.

What about other ways to do joins? Is there a way to construct a SqlQuery that involves a join against an existing SqlExpr (Entity val)?

Not via direct construction. SqlQuery has no public constructors.

How about via some other function? There is apparently one function of the form a -> SqlQuery b: from. It's actual type is From a a' => a -> SqlQuery a'.

What ToFrom (do not fail to appreciate this delightful name) instances do we have available? We would especially like one with SqlExpr (Entity val) as the first type parameter.

Alas, while there are a number of interesting instances, none of them apparently relate to this case. In some sense this is satisfying as the SQL UPDATE statement does not use a FROM clause. If the Esqueleto from function had been the solution it would have been a strange (though not inconceivable) mismatch in the modeling of the underlying SQL.

I cannot empirically prove a negative but the above avenues are quite suggestive of that answer.