3

Ok, I've got a method with multiple optional arguments like this 

def(username: Option[String], petname: Option[String], favouritefood: Option[String])

and i want to write a dynamic query that will be capable of fetching the data of defined arguments in a way of this

select * from table where un like username or pn like pn or ff like ff;

so depending of which arguments are defined to add them to query with OR operator?

bgvoka
  • 65
  • 1
  • 4
  • Probably, you can try this blog. http://davidruescas.com/2013/12/27/querying-in-slick-with-many-optional-constraints/ – Yadu Krishnan Jan 27 '15 at 13:50
  • I think you need to do something like this http://stackoverflow.com/questions/26815913/how-to-do-or-filter-in-slick – vitalii Jan 27 '15 at 14:10
  • checked them already but this doesn't work with Optional values, as I cannot check if they are defined in the for-comprehension when making query – bgvoka Jan 27 '15 at 14:42
  • do you want to generate an sql string? or make slick api calls? – tfh Jan 27 '15 at 15:43
  • id want to do it as an api call – bgvoka Jan 27 '15 at 16:07
  • You could combine the ideas https://gist.github.com/cvogt/9193220 and http://stackoverflow.com/questions/26815913/how-to-do-or-filter-in-slick so instead of chaining filters you build a list of conditions and reduce(_ || _) – cvogt Jan 27 '15 at 17:19
  • how do you mean list of conditions? It would be really helpful if you could write an example as for this one on github, having 15 tables and a need to search only through and for those defined values matching that tables and columns but with OR operator I think is really common and a lot of them have problems with it, I spoke with few people that needed to change from Slick and search for something else to solve this, are you thinking about solving it in a near time? thanks a lot on answering :) – bgvoka Jan 28 '15 at 05:02

2 Answers2

8

Something like this should work. I had to use a similiar fragment in my own code and it is also close to what cvogt proposes in above comment (I think). 

val username = Option("")
val petname = Option("")
val ff:Option[String] = None

val default = LiteralColumn(1) === LiteralColumn(1) 

yourTable.filter { it => 
  List(
      username.map(it.username === _),
      petname.map(it.petname === _),
      ff.map(it.ff === _)
  ).collect({case Some(it)  => it}).reduceLeftOption(_ || _).getOrElse(default)
}
tfh
  • 620
  • 1
  • 4
  • 14
  • yeah it works great :) now I also need something like 4 optional values, and to build up a query that will contain in where condition only those defined, that wont check columns for underfined values, like `code` def(a1: Option[String], a2: Option[String], a3: Option[String]) `code` a1 - Some("safe") `code` a2 - Some("unsafe") `code` a3 - None `code` select * from table where column like a1 or column like a2 to ignore those that are undefined in where clause – bgvoka Feb 02 '15 at 17:55
  • I modified my answer. If you `map` on `None` this value stays as `None`. In the end you can simply filter for defined values. – tfh Feb 03 '15 at 08:47
  • thanks, works great, I only need to map it to _.get after last filter to reduce the option wrap :) – bgvoka Feb 03 '15 at 12:52
  • question answered? ;) – tfh Feb 03 '15 at 13:07
  • I updated my answer, see the answer of Sebastien Lorber and first comment on why. I think you should update you code also. – tfh Feb 05 '15 at 13:41
  • Im having more trouble with this, if my it.username is Optional[String], the query isnt compiling, how should it go with the Column[Option[String]] on that side? – bgvoka Feb 09 '15 at 13:32
  • Perfect example! What i don't like is that i had to write 1=1 in my code- limitation in slick. – charino Aug 30 '19 at 20:48
1

The thoefer is nice for simple use cases but has some limits. Like if all your options are None's the list is empty and you can't reduce an empty list :)

If you need something more composable, based on predicate, conjunctions and disjunctions (a bit like Hibernate/JPA Criteria API), you can check my answer in Slick: create query conjunctions/disjunctions dynamically

Community
  • 1
  • 1
Sebastien Lorber
  • 89,644
  • 67
  • 288
  • 419
  • I updated my answer to handle an empty list as well. One can use `reduceLeftOption` in combination with `getOrElse` in this specific case. – tfh Feb 05 '15 at 13:40