Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Slick Plain SQL Query with Dynamic Conditions

Tags:

scala

slick

I'm struggling on appending additional conditions to my query. In its simplest form, what I need is something like below:

def findPeople(name: String, maybeSurname: Option[String]) = {
   val sql1 = sql"select * from my_table where name = $name"
   val sql2 = maybeSurname.map( surname => sql"and col2 = $surname" ).getOrElse(sql"")
   val finalSql = sql1 + sql2 // I need this kind of feature
   ...
   ...
}

Using #$ could be an option, but then surname wouldn't be a bind variable, which is a big issue.

like image 878
Feyyaz Avatar asked Sep 30 '15 08:09

Feyyaz


People also ask

How do I write a dynamic SQL query?

First, declare two variables, @table for holding the name of the table from which you want to query and @sql for holding the dynamic SQL. Second, set the value of the @table variable to production. products . Fourth, call the sp_executesql stored procedure by passing the @sql parameter.

What are dynamic queries in SQL?

You can use dynamic SQL to create applications that execute dynamic queries, which are queries whose full text is not known until runtime. Many types of applications need to use dynamic queries, including: Applications that allow users to input or choose query search or sorting criteria at runtime.

What is dynamic querying?

Dynamic queries refer to queries that are built dynamically by Drupal rather than provided as an explicit query string. All Insert, Update, Delete, and Merge queries must be dynamic. Select queries may be either static or dynamic. Therefore, "dynamic query" generally refers to a dynamic Select query.

What is dynamic query builder?

The Query Builder control enables you to define your query for scenarios where you need server-side filtering based on criteria defined at run time. You can use it with your database connectors by providing the query based on the dynamic selection.


1 Answers

here is an sample test on slick 3.1.x

import slick.jdbc.{SQLActionBuilder, SetParameter, PositionedParameters}

object SlickKit {

  implicit class SQLActionBuilderConcat (a: SQLActionBuilder) {
    def concat (b: SQLActionBuilder): SQLActionBuilder = {
      SQLActionBuilder(a.queryParts ++ b.queryParts, new SetParameter[Unit] {
        def apply(p: Unit, pp: PositionedParameters): Unit = {
          a.unitPConv.apply(p, pp)
          b.unitPConv.apply(p, pp)
        }
      })
    }
  }
}

and then

import SlickKit._
val sql1 =
  sql"""
   select count(*) from idinfo_#$i
    """

val sql2 =
  sql"""
       where source=$source
    """

val sql = sql1 concat sql2

sql.as[Int].head
like image 100
trydofor Avatar answered Sep 29 '22 20:09

trydofor