{"id":1517,"date":"2015-05-04T10:12:12","date_gmt":"2015-05-04T01:12:12","guid":{"rendered":"http:\/\/kazu.tv\/blog\/?p=1517"},"modified":"2015-05-04T10:12:12","modified_gmt":"2015-05-04T01:12:12","slug":"comparing-nullable-columns-in-squeryl","status":"publish","type":"post","link":"https:\/\/kazu.tv\/blog\/2015\/05\/04\/comparing-nullable-columns-in-squeryl\/","title":{"rendered":"Comparing nullable columns in Squeryl"},"content":{"rendered":"<h2>How to compare nullable columns in Squeryl<\/h2>\n<p>Suppose we have a table definition like this:<\/p>\n<pre class=\"brush: scala;\">case class T1 (\r\n  id: Int,\r\n  col1: Option[Int]\r\n)\r\nobject FooDb extends Schema {\r\n\u00a0 val t1 = table[T1](\"t1\")\r\n}<\/pre>\n<p>And, we want to execute a query like the following:<\/p>\n<pre class=\"brush: sql;\">select * from t1 where col1 &lt; 10<\/pre>\n<p>The correct statement in Squeryl is shown below:<\/p>\n<pre class=\"brush: scala;\">from(FooDb.t1)( t1 =&gt;\r\n  where(t1.col1 lt Some(10))\r\n  select(t1)\r\n)<\/pre>\n<p>This isn&#8217;t very intuitive, and actually, it took me a lot of time to get to this answer.<\/p>\n<h2>Here are some that I tried and failed<\/h2>\n<p>The most intuitive one doesn&#8217;t compile:<\/p>\n<pre class=\"brush: scala;\">  where(t1.col1 lt 10) \/\/ -&gt; doesn't compile<\/pre>\n<p>This one causes NoSuchElementException:<\/p>\n<pre class=\"brush: scala;\">  where(t1.col1.get lt 10) \/\/ -&gt; NoSuchElementException<\/pre>\n<p>Another one that causes NoSuchElementException:<\/p>\n<pre class=\"brush: scala;\">  where(t1.col1.map(_ lt 10).get) \/\/ -&gt; NoSuchElementException<\/pre>\n<h2>Official site should have example<\/h2>\n<p>I&#8217;ve found some guys that were having the same issue:<\/p>\n<ul>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/18053393\/squeryl-how-to-compare-optiont-objects-in-where-clause\" target=\"_blank\">Squeryl: how to compare Option[T] objects in where clause?<\/a><\/li>\n<li><a href=\"http:\/\/Important Squeryl Limitations\" target=\"_blank\">http:\/\/kevinlocke.name\/bits\/2012\/10\/03\/important-squeryl-limitations\/<\/a><\/li>\n<\/ul>\n<p>I think the Squeryl web site should have an example that uses nullable column in the where clause.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>How to compare nullable columns in Squeryl Suppose we have a table definition like this: case class T1 ( id: I&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[20],"tags":[947,905],"class_list":["post-1517","post","type-post","status-publish","format-standard","hentry","category-scala","tag-scala","tag-squeryl"],"_links":{"self":[{"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/posts\/1517","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/comments?post=1517"}],"version-history":[{"count":1,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/posts\/1517\/revisions"}],"predecessor-version":[{"id":1518,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/posts\/1517\/revisions\/1518"}],"wp:attachment":[{"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/media?parent=1517"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/categories?post=1517"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kazu.tv\/blog\/wp-json\/wp\/v2\/tags?post=1517"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}