Dynamically Constructed Query Using DSL

[Peter Hancox]

Hoping you might be able to give me some pointers on whether it is possible
to dynamically construct these queries in a type safe manner using the query
DSL, or whether I'm wasting my time and should just dynamically construct the
query using string manipulation.  I have done this for hibernate using plain
old string manipulation and that works fine.

My use case is where the UI has a list screen and 1-n search criteria fields
which, if not blank, should form the where clause of the query.  This scenario
is very common so I want to avoid boiler plate code as much as possible. e.g.,

ignoring search fields with no criteria, converting to lower case, etc.

Here is my current function showing working code for a simplistic single search
criteria scenario.  It also shows code that looks like what I want but doesn't
compile.  Is it possible to dynamically construct the query from a list of
supplied search criteria using the query DSL?

  def getSearchQuery[T](entity: Entity[_, Persisted, T], criteriaList: List[(ColumnInfo[T, String], TextField)]) = {

    // This works for the simple case where we just look at the list head.
    val criteria = criteriaList(0)
    (
      select from entity where
      (lower(criteria._1) like s"${criteria._2.value.get.toString.toLowerCase}%"))

    // But what we really want is to dynamically construct the where clause in a
    // type-safe manner using the query DSL.

    //    (
    //      select from entity where
    //      (for {
    //        (ci, v) <- criteriaList
    //        if (v.value.get.toString.length > 0)
    //      } yield (lower(ci) like s"${v.value.get.toString.toLowerCase}%"))
    //    )
  }

The application then runs the query as follows:

    val p = SportZmanInternetUI.personEntity
    val persons = SportZmanInternetUI.queryDao.query(getSearchQuery(p, List(
      (p.surname, txtSearchSurname), (p.firstName, txtSearchFirstName))))

REGARDS

Peter

[Konstantinos Kougios]

Hi Peter, quick reply: the select DSL is a mutable Builder. Does http://code.google.com/p/mapperdao/wiki/DynamicQueries answer your question?

--
You received this message because you are subscribed to the Google Groups "mapperdao" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapperdao+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Peter Hancox]

That's certainly a step in the right direction but the first problem I'm grappling with is that I want to construct 

the query in a reusable function.  What should the function return type be?

It looks like it should be a Query.Where class but this is declared protected[mapperdao] so my code can't access it.

I'm letting it default at the moment (i.e., not specifying a return type) but that appears to be causing me other problems.

[Konstantinos Kougios]

ok, I can make the Query.Where class public. But what other issues do you get?

[Peter Hancox]

Just for initial concept testing I have the function declared as:

  def getSearchQuery[ID, T](entity: Entity[ID, Persisted, T], criteriaList: List[(ColumnInfo[T, String], TextField)]) = {

    val filteredCriteria = criteriaList filter (_._2.value.get.toString.length > 0)

    val selectClause = select from entity

    filteredCriteria.length match {

      case 0 => selectClause

      case 1 => selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.value.get.toString.toLowerCase}%")

      case _ => selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.value.get.toString.toLowerCase}%")

    }

}

If I attempt to invoke it as:

      val s = suburbEntity

      val txtSearchField = new TextField

        val query = getSearchQuery(s, List(

          (s.name, txtSearchField), (s.name, txtSearchField)))

      val results = queryDao.query(query)

i.e., with no return type defined I have a compiler error (differences between the two alternatives in bold italic):

overloaded method value query with alternatives: 

[ID(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query), PC(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query) <: com.googlecode.mapperdao.Persisted, T(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)](qe: com.googlecode.mapperdao.Query.Builder[ID(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query),PC(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query),T(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)])List[T(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query) with PC(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)] 

<and>   

[ID(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query), PC(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query) <: com.googlecode.mapperdao.Persisted, T(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)](qe: com.googlecode.mapperdao.Query.Where[ID(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query),PC(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query),T(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)])List[T(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query) with PC(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)(in method query)]  

cannot be applied to (com.googlecode.mapperdao.Query.OrderBy[_12])

 

I can specify a return type of:

def getSearchQuery[ID, T](entity: Entity[ID, Persisted, T], criteriaList: List[(ColumnInfo[T, String], TextField)]): Builder[ID, Persisted, T] = {

This fixes the compiler error in the calling code, but I can't coerce the function return to a Builder (which is public)

Making Query.Where public might not be the right answer?  If you think it might be the right solution then I can hack your code here to try

it rather than having you do a build.

[Peter Hancox]

Invoking the function with this code:

    val s = suburbEntity

    val txtSearchField = new TextField

    val query = getSearchQuery(s, List(

      (s.name, txtSearchField), (s.name, txtSearchField)))

    info(query.toString)

    val results = queryDao.query(query)

With the function defined as:

  def getSearchQuery[ID, T](entity: Entity[ID, Persisted, T], criteriaList: List[(ColumnInfo[T, String], TextField)]) = {

    val criteria = criteriaList(0)

    val test1 = (

      select from entity where

      (lower(criteria._1) like s"${criteria._2.value.get.toString.toLowerCase}%"))

    test1

  }

No compiler errors and runs OK.

With the function defined as:

  def getSearchQuery[ID, T](entity: Entity[ID, Persisted, T], criteriaList: List[(ColumnInfo[T, String], TextField)]) = {

    val filteredCriteria = criteriaList filter (_._2.value.get.toString.length > 0)

    val selectClause = select from entity

    val test2 = filteredCriteria.length match {

      case 0 => selectClause

      case 1 => selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.value.get.toString.toLowerCase}%")

      case _ => selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.value.get.toString.toLowerCase}%")

    }

    test2

  }

Compilation error in calling code "overloaded method value query with alternatives".  As per previous post can't decide between Query.Builder or Query.Where

Made Query.Where public and used it to specify the return type:

  def getSearchQuery[ID, T](entity: Entity[ID, Persisted, T], criteriaList: List[(ColumnInfo[T, String], TextField)]): Where[ID, Persisted, T] = {

    val filteredCriteria = criteriaList filter (_._2.value.get.toString.length > 0)

    val selectClause = select from entity

    val test2 = filteredCriteria.length match {

      case 0 => selectClause

      case 1 => selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.value.get.toString.toLowerCase}%")

      case _ => selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.value.get.toString.toLowerCase}%")

    }

    test2

  }

Now the function won't compile

type mismatch;  found   : com.googlecode.mapperdao.Query.OrderBy[_12] where type _12 >: com.googlecode.mapperdao.Query.Builder[ID,com.googlecode.mapperdao.Persisted,T] with com.googlecode.mapperdao.Query.Where[ID,com.googlecode.mapperdao.Persisted,T] <: com.googlecode.mapperdao.Query.OrderBy[_ >: com.googlecode.mapperdao.Query.Builder[ID,com.googlecode.mapperdao.Persisted,T] with com.googlecode.mapperdao.Query.Where[ID,com.googlecode.mapperdao.Persisted,T] <: com.googlecode.mapperdao.Query.OrderBy[_ >: com.googlecode.mapperdao.Query.Builder[ID,com.googlecode.mapperdao.Persisted,T] with com.googlecode.mapperdao.Query.Where[ID,com.googlecode.mapperdao.Persisted,T] <: Object]]  required: com.googlecode.mapperdao.Query.Where[ID,com.googlecode.mapperdao.Persisted,T]

Then tried a return type of OrderBy[Builder[ID, Persisted, T]] but then I had a similar type mismatch in the function definition AND the 

overloaded method with alternative error in the calling code.

So I can make it work if I just return a simple query constructed as it would have been done inline.  But if I build the query up by components

I need to work out what the returned type should be or find a way to disambiguate the alternative method calls to the queryDao query method.

Thanks very much for your help.

[Peter Hancox]

Getting closer in my understanding I think.  With the function defined as:

  def getSearchQuery[ID, T](entity: Entity[ID, Persisted, T], criteriaList: List[(ColumnInfo[T, String], TextField)]) = {

    val filteredCriteria = criteriaList filter (_._2.value.get.toString.length > 0)

    val selectClause = select from entity

    val test2 = filteredCriteria.length match {

      case 0 => selectClause

      case 1 => selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.value.get.toString.toLowerCase}%")

      case _ => selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.value.get.toString.toLowerCase}%")

    }

    test2

  }

Correct me if I'm wrong but case 0 will return a "Builder" instance whereas case 1 and the default case will return a "Where" instance.  Because

the compiler won't know until run-time, it infers the nearest common class for both those cases which appears to be the "OrderBy" trait.  I believe 

I've been misinterpreting the compiler message given when trying to pass the functions return value to queryDao.query.  I thought it was complaining

because it didn't know which overloaded query method to use; but I think the problem is that neither of them is applicable to an "OrderBy"?

What I need is to be able to return case 0 as a "Where" instead of a "Builder".  Possibly with a where clause that always evaluates to true but that

seems like a bit of a hack!  I'm guessing a bit as to how mapperdao classes are structured for the query DSL but is there any way to create a useable

"Where" instance that has a "null" where clause (i.e., just a simple select)?

[Peter Hancox]

Think I have it working now (at least as far as having the correct types).  Still need to put the meat into the 

final function so it's more than just a prototype.  However, I will need "Where" to be public as I can't see any 

other way of achieving this.

  def getSearchQuery[ID, T](entity: Entity[ID, Persisted, T], criteriaList: List[(ColumnInfo[T, String], String)]) = {

    val filteredCriteria = criteriaList filter (_._2.length > 0)

    val selectClause = select from entity

    val test = filteredCriteria.length match {

      case 0 => new Where(selectClause)

      case 1 => selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.toLowerCase}%")

      case _ => selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.toLowerCase}%")

    }

    test

  }

Managing case 0 was what was causing me the grief; and so far this appears to work.  Let me know if there is a better way.

[Konstantinos Kougios]

I'll add a common trait on my side on both Where and Builder and then queryDao can take this trait as parameter, simplifying things

[Konstantinos Kougios]

as the Query is an mutable builder, I might also code it like this:

val selectClause = select from entity

if (filteredCriteria.length==1) selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.toLowerCase}%") // mutates
if (filteredCriteria.length>1) selectClause where (lower(filteredCriteria(0)._1) like s"${filteredCriteria(0)._2.toLowerCase}%")

selectClause // as mutated above

On 30/05/13 14:14, Peter Hancox wrote:

[Peter Hancox]

Most of this code was to sort out the function definition rather than its body.  I'm envisaging a foldLeft on the list doing

most of the heavy lifting in the final solution.  A few more simple test cases then onto that .....

Thanks for your help.

[Konstantinos Kougios]

Hi Peter,

    Just deployed a snapshot that I hope will help.

Now the query builder extends a public trait QueryBuilder[ID,PC,T] where ID,PC,T are same as the entity you query, i.e. Int,SurrogateIntId,MyClass. Better leave scala to infer types.

so

select from entity
and
select from entity where ...

both are of type QueryBuilder.

queryDao.query accepts this type and hence you won't have to treat Query.Where and Query.Builder differently.

Snapshot is available, so please let me know if it works.

On 30/05/13 14:14, Peter Hancox wrote:

[Peter Hancox]

Still not quite there.  The snapshot works fine and solves the type issues in the case of my test code.  However, it

did introduce some issues with my intended solution (which I'll come back to).

Given the problems with my intended solution I thought I'd first try your suggestion to use the mutability of the query builder.

  def getSearchQuery[ID, T](entity: Entity[ID, Persisted, T], criteriaList: List[(ColumnInfo[T, String], String)]) = {

    val filteredCriteria = criteriaList filter (_._2.length > 0)

    val selectClause = select from entity

    filteredCriteria foreach (whereClause => {

      debug("before: " + selectClause)

      selectClause where (lower(whereClause._1) like s"${whereClause._2.toLowerCase}%")

      debug(" after: " + selectClause)

    })

    selectClause

  }

This fails where the list contains 2 or more elements.  Seems as if mutating the select clause with a where clause is replacing the 

previous where clause so I see:

before: select from SuburbEntity(Suburb,com.dtc.deltasoft.entity.Suburb) join List() where None

 after: select from SuburbEntity(Suburb,com.dtc.deltasoft.entity.Suburb) join List() where Some(Where(SqlFunctionOp(SqlFunctionValue(lower,List(ColumnInfo(Column(SuburbEntity(Suburb,com.dtc.deltasoft.entity.Suburb),NAME,class java.lang.String),<function1>,class java.lang.String))),Operand( like ),aab%)))

before: select from SuburbEntity(Suburb,com.dtc.deltasoft.entity.Suburb) join List() where Some(Where(SqlFunctionOp(SqlFunctionValue(lower,List(ColumnInfo(Column(SuburbEntity(Suburb,com.dtc.deltasoft.entity.Suburb),NAME,class java.lang.String),<function1>,class java.lang.String))),Operand( like ),aab%)))

 after: select from SuburbEntity(Suburb,com.dtc.deltasoft.entity.Suburb) join List() where Some(Where(SqlFunctionOp(SqlFunctionValue(lower,List(ColumnInfo(Column(SuburbEntity(Suburb,com.dtc.deltasoft.entity.Suburb),POSTCODE,class java.lang.String),<function1>,class java.lang.String))),Operand( like ),200%)))

And back to the problems with my original approach.  I had intended to use folding similar to 

    val selectClause = select from entity

    criteriaList.filter(_._2.length > 0).

      foldLeft(selectClause)((q, w) => q where (lower(w._1) like s"${w._2.toLowerCase}%"))

The problem with this is that "selectClause" is inferred as a Query.Builder which means that the folding function has to 

result in a Query.Builder which it doesn't.  If I cast "selectClause" to QueryBuilder trait then the "where" function isn't

defined.

I can probably work around this by handling the 0 and 1 element cases specifically and use foldLeft for 2..n cases where

the accumulator should now be a "Where"?

Alternately, would it make sense to define an abstract "where" function on the QueryBuilder trait?

What are your thoughts on what is happening in the case of the mutable solution?  Is that the behaviour you were expecting and

I'm doing something wrong?

REGARDS

Peter

[Konstantinos Kougios]

Hi,

    Indeed you can't call "where" twice as that overwrites the first call.

Instead the calls should be like:

val q = select from jpe where jpe.id === 1
q or jpe.id === 2
q or (jpe.id === 5 or jpe.id === 6)
q orderBy (jpe.id, desc)

In your case you will have a problem doing the initial "where" as you have a variable number of clauses , 0..n . I'll try to think if there is a solution to skip calling "where" once before been able to call "or"/"and" and fix the type issues you're facing. I'll probably have to write a similar test suite.

Cheers

[Peter Hancox]

Duh!!!

Sorry, it appears my brain has gone on holidays.  Should have been obvious that I needed one "where" followed by 0..n "and" 

clauses.  Spending too much time focusing on the type issues.

May as well just bite the bullet and handle each of the 0, 1, and 2..n cases differently.

An approach to being able to call "and"/"or" without an initial "where" would make it easier but I don't think it would be

worth messing up other code just for this. 

[Konstantinos Kougios]

Had a look, there is a way to mutate the builder to achieve what you want, i.e.

        val s = (select from jpe)
        val w= (s where)
        w(jpe.name === "x1")
        w or (jpe.name === "x2")

(the parenthesis are necessary to help scala with the DSL)

So now s contains the select clause and you can call s.join to join if necessary.
w contains the where clause and you have to start with a clause followed by and/or and more clauses. It's like constructing a sql string.

Note : don't create w if you don't have any where clauses.

Cheers

[Peter Hancox]

OK I think we're done.  Many thanks for your help.

  type CriteriaListElement[T] = (ColumnInfo[T, String], String)

  def getSearchQuery[ID, T](entity: Entity[ID, Persisted, T], criteriaList: List[CriteriaListElement[T]]) = {

    val selectClause = select from entity

    val filteredCriteria = criteriaList filter (e => e._2 != null && e._2.length > 0)

    if (filteredCriteria.length > 0) {

      def criteriaExpression(e: CriteriaListElement[T]) = { (lower(e._1) like s"${e._2.toLowerCase}%") }

      val whereClause = selectClause where criteriaExpression(filteredCriteria(0))

      filteredCriteria drop (1) foreach (criteria => { whereClause and criteriaExpression(criteria) })

    }

    selectClause

  }

[Kostas Kougios]

FYI latest snapshot has an immutable DSL + builder