CODEWITHSUNDEEP
sqlhaskellsubqueryesqueleto
ruben.moor
ruben.moor

Reputation: 1965

How to compose queries/subselect with Esqueleto?

Based on this answer, I conclude that Esqueleto does not allow to compose two queries with a left outer join. However, I hope there still is a solution to my problem, somewhere.

I want to do a left outer join with a table that is additionally restricted. Consider my first approach:

fetchFarmsByCity1 city = runDb . select . from $
  \(farm `LeftOuterJoin` pig) -> do
    on $ pig ?. PigFkFarm ==. just (farm ^. FarmId)
    where_ $
          pig ?. PigNumberOfLegs ==. val 4
      &&. farm ^. FarmCity ==. val city
    return (farm, pig)

My second approach is an sql-motivated subquery that fails already during type checking, probably because of the restriction linked on the top of this post:

pigsQuery = from $ \pig -> do
  where_ $ pig ^. PigNumberOfLegs ==. val 4
  return pig

fetchFarmsByCity2 city = runDb . select . from $
  \(farm `LeftOuterJoin` pig) -> do
    pigs <- pigsQuery
    on $ pig ?. PigFkFarm ==. just (farm ^. FarmId)
    where_ $
          farm ^. FarmCity ==. val city

Is there another way to solve this? Can I somehow move the number-of-legs-restriction in front of the outer join (in the first approach)? Splitting it up into two queries would be my measure of last resort.

I feel that this is standard enough to have alternative solutions.

Upvotes: 1

Views: 342

Answers (1)

xnyhps
xnyhps

Reputation: 3316

It seems to work by moving the "4 legs" restriction to the on clause instead of the where_ clause:

fetchFarmsByCity1 city = select . from $
  \(farm `LeftOuterJoin` pig) -> do
    on $
        (pig ?. PigFkFarm ==. just (farm ^. FarmId)
        &&. (pig ?. PigNumberOfLegs ==. just (val 4)))
    where_ $
      farm ^. FarmCity ==. val city
    return (farm, pig)

Upvotes: 3

Related Questions