How can I map a Row to a class using Anorm?

Question

I have a class User:

case class User (id: Int, name: String)

And I would like to map the rows from a query using Anorm Stream API. I have tried with this code:

val selectUsers = SQL("SELECT id, name FROM users")
val users = selectUsers().map(
    user => User(0, user.name)
).toList

But I get an error:

Error raised is : value name is not a member of play.db.anorm.SqlRow

on

user => User(0, user.↓name)

How can I map the SqlRow to a class?

---

As suggested by Ricardo, I tried:

object User extends Magic[User]

val users: List[User] = SQL("SELECT * FROM users").as(User*)

But with this code I get an RuntimeException occured : ColumnNotFound(User.id) on:

val users: List[User] = SQL("SELECT * FROM users").as(User*)

Any suggestions? Am I supposted to have the User object in the line right before? and I still have my case class User.

Com

Answer 1

I got it working with this:

val selectUsers = SQL("SELECT id, name FROM users")
val users = selectUsers().map(
    user => new User(user[Int]("id"), user[String]("name"))
).toList

Every row user is a dictionary. I don't know the Scala syntax very well.

Com

Answer 2

You can use Magic helper, create a object that extends magic :

object User extends Magic[User]

Then :

val users:List[User] = SQL("select * from User").as(User*)

See the doc for more information : Magic helper

Factor

The reason why this don't work for me is that I'm using PostgreSQL as database, and it's not possible to use Magic with PostgreSQL. – Com

Answer 3

To make it a bit more scalable you could do this.

Create a val and map the incoming data to a user.

import {classname}

val parsedValueOfUser = {
 get[Int]("id") ~
 get[String]("name") map {
   case id ~ name => User(id, name)
 }
}

Now when you want to get a user from the database and map it to your User class you can do:

val selectUsers = SQL("SELECT id, name FROM users").as(parsedValueOfUser *)

Diffusion

Answer 4

I ran into this exact issue and took me awhile to finger out what was wrong. It turned out to be the model and database names have to be the same, case-sensitive.

So for your example your database table would need to be called "User"

You can change a database table name using: RENAME TABLE users TO User ;

Hope that helps.

Caustic