scalikejdbc query example for a grid support filter, order by column and paging

scalikejdbc.scala

  /**
    * Query data for a grid support filter, order by column and paging.
    * @param tableQuery
    * @param session
    * @return
    */
  def query(tableQuery: TableQueryDto)(implicit session: DBSession): TableData = {

    var sql = select.from(User as u).toSQLSyntax

    def buildFilter(filters: List[TableFilterDto]): SQLSyntax = filters match {
      case head :: tails =>
        val sql = head.field match {
          case "id" => SQLSyntax.eq(u.id, head.asLong)
          case "fullName" => SQLSyntax.like(u.fullName, head.asLikeValue)
          case "agencyId" => SQLSyntax.eq(u.agencyId, head.asLong)
          case "nameOrEmail" => SQLSyntax.roundBracket(SQLSyntax.like(u.fullName, head.asLikeValue).or.like(u.email, head.asLikeValue))
          case _ => throw new Exception("Invalid filtering key: " + head.field)
        }
        sql.and(buildFilter(tails))
      case Nil => sqls"1 = 1"
    }

    if(tableQuery.filters.nonEmpty) {
      sql = sql.append(SQLSyntax.where(buildFilter(tableQuery.filters)))
    }

    sql = sql.orderBy(u.selectDynamic(tableQuery.sortBy))

    sql = if (tableQuery.sortAsc) sql.asc else sql.desc

    val total = sql"select count(*) from ($sql) x".map(rs => rs.long(1)).single.apply().get

    val users = sql"$sql limit ${tableQuery.numberOfRow} offset ${tableQuery.start}".map(User.create(u)).list().apply()

    TableData(total, tableQuery.numberOfRow, Pickle.intoString(users))
  }