Slick dynamic queries

[siva pradeep]

Hi,

I am trying to build dynamic queries using slick but didnt find any leads.
Can some one help me to achieve the following use case.

UseCase:

The user will be given a screen to choose a list of fields on the front end.  Let us say the user is given 10 fields from two tables.

Table A                  Table B
Field 1                    Field 6
Field 2                    Field 7
Field 3                    Field 8
Field 4                    Field 9
Field 5                    Field 10

I want to build a query based on user selection. let us say if the user select Field 1, Field 3, Field 9, Field 10

The query should be formed as (select a.field1, a.field3, b.field9, b. field10 from TableA a, TableB b where ..............)

The idea is build a query dynamically out of only those fields  the user has selected rather than selecting all the fields.

Is there a mechanism to build a query like this in slick?

Appreciate your help.

Thanks,
Siva Pradeep

[Christopher Vogt]

Hi,

> I want to build a query based on user selection. let us say if the user
> select Field 1, Field 3, Field 9, Field 10

this could lead to arbitrary combinations of field, dynamically
selected. The problem with this is, that Slick's type-safety requires
the type to be statically known, but it wouldn't be. We don't have a
good answer to this yet. You may want to generate SQL. Or request too
much data from the database using Slick and then filter out the selected
columns before returning it. We may add support for constructing queries
dynamically at some point, but we don't have anything scheduled yet.

If you only wanted to filter based on dynamic conditions, the result
type wouldn't change and thus would be possible with Slick as it is now.

Chris

[Jeroen ter Voorde]

Hi,

I did the following to create a dynamic select list (using 2.1M2 with the hello-slick activator template). It *seems* to

work fine (but it is quite untested except for this code). What do you think? 

Jeroen

import scala.slick.driver.H2Driver.simple._

import scala.slick.lifted.{FlatShapeLevel, Shape, ProductNodeShape}

class ListShape[Level <: ShapeLevel, M <: IndexedSeq[Any], U <: IndexedSeq[Any], P <: IndexedSeq[Any]](val shapes: Seq[Shape[_, _, _, _]]) extends ProductNodeShape[Level, IndexedSeq[Any], M, U, P] {

  override def getIterator(value: IndexedSeq[Any]) = value.iterator

  def getElement(value: IndexedSeq[Any], idx: Int) = value(idx)

  def buildValue(elems: IndexedSeq[Any]) = elems

  def copy(shapes: Seq[Shape[_ <: ShapeLevel, _, _, _]])  = new ListShape(shapes)

}

object ListShape {

  def apply(numColumns: Int) : Shape[FlatShapeLevel, IndexedSeq[Any], IndexedSeq[Any], IndexedSeq[Any]] = {

    val columnShapes = (0 until numColumns).map(_ => Shape.columnShape[Any, FlatShapeLevel])

    new ListShape(columnShapes.toVector)

  }

}

object CaseClassMapping extends App {

  // the base query for the Users table

  val users = TableQuery[Users]

  val db = Database.forURL("jdbc:h2:mem:hello", driver = "org.h2.Driver")

  db.withSession { implicit session =>

    

    // create the schema

    users.ddl.create

    

    // insert two User instances

    users += User("John Doe", Some("Dorpstreet"), Some(13))

    users += User("Fred Smith", Some("42 Elmstreet"), None)

    val columns = Vector(("NAME", "string"), ("ID", "int"), ("ADDRESS", "string?"), ("HOUSENO", "int?"))

    implicit val shape = ListShape(columns.size)

    val q = users.filter(t => t.id >= 1.bind).map { u =>

      columns.map( c => {

        c._2 match {

          case "string" => u.column[String](c._1)

          case "string?" => u.column[Option[String]](c._1)

          case "int" => u.column[Int](c._1)

          case "int?" => u.column[Option[Int]](c._1)

          case _ => throw new RuntimeException("Unsupported column type")

        }

      })

    }

    println(q.selectStatement)

    val list = q.list.asInstanceOf[List[Product]].map(_.productIterator.toList)

    println(list)

  }

  

}

case class User(name: String, address: Option[String], houseno: Option[Int], id: Option[Int] = None)

class Users(tag: Tag) extends Table[User](tag, "USERS") {

  // Auto Increment the id primary key column

  def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)

  // The name can't be null

  def name = column[String]("NAME", O.NotNull)

  def address = column[Option[String]]("ADDRESS")

  def houseno = column[Option[Int]]("HOUSENO")

  // the * projection (e.g. select * ...) auto-transforms the tupled

  // column values to / from a User

  def * = (name, address, houseno, id.?) <> (User.tupled, User.unapply)

}

Op donderdag 27 maart 2014 17:06:59 UTC+1 schreef Christopher Vogt: