Build dynamic query with Slick 2.1.0

Question

Goal is to filter Items with optional keywords and/or shopId.

If none of them are defined, all Items should be returned.

My attempt is

case class ItemSearchParameters(keywords: Option[String], shopId: Option[Long])

def search(params: ItemSearchParameters): Either[Failure, List[Item]] = {
    try {
      db withDynSession {
        val q = Items.query
        if (params.keywords.isDefined) {
          q.filter { i =>
            ((i.title like "%" + params.keywords + "%")
            || (i.description like "%" + params.keywords + "%"))
          }
        }
        if (params.shopId.isDefined) {
          q.filter { i =>
            i.shopId === params.shopId
          }
        }
        Right(q.run.toList)
      }
    } catch {
      case e: SQLException =>
        Left(databaseError(e))
    }
  }

params.keywords or params.ShopId defined this function returned all Items. Can someone please explain what is wrong?

---

Update: second attempt

def search(params: ItemSearchParameters): Either[Failure, List[Item]] = {
    try {
      db withDynSession {
        var q = Items.query
        q = params.keywords.map{ k => q.filter(_.title like "%" + k + "%")} getOrElse q
        q = params.keywords.map{ k => q.filter(_.description like "%" + k + "%")} getOrElse q
        q = params.shopId.map{ sid => q.filter(_.shopId === sid)} getOrElse q
        Right(q.run.toList)
      }
    } catch {
      case e: SQLException =>
        Left(databaseError(e))
    }
  }

For this second attempt how to do (title OR description) if keywords isDefined?

---

Update: Third attempt with MaybeFilter Not working

case class MaybeFilter[X, Y](val query: scala.slick.lifted.Query[X, Y, Seq]) {
  def filteredBy(op: Option[_])(f:(X) => Column[Option[Boolean]]) = {
    op map { o => MaybeFilter(query.filter(f)) } getOrElse { this }
  }
}


class ItemDAO extends Configuration {

  implicit def maybeFilterConversor[X,Y](q:Query[X,Y,Seq]) = new MaybeFilter(q)

  def search(params: ItemSearchParameters): Either[Failure, List[Item]] = {
    try {
      db withDynSession {
        val q = Items
          .filteredBy(params.keywords){i => ((i.title like "%" + params.keywords + "%")
            || (i.description like "%" + params.keywords + "%"))}
          .filteredBy(params.shopId){_.shopId === params.shopId}
          .query
        Right(q.list)
      }
    } catch {
      case e: SQLException =>
        Left(databaseError(e))
    }
  }
}

Third attempt returns empty list if keywords is given

Answer 1

def search(params: ItemSearchParameters): Either[Failure, List[Item]] = {
    try {
      db withDynSession {
        var q = Items.query

        q = params.keywords.map{ k => q.filter(
          i => (i.title like "%" + k + "%")
              || (i.description  like "%" + k + "%")
          )} getOrElse q

        q = params.shopId.map{ sid => q.filter(
          _.shopId === sid
          )} getOrElse q

        Right(q.run.toList)
      }
    } catch {
      case e: SQLException =>
        Left(databaseError(e))
    }
  }

I am not sure it is the best answer because of var q

Answer 2

As I understood you correct, you want to make a filter by optional fields. Your second attempt is quiet closer to reality, the first has incorrect matching, you compare option fields to non option. You've answered your own answer while I was writing this response :)

I'd like to recommend you this MaybeFilter https://gist.github.com/cvogt/9193220

Or here is modified version: https://github.com/neowinx/hello-slick-2.1-dynamic-filter/blob/master/src/main/scala/HelloSlick.scala#L3-L7

Maybe this can help you to solve your problem in a more generic way.