Case classes and Scalaquery

[Edmondo Porcu]

Dear ScalaQuery users,
is there a simple way to turn a case class into a tuple and viceversa
to avoid boilerplate code when inserting or querying a db with
scalaquery?


Best Regards
Edmondo

[virtualeyes]

Sure, don't use tuples, or, if your reason for using tuples is to grab
only a subset of case class fields, then create a case class
"projection" based on those fields

That way you are always working with sensible objects and
never ._1, ._2 madness ;-)

I think you can have your cake (object) and eat it (ScalaQuery
goodness) too. Of course, that's just how I do it, I'm sure there are
more advanced developers that have a solution for what you're
describing.

[Dickon Field]

Do you have a code sample? I've tried to return objects from a query using the syntax in Stefan's Commerzbank presentation but could not get it to work - I'm just converting from tuples using .map at the moment

Dickon

[virtualeyes]

@Dickon, if you just want an object returned do something like:

case class Foo(id: Int, bar: String)
object Foos extends Table[Foo]("foo") {
def id = column[Int]("id", O PrimaryKey)
def bar column[String]("bar")
}

def getFoos(id: Int): List[Foo] {
val q = (for {f <- Foos; if f.id is id.bind } yield f)
asList(q)
}

def asList[T](q: Query[_,_]): List[T] = {
db withSession { implicit ss: Session=>
q.list map { case t:T => t }
}
}

This approach is of course not fully type safe (if you chose to do,
"yield (f.bar)" for example, then the query would fail at runtime as
you are missing the id field to perform the query result to case class
conversion). Anyway, highly convenient, IMO.

Projection case classes (a view in DBMS terms) are the same, but
instead of returning object tuple(s) from your query (that's what SQ
returns with "yield f", a tuple of fields representing the mapping),
you return the tuple that represents your projection case class.

Should note that if you intend to use Parameters binding (suggested,
caches query statements) then asList method above either needs
signature changed from "q: Query[_,_]" to "q: MutatingUnitInvoker[_]"
or have asList versions for both query types if for some reason you
don't want to cache query generation (which is what happens when you
only go with fooparam.bind). asOption versions would be the same but
with q.firstOption in place of q.list and of course, return Option[T]

Take all this with a grain of salt, am new-ish to ScalaQuery, far from
an expert, but am absolutely loving SQ, functional queries, LINQ-to-
SQL goodness in Scala, totally awesome ;-)

[Edmondo Porcu]

This is my case:

def findCompanyById(id: Int) = {
val query = for (company <- Companies if company.id === id) yield
company.id ~ company.id2 ~ company.name ~ company.country ~
company.city ~ company.latitude ~ company.longitude
val result = db withSession {
query.list
}
result.map {
case (companyId, companyId2, name, country, city, latitude,
longitude) => ExampleCompany(companyId, companyId, name, country,
city, latitude, longitude)

}
}

This is very horrible...

2012/5/2 virtualeyes <sit...@gmail.com>:

[virtualeyes]

yes, very horrible indeed, Edmondo, that's quite a lot of typing! ;-)

Did you try the example I gave?

If you map a companion object to a case class definition, then you can
work with objects and avoid tuple boilerplate...

On May 14, 10:35 pm, Edmondo Porcu <edmondo.po...@gmail.com> wrote:
> This is my case:
>
> def findCompanyById(id: Int) = {
>     val query = for (company <- Companies if company.id === id) yield
> company.id ~ company.id2 ~ company.name ~ company.country ~
> company.city ~ company.latitude ~ company.longitude
>     val result = db withSession {
>       query.list
>     }
>     result.map {
>       case (companyId, companyId2, name, country, city, latitude,
> longitude) => ExampleCompany(companyId, companyId, name, country,
> city, latitude, longitude)
>
>     }
>   }
>
> This is very horrible...
>

> 2012/5/2 virtualeyes <sit1...@gmail.com>:

[Edmondo Porcu]

Dear VirtualEyes,
I didn't really understand what you mean with "mapping a companion

object to a case class definition"

Thank you
Edmondo

2012/5/14 virtualeyes <sit...@gmail.com>:

[virtualeyes]

Edmondo, see my post earlier in this thread...

On May 14, 11:39 pm, Edmondo Porcu <edmondo.po...@gmail.com> wrote:
> Dear VirtualEyes,
> I didn't really understand what you mean with "mapping a companion
> object to a case class definition"
>
> Thank you
> Edmondo
>

> 2012/5/14 virtualeyes <sit1...@gmail.com>:

[Toby Thain]

On Wednesday, 2 May 2012 04:15:40 UTC-4, virtualeyes wrote:

@Dickon, if you just want an object returned do something like:

case class Foo(id: Int, bar: String)
object Foos extends Table[Foo]("foo") {
  def id = column[Int]("id", O PrimaryKey)
  def bar column[String]("bar")
}

This does not compile for me.


class FooObj(id:Int,
                 updated:Timestamp,
                 name:String,
                 lat:Option[Float],
                 lon:Option[Float],
                 address:Option[String],
                 city:String,
                 localCity:Option[String],
                 state:Option[String],
                 postalCode:Option[String],
                 countryId:String,
                 phone:Option[String],
                 email:Option[String],
                 url:Option[String],
                 accessible:Option[Boolean])

object Foo extends Table[FooObj]("foo")
{
    def id          = column[Int]("id", O AutoInc)
    def updated     = column[Timestamp]("updated")
    def name        = column[String]("name")
    def lat         = column[Option[Float]]("lat")
    def lon         = column[Option[Float]]("lon")
    def address     = column[Option[String]]("address")
    def city        = column[String]("city")
    def localCity   = column[Option[String]]("local_city")
    def state       = column[Option[String]]("state")
    def postalCode  = column[Option[String]]("postal_code")
    def countryId   = column[String]("country_id")
    def phone       = column[Option[String]]("phone")
    def email       = column[Option[String]]("email")
    def url         = column[Option[String]]("url")
    def accessible  = column[Option[Boolean]]("accessible")

    def * = id // <-- error here
}

[error] Foo.scala:48: type mismatch;
[error]  found   : org.scalaquery.ql.NamedColumn[Int]
[error]  required: org.scalaquery.ql.ColumnBase[main.scala.xx.db.FooObj]
[error]     def * = id
[error]             ^
[error] one error found

If I omit the * member, I get:

[error] Foo.scala:31: object creation impossible, since method * in class AbstractTable of type => org.scalaquery.ql.ColumnBase[main.scala.xx.db.FooObj] is not defined
[error] object Foo extends Table[FooObj]("foo")
[error]        ^
[error] one error found


 

def getFoos(id: Int): List[Foo] {
  val q = (for {f <- Foos; if f.id is id.bind } yield f)
  asList(q)
}

def asList[T](q: Query[_,_]): List[T] = {
  db withSession { implicit ss: Session=>
    q.list map { case t:T => t }
  }
}

...

[RasputinJones]

Take a look at MapperTest. It shows you how to map a case class to a projection. I believe that's what virtualeyes is referring to.

https://github.com/szeiger/scala-query/blob/master/src/test/scala/scala/slick/test/ql/MapperTest.scala

Stephan also discussed this a bit on his blog. 

http://szeiger.de/blog/2009/09/27/a-scala-query-update/

[virtualeyes]

Needs to be:
case class FooObj

not:
class FooObj

Also, since apparently we cannot name case class and companion object
the same, I tend to go with convention
case class Foo
object Foos

so model is singular and mapper is plural

[Toby Thain]

On Thursday, 28 June 2012 11:44:40 UTC-4, virtualeyes wrote:

Needs to be:
case class FooObj

not:
class FooObj

Yes, well spotted. Also I needed this new syntax from the MapperTest linked above:

    def * = id ~ updated ~ name ~ lat ~ lon ~ address ~ city ~
            localCity ~ state ~ postalCode ~ countryId ~ phone ~
            email ~ url ~ accessible <> (FooObj, FooObj.unapply _)
 
This is still mostly voodoo to me, although I see that <> (a method on Projection) produces a MappedProjection.