1

I have just started with squeryl and have no answer for how write a query like that

SELECT ref label 
FROM x_table 
WHERE ref like x% or lable like x%

where x is some value from the user, especially I haven't found analog of the sign % in squeryl or how can I use it.

My version:

val products = from(AppDB.productTable) (
    s => where ((s.label like value) or (s.ref like value))  select(s)
)

doesn't work correct.

val value : Option[String] I get from the user.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
arussinov
  • 1,237
  • 11
  • 16

2 Answers2

1

You can try to optionize your fields, like this:

val products = from(AppDB.productTable) (s => 
  where ((Some(s.label) like value) or (Some(s.ref) like value)) 
  select(s))

That will compile as the query will be comparing an Option[String] to an Option[String]. Squeryl will handle the option state internally.

If you are simply looking to add the wildcard to what you are comparing, assuming both sides of the like clause are of the type Option[String], then you can do this:

s.label like value.map(_ + "%")
jcern
  • 7,798
  • 4
  • 39
  • 47
  • No in this case we get an erorr **like is not a member of Some[Option[String]]**... – arussinov Dec 26 '12 at 20:01
  • `s.ref` is an `Option[String]` and `value` is an `Option[String]`? What was the error you were receiving originally? – jcern Dec 26 '12 at 20:02
  • Yes, I was looking how to add the wildcard, so that is ok, thanks, but it works only with _Option[String]_ and will not work with _String_ for example right? – arussinov Dec 26 '12 at 20:52
  • as for the code @jcern suggested in the top of the answer, it gives an error **"like is not a member of Some[Option[String]]"** – arussinov Dec 26 '12 at 21:02
  • If value was a string, you could simply make the right hand side `(value + "%")`. – jcern Dec 26 '12 at 21:12
  • No the value was `Option[String]` and it's OK with Map(),I asked so, because applying map to Option[String] and String gives different results. `(value + "%")` should work, but when I was trying so, I haven't got any result, no errors and no result, but now I know why... – arussinov Dec 26 '12 at 21:27
0

You can use shorter version:

AppDB.productTable.where ((s.label like value) or (s.ref like value))

and option fields are for nullable columns

scaramush
  • 29
  • 2
  • No, in this way it doesn't behave as necessary, if you need to get all rows where **label** or **ref** contains **value** you have to use a wild card – arussinov Dec 29 '12 at 00:42