memo

check.scala

case class TestMember(id: Int, testCompanyId: Int, name: String, age: Int, testCompany: Option[TestCompany] = None)

object TestMember extends SQLSyntaxSupport[TestMember] with SimpleSearch[TestMember] {
  override val tableName = "test_members"
  def apply(rn: ResultName[TestMember])(rs: WrappedResultSet): TestMember = autoConstruct(rs, rn, "testCompany")

  def create(testCompanyId: Int, name: String, age: Int)(implicit dBSession: DBSession) = {
    withSQL {
      insert.into(TestMember).namedValues(
        column.testCompanyId -> testCompanyId,
        column.name -> name, 
        column.age -> age
      )
    }.update().apply()
  }
  
  def joined(searchForm: Map[String, Any])(implicit dBSession: DBSession) = {
    val m = TestMember.syntax("m")
    val c = TestCompany.syntax("c")
    
    select.from(TestMember as m)
      .leftJoin(TestCompany as c).on(m.testCompanyId, c.id)
      .where(SQLAutoCondition(Seq(m, c))(searchForm))
  }
}

case class TestCompany(id: Int, name: String, capital: Int)

object TestCompany extends SQLSyntaxSupport[TestCompany] with SimpleSearch[TestCompany] {
  override val tableName = "test_companies"
  def apply(rn: ResultName[TestCompany])(rs: WrappedResultSet): TestCompany = autoConstruct(rs, rn)
  def create(id: Int, name: String, capital: Int)(implicit dBSession: DBSession) = {
    withSQL {
      insert.into(TestCompany).namedValues(column.id -> id, column.name -> name, column.capital -> capital)
    }.update().apply()
  } 
}

it should "operators" in {
    DB autoCommit { implicit s =>
      try sql"drop table ${TestCompany.table}".execute.apply()
      catch { case e: Exception => }
      sql"create table ${TestCompany.table} (id int not null auto_increment, name varchar(256), capital int)".execute.apply()

      try sql"drop table ${TestMember.table}".execute.apply()
      catch { case e: Exception => }
      sql"create table ${TestMember.table} (id int not null auto_increment, test_company_id int not null, name varchar(256), age int not null)".execute.apply()
    }
    
    DB localTx { implicit s =>
      TestCompany.create(1, "ScalikeJDBC Core", 100)
      TestCompany.create(2, "ScalikeJDBC Interpolation", 50)
      TestCompany.create(3, "ScalikeJDBC Interpolation Macro", 30)
      TestCompany.create(4, "ScalikeJDBC Syntax Support Macro", 30)
      TestCompany.create(5, "Interpolation", 30)
      
      TestCompany.autoSearch(Map()).toSQL.map({
        rs => TestCompany(TestCompany.syntax.resultName)(rs) 
      }).list.apply().size should equal(5)

      TestCompany.autoSearch(Map("name_cont" -> "Macro")).toSQL.map({
        rs => TestCompany(TestCompany.syntax.resultName)(rs)
      }).list.apply().size should equal(2)

      TestCompany.autoSearch(Map("name_start_or_name_end" -> "Interpolation")).toSQL.map{ rs => rs }.list.apply().size should equal(2)

      TestCompany.autoSearch(Map("name_start_or_name_end" -> "Interpolation", "capital_lt" -> 50)).toSQL.map{ rs => rs }.list.apply().size should equal(1)

      TestCompany.autoSearch(Map("name_start_or_name_end" -> "Interpolation", "capital_lteq" -> 50)).toSQL.map{ rs => rs }.list.apply().size should equal(2)

      TestCompany.autoSearch(Map("name_start_or_name_end" -> "Interpolation", "capital_gteq" -> 50)).toSQL.map{ rs => rs }.list.apply().size should equal(1)

      TestCompany.autoSearch(Map("name_start_or_name_end" -> "Interpolation", "capital_gt" -> 50)).toSQL.map{ rs => rs }.list.apply().size should equal(0)


      TestMember.create(1, "Bob", 25)
      TestMember.create(1, "Alice", 23)
      TestMember.create(2, "John", 32)
      TestMember.create(5, "ScalikeJDBC", 3)

      TestMember.joined(Map("age_eq" -> 25)).toSQL.map { rs => rs }.list.apply().size should equal(1)

      TestMember.joined(Map("name_cont" -> "Bob")).toSQL.map { rs => rs }.list.apply().size should equal(1)
      TestMember.joined(Map("c_name_cont" -> "Bob")).toSQL.map { rs => rs }.list.apply().size should equal(0)
      TestMember.joined(Map("c_name_cont" -> "ScalikeJDBC")).toSQL.map { rs => rs }.list.apply().size should equal(3)
    }
  }

model.scala

case class Member(id: Long, name:String, age:Int)

object Member extends SQLSyntaxSupport[Member] with SimpleSearch[Member] {
  override val tableName = "members"
  
  def apply(rn: ResultName[Member])(rs: WrappedResultSet): Member = autoConstruct(rs, rn)
}

sample.scala

import scalikejdbc._

trait SimpleSearch[A] { self: SQLSyntaxSupport[A] =>
  def autoSearch(searchForm: Map[String, Any])(implicit dBSession: DBSession): ConditionSQLBuilder[Nothing] = {
    select.from(self as syntax).where(SQLAutoCondition(syntax)(searchForm))
  }
}

object SQLAutoCondition {
  def columnOption(s: QuerySQLSyntaxProvider[_, _], name: String): Option[SQLSyntax] = {
    // TODO:
    s.columns.find(_.value.equalsIgnoreCase(name)).map{_ => s.column(name)}
  }
  
  def apply(s: QuerySQLSyntaxProvider[_, _])(pairs: Map[String, Any]): SQLSyntax = autoCondition(s)(pairs)
  def apply(ss: Seq[QuerySQLSyntaxProvider[_, _]])(pairs: Map[String, Any]): SQLSyntax = autoCondition(ss)(pairs)

  def autoCondition(s: QuerySQLSyntaxProvider[_, _])(pairs: Map[String, Any]): SQLSyntax = {
    pairs.withFilter {
      case (_, None) => false
      case _ => true
    }.map {
      case (k, Some(v)) => (k, v)
      case (k, v) => (k, v)
    }.flatMap { case (k, v) =>
      smartOperator(s)(k, v)
    }.foldLeft(sqls"1 = 1") { (acc, a) => acc.and(a)}
  }

  def autoCondition(ss: Seq[QuerySQLSyntaxProvider[_, _]])(pairs: Map[String, Any]): SQLSyntax = {
    pairs.withFilter {
      case (_, None) => false
      case _ => true
    }.map {
      case (k, Some(v)) => (k, v)
      case (k, v) => (k, v)
    }.flatMap { case (k, v) =>
      lazy val secondary = ss.map {s => Tuple2[QuerySQLSyntaxProvider[_, _], String](s, s.tableAliasName + "_")
      }.filter { 
        case (_, prefix) => k.startsWith(prefix) 
      }.map { case (alias, prefix) => Tuple2[QuerySQLSyntaxProvider[_, _], String](alias, k.stripPrefix(prefix)) 
      }.find { case (s, pk) => smartOperator(s)(pk, v).isDefined 
      }.map { case (s, pk) => smartOperator(s)(pk, v) }
      
      ss.find { s => smartOperator(s)(k, v).isDefined }
        .map { s => smartOperator(s)(k, v) }.orElse(secondary)
      
    }.foldLeft(sqls"1 = 1") { (acc, a) => acc.and(a)}
  }

  def operator(s: QuerySQLSyntaxProvider[_, _])(k:String, v:Any): Option[SQLSyntax] = {
    // TODO: 全部
    contains(s)(k, v)
      .orElse(equals(s)(k, v))
      .orElse(graterThan(s)(k, v))
      .orElse(graterThanEquals(s)(k, v))
      .orElse(lesserThan(s)(k, v))
      .orElse(lesserThanEquals(s)(k, v))
      .orElse(start(s)(k, v))
      .orElse(end(s)(k, v))
  }
  
  def smartOperator(s: QuerySQLSyntaxProvider[_, _])(k:String, v:Any): Option[SQLSyntax] = orOperator(s)(k, v).orElse(singleOperator(s)(k, v))

  def singleOperator(s: QuerySQLSyntaxProvider[_, _])(k:String, v:Any): Option[SQLSyntax] = {
    operator(s)(k, v)
  }

  def orOperator(s: QuerySQLSyntaxProvider[_, _])(k:String, v: Any): Option[SQLSyntax] = {
    if(k.contains("_or_")) {
      Some(k.split("_or_").flatMap {pk => operator(s)(pk, v)}.foldLeft(sqls"1 = 0") { (acc, a) => acc.or(a)})
    } else {
      None
    }
  }

  def contains(s: QuerySQLSyntaxProvider[_, _])(k:String, v: Any): Option[SQLSyntax] = {
    val suffixes = Seq("_cont", "_contains")
    suffixes.find(k.endsWith(_)).headOption match {
      case None => None
      case Some(suffix) => {
        columnOption(s, k.stripSuffix(suffix)).map { col => sqls.like(col, s"""%${v}%""") }
      }
    }
  }

  def start(s: QuerySQLSyntaxProvider[_, _])(k:String, v: Any): Option[SQLSyntax] = {
    val suffixes = Seq("_start")
    suffixes.find(k.endsWith(_)).headOption match {
      case None => None
      case Some(suffix) => {
        columnOption(s, k.stripSuffix(suffix)).map { col => sqls.like(col, s"""${v}%""") }
      }
    }
  }

  def end(s: QuerySQLSyntaxProvider[_, _])(k:String, v: Any): Option[SQLSyntax] = {
    val suffixes = Seq("_end")
    suffixes.find(k.endsWith(_)).headOption match {
      case None => None
      case Some(suffix) => {
        columnOption(s, k.stripSuffix(suffix)).map { col => sqls.like(col, s"""%${v}""") }
      }
    }
  }

  def equals(s: QuerySQLSyntaxProvider[_, _])(k:String, v: Any): Option[SQLSyntax] = {
    val suffixes = Seq("_eq", "_equals")
    suffixes.find(k.endsWith(_)).headOption match {
      case None => None
      case Some(suffix) => {
        columnOption(s, k.stripSuffix(suffix)).map { col => sqls.eq(col, v) }
      }
    }
  }

  def graterThan(s: QuerySQLSyntaxProvider[_, _])(k:String, v: Any): Option[SQLSyntax] = {
    val suffixes = Seq("_gt", "_grater_than")
    suffixes.find(k.endsWith(_)).headOption match {
      case None => None
      case Some(suffix) => {
        columnOption(s, k.stripSuffix(suffix)).map { col => sqls.gt(col, v) }
      }
    }
  }

  def graterThanEquals(s: QuerySQLSyntaxProvider[_, _])(k:String, v: Any): Option[SQLSyntax] = {
    val suffixes = Seq("_gteq", "_grater_than_equals")
    suffixes.find(k.endsWith(_)).headOption match {
      case None => None
      case Some(suffix) => {
        columnOption(s, k.stripSuffix(suffix)).map { col => sqls.gt(col, v).or(sqls.eq(col, v)) }
      }
    }
  }

  def lesserThan(s: QuerySQLSyntaxProvider[_, _])(k:String, v: Any): Option[SQLSyntax] = {
    val suffixes = Seq("_lt", "lesser_than")
    suffixes.find(k.endsWith(_)).headOption match {
      case None => None
      case Some(suffix) => {
        columnOption(s, k.stripSuffix(suffix)).map { col => sqls.lt(col, v) }
      }
    }
  }

  def lesserThanEquals(s: QuerySQLSyntaxProvider[_, _])(k:String, v: Any): Option[SQLSyntax] = {
    val suffixes = Seq("_lteq", "lesser_than_equals")
    suffixes.find(k.endsWith(_)).headOption match {
      case None => None
      case Some(suffix) => {
        columnOption(s, k.stripSuffix(suffix)).map { col => sqls.lt(col, v).or(sqls.eq(col, v)) }
      }
    }
  }
}

usage.scala

case class MemberSearchParameter(name_cont: String, age_gteq: Int) {
  def toMap: Map[String, Any] = this.getClass.getDeclaredFields.map(_.getName).zip(this.productIterator.toList).toMap
}

class MemberController extends Controller {
  val form: Form[MemberSearchParameter] = Form(
    mapping(
      // "name_cont" -> default(text, "")
      "name_cont" -> default(text, ""),
      "age_gteq" -> default(number, 0)
    )(MemberSearchParameter.apply)(MemberSearchParameter.unapply)
  )
  
  def index = Action {
    val members = Member.autoSearch(form.bindFromRequest.get.toMap).toSQL.map { rs => /* ... */ }.list.apply()
    Ok(views.html.members.index(members))
  }
}