Transforming Doobie ConnectionIO[Option[Int]] without explicit match

Question

I have a ConnectionIO[Option[Int]] and map over the Option to produce a ConnectionIO[Option[String]] with a query the Some[Int] otherwise keep the Nones. I was able to do this with aforcomprehension and amatch`:

def findWidgetByOwner(name: String): ConnectionIO[Option[String]] = for {
    opt <- sql"SELECT owner_id FROM owners WHERE name = $name".query[Int].option
    widget <- opt match {
        case None => None.pure[ConnectionIO]
        case Some(id) => sql"SELECT widget_name FROM widgets WHERE owner_id = $id".query[String].option
    }
} yield widget

I know i'm getting tripped up by the ConnectionIO container, but I can't find a cleaner mapping approach that to transform ConnectionIO[Option[Int]] to ConnectionIO[Option[String]].

Answer 1

It would be cleaner to join using SQL instead of scala:

def findWidgetByOwner(name: String): ConnectionIO[Option[String]] = 
  sql"""
    SELECT widgets.widget_name FROM widgets WHERE owner_id = $id
    INNER JOIN owners ON widgets.owner_id = owners.owner_id
    WHERE owners.name = $name
  """.query[Int].option

But if you want to clean up the original, some incantation of sequence would probably work (not tested):

import cats._, cats.data._, cats.implicits._

...
widget <- opt.map {id => 
  sql"SELECT widget_name FROM widgets WHERE owner_id = $id".query[String].unique
}.sequence

Note: You have to change query[String].option to .query[String].unique otherwise widget becomes an Option[Option[String]] which if the widget query can be null, might be desirable, but requires a .flatten at the end.