How to query Slick with an optional foreign key to return all records with and without relationship?

Question

I have an optional foreign key defined on Event which goes to EventType. I want to query for all events, even those events which have a None (null) event type. This is the foreign key defined on Event.

def eventTypeId = column[Option[Long]]("event_type_id")
def eventType = foreignKey("event_type", eventTypeId, EventTypes.eventTypes)(_.id)

My initial query is as follows but it only returns those records which do have the foreign key set since the foreign key is optional. How?

(for {
   p <- events
   e <- p.eventType
} yield (p, e))

I wish to see all events with AND without the foreign key set.

Answer 1

It sounds like what you're asking for is for Slick to produce an outer join in that situation. I realize you've tagged this as a Slick 2 question, but the enhancement is slated for Slick 3.2: https://github.com/slick/slick/issues/179

In the meantime, you can manage the join yourself. For example:

events.leftJoin(eventTypes).on(_.eventTypeId === _.id).
map { case (e, et) => (e, et.name.?) }

... which will ultimately give you a result of Seq[(Event, Option[String])] (or similar, assuming you have a name that's a String).