How to filter a result set multiple times based on conditions?

[Ryan McCullagh]

Hello,

I've come to stopping point in trying to implement a multiple filter function. This is my class, and the method I'm trying to filter on is the `all` method. For example, in the `all` method I have many optional values that can be passed in, and I want to be able to do `if` statements on all of these `Optional`'s and then `filter` the query result based on that. 

import slick.backend.DatabaseConfig
import slick.driver.{MySQLDriver}
import slick.driver.MySQLDriver.api._


import scala.concurrent.Future
import scala.concurrent.ExecutionContext.Implicits.global


/**
  * Created by rmccullagh on 3/29/17.
  */
object JurisdictionRepo extends {
  val profile = slick.driver.MySQLDriver
} with JurisdictionRepo


// RuleRepo is coded to "attach" to an owner
object JurisdictionRuleRepo extends {
  val profile = slick.driver.MySQLDriver
  val ownerTableName = JurisdictionRepo.tableName
} with RuleRepo


trait JurisdictionRepo {
  private val dbConfig: DatabaseConfig[MySQLDriver] = DatabaseConfig.forConfig("pnga-master-data")
  private val db = dbConfig.db


  val profile: slick.driver.JdbcProfile
  val tableName = "jurisdiction"


  def add(jurisdictionRow: JurisdictionRow): Future[Unit] = db.run(query += jurisdictionRow).map { _ => () }


  def delete(id: String): Future[Int] = db.run(query.filter(_.id === id).delete)


  def get(id: String): Future[Option[JurisdictionRow]] = db.run(query.filter(_.id === id).result.headOption)


  def update(id: String, jurisdiction: JurisdictionRow) = {
    db.run(query.filter(_.id === id).map(row =>
      (row.parentId, row.name, row.code, row.currency)
    ).update(
      (jurisdiction.parentId, jurisdiction.name, jurisdiction.code, jurisdiction.currency)
    ))
  }

  def all(name: Option[String],

          parentId: Option[String],

          limit: Option[String],

          offset: Option[String],

          sortBy: Option[String],

          sortDirection: Option[String]) = {

    val result = query

    if(name.isDefined) {

      result = result.filter(_.name === name)

    }

    db.run(result.result)

  }

  import profile.api._


  lazy val schema: profile.SchemaDescription = query.schema


  case class JurisdictionRow(id: String,
                             parentId: Option[String],
                             name: String,
                             code: String,
                             currency: String)


  class Jurisdiction(_tableTag: Tag) extends Table[JurisdictionRow](_tableTag, tableName) {
    val id: Rep[String] = column[String](s"${tableName}_id", O.PrimaryKey, O.Length(36, varying=true))
    val parentId = column[Option[String]]("parent_id", O.Default(None), O.Length(36, varying=true))
    val name: Rep[String] = column[String]("name", O.Length(255, varying=true))
    val code: Rep[String] = column[String]("code", O.Length(255, varying=true))
    val currency: Rep[String] = column[String]("currency")


    def * = (id, parentId, name, code, currency) <> (JurisdictionRow.tupled, JurisdictionRow.unapply _)
  }


  val jurisdictions = TableQuery[Jurisdiction]


  lazy val query = new TableQuery(tag => new Jurisdiction(tag))


}

Notice, the `all` method attempts to filter multiple times, by checking if the `name.isDefined` is true. How can I do this?

[Richard Dallaway]

Hello Ryan

Something called the “maybe filter” sounds like what you need:

 https://gist.github.com/studiodev/5c6471cb1d823914ee28

Any use?

Cheers

Richard

--

---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/74d77a19-6b99-4693-84c5-d53b2cfc24dc%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ryan McCullagh]

Hi Richard,

Thanks for your reply. I've attempted to use the MaybeFilter class in my code, and have changed my `all` method to the following;

  def all(name: Option[String],
          parentId: Option[String],
          limit: Option[String],
          offset: Option[String],
          sortBy: Option[String],
          sortDirection: Option[String]) = {

    val jurisdictionList = new java.util.ArrayList[JurisdictionRow]()


   MaybeFilter(jurisdictions)
      .filter(name)(v => d => d.name === v)
      .query.map { row => {
        jurisdictionList.add(
          this.JurisdictionRow(row.id, Some(row.parentId), row.name, row.code, row.currency)
        )
      }
    }
    jurisdictionList
  }

However, this does not compile. How can I use the results of the `MaybeFilter`? My goal is to obtain a list of `JurisdictionRow` records.

[Richard Dallaway]

Hi Ryan

Can you say what the error is, or do you have the code (or a version of it) in a public repository?

My guess is... Where you are calling map...the purpose of the map there is changing the query to select a set of columns. The result of map used there is a query. The code you have is appending things into a list. I suspect the problem is around there. 

Richard

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/a19cb9da-ee34-4ea9-8783-15649879883b%40googlegroups.com.