8

I have a database schema with teachers, schools, and districts. The TEACHERS table has a nullable SCHOOL_ID column (a teacher may or may not belong to a school) and the SCHOOLS table has a nullable DISTRICT_ID column (a school may or may not belong to a district).

Using Esqueleto, I want a list of teachers, each with a school (if they belong to one) and a district (if they belong to a school that belongs to a district). It took a little while to figure out the right expression just for the teacher->school left join, but I eventually got it right:

select $
from $ \(teacher `LeftOuterJoin` school) -> do
  on (teacher ^. TeacherSchoolId  ==. school ?. SchoolId)
  return (teacher, school)

I tried adding another left join on DISTRICTS using an analogous expression:

select $
from $ \(teacher `LeftOuterJoin` school `LeftOuterJoin` district) -> do
  on (school  ^. SchoolDistrictId ==. district ?. DistrictId)
  on (teacher ^. TeacherSchoolId  ==. school   ?. SchoolId)
  return (teacher, school, district)

But I get an error:

Couldn't match type ‘Entity School’ with ‘Maybe (Entity School)’
Expected type: SqlExpr (Maybe (Entity School))
  Actual type: SqlExpr (Entity School)
In the first argument of ‘(?.)’, namely ‘school’
In the second argument of ‘(==.)’, namely ‘school ?. SchoolId’

Can this double join be expressed using Esqueleto? If so, how?

Sean
  • 29,130
  • 4
  • 80
  • 105
  • I don't know this library, and it all looks complicated, but it kind of looks like you need to lift some `Maybe` stuff to sync up with the SQL stuff. Or the other way 'round. I dunno. – dfeuer Jan 24 '15 at 03:32

1 Answers1

3

Try changing

 on (teacher ^. TeacherSchoolId  ==. school   ?. SchoolId)

To

 on (teacher ^. TeacherSchoolId  ==. just (school   ?. SchoolId))

If that doesn't work, slap 'just' on other components of the query expression until it works.

Reference: used Esqueleto recently on a commercial project

Update, 2016/10/26:

I recently encountered this problem. I think it's a Persistent serialization problem interacting with Esqueleto's willingness to pretend the join doesn't produce nullable results.

I recently changed a fragment of a query from:

  person  `LeftOuterJoin`
  personExtra
 ) -> do
  on ((personExtra ^. PersonExtraPerson) ==. (person ^. PersonId))

to:

  person  `LeftOuterJoin`
  personExtra
 ) -> do
  on ((personExtra ?. PersonExtraPerson) ==. just (person ^. PersonId))

I also changed the return type of my query from Entity PersonExtra to Maybe (Entity PersonExtra).

Now Persistent expects the possibility of a PersistNull and the query works fine for me.

bitemyapp
  • 1,627
  • 10
  • 15
  • I've tried all sixteen combinations of having and not having `just` around each of the four arguments to the two `==.` operators. None of them results in compilable code. – Sean Jan 26 '15 at 19:21
  • @Sean what's with the `?.` ? – bitemyapp Jan 26 '15 at 21:26
  • It's "to project a field from a Maybe (Entity a)" according to the Esqueleto docs. – Sean Jan 26 '15 at 22:26
  • @Sean try flipping those around to `^.` with the aforementioned combinations of `just` as well. I know the combinatorics / situation is ridiculous but type-based reasoning in Esqueleto is pretty hard. There's a fair bit of arbitrary behavior. – bitemyapp Jan 27 '15 at 06:00
  • Is there a saner package everyone should use instead? – dfeuer Jan 27 '15 at 18:13
  • The only variation using only `^.` that compiled was to put it on the right-hand side of both `==.` operators. That produced a runtime error `PersistMarshalError "int64 Expected integer, recevied: PersistNull`. – Sean Jan 27 '15 at 18:17
  • Need to fix the type so it mentions that the column may not exist, then fix the query if needs be (possibly with `just`) – bitemyapp Jan 28 '15 at 06:07
  • @dfeuer I haven't found anything better yet. I've tried everything announced on the mailing list. I'm not happy with this either. – bitemyapp Jan 28 '15 at 06:08