CODEWITHSUNDEEP
scalasqueryl
Mike G.
Mike G.

Reputation: 690

Squeryl: how to compare Option[T] objects in where clause?

I got stuck with the following problem.

I have a class, say Post, that looks like:

case class Post (

  id: Int,
  slug: String,
  title: String,

  @Column("postText")
  text: String,
  isVisible: Boolean,
  created: Timestamp,
  lastUpdated: Timestamp,
  published: Option[Timestamp]

) extends KeyedEntity[Int]

My problem is to get previous and next posts from the database, when the posts in ordered by published field. The problem I stuck with is that published field is Option[Timestamp]. I created a Squeryl query like this:

val nextPost = from(postTable)( p =>
      where((p.published > post.published) and p.isVisible === true)
      select(p)
      orderBy(p.published asc)
    ).page(0, 1)

And when I looked at the resulted sql I saw something like this: "... WHERE post.published > Some("....") ..." and of course this resulted in a syntax error in SQL query.

I looked throgh the documentation, but cannot find the answer. I am already thinking of switching to Slick...

UPDATE

There is a definite bug in squeryl mysql query construction. I ended up with 

val x : Timestamp =  post.published.getOrElse(new Timestamp(0))
val nextPost = from(postTable)( p =>
  where((p.published.getOrElse(new Timestamp(0)) > x) and p.isVisible === true)
    select(p)
    orderBy(p.published asc)
).page(0, 1)

which produces query:

Select
  Post9.lastUpdated as Post9_lastUpdated,
  Post9.published as Post9_published,
  Post9.postText as Post9_postText,
  Post9.slug as Post9_slug,
  Post9.id as Post9_id,
  Post9.isVisible as Post9_isVisible,
  Post9.title as Post9_title,
  Post9.created as Post9_created
From
  Post Post9
Where
  ((Post9.published > 2013-08-01 14:21:25.0) and (Post9.isVisible = true))
Order By
  Post9.published Asc
limit 1 offset 0

see, how the query constructor formatted the date...

I am switching to SLICK.

Upvotes: 3

Views: 1404

Answers (2)

VasiliNovikov
VasiliNovikov

Reputation: 10276

I think this is because you compared Timestamps, not DB objects. It's vital to understand the difference to use squeryl.

So, you should use instead:

p.published gte post.published
p.published.~ > post.published
p.published === post.published
p.published gt post.published

Refs:

http://squeryl.org/schema-definition.html

http://squeryl.org/inserts-updates-delete.html

and actually all examples where "less"/"greater" are needed.

Upvotes: 1

stefan.schwetschke
stefan.schwetschke

Reputation: 8932

I didn't verify this, but have you tried 

p.published.get > post.published.get

This should help you getting rid of the Some(...) part.

Attention: Your query might behave diffetently, because in SQL "NULL == NULL" is not true (it is NULL...). You could also try getOrElse(...).

Upvotes: 0

Related Questions