Squeryl join with null columns

Question

Let's say I have a table that contains ids that relate to other tables. I want to get all the objects because the combination of the tables makes up one object. Naturally I'd join them but I have an issue when some of the fields are optional. So I want to "get all" using this.

from(mainTable,
  optionalFieldTable,
  optionalFieldTable,
  requiredFieldTable1,
  requiredFieldTable2)((main,o1,o2,r1,r2) =>
  where(
    (main.someId1 === r1.id) and
    (main.someId2 === r2.id) and
    (main.optId1.inhibitWhen(main.optId1 == None) === o1.id) and
    (main.optId2.inhibitWhen(main.optId2 == None) === o2.id))
  select(main,o1,o2,r1,r2)
  ).toList

The above gives me an empty list. If I remove the optional fields from the query all together though it works. So what I'm wondering if there is a special way to test if the optional ids are None or Some other than the inhibit when?

I suspect that it has something to do with returning the tuple because it's trying to return to O1 and O2 object and not Option[O1] and Option[O2]

Answer 1

I think you'd probably want to use the join syntax like:

join(mainTable, 
  requiredFieldTable1, 
  requiredFieldTable2, 
  optionalFieldTable1.leftOuter, 
  optionalFieldTable2.leftOuter)((main,r1,r2,o1,o2) => 
  select(main,o1,o2,r1,r2)
  on(main.someId1 === r1.id, 
     main.someId2 === r2.id,
     main.optId1 === o1.map(_.id),
     main.optId2 === o2.map(_.id)))

That query should work for you. In the above, it should just return None for o1 and o2 when the fields do not exist. By using the outerJoin, Squeryl takes care of the inhibit part for you. You can take a look at this for reference: http://www.squeryl.org/docs/0.9.5/joins.html