ColumnNotFound problem with Magic in play scala

I'm getting a "play.exceptions.JavaExecutionException: ColumnNotFound(comments.id)" in a piece of code after trying to migrate to MySql instead of the memorydb. Postgres support by Magic is almost null. The evolution:

create table comments ( 
    id bigint(20) NOT NULL AUTO_INCREMENT, 
    source varchar(255) NOT NULL, 
    target varchar(255) NOT NULL, 
    content text NOT NULL, 
    date bigint NOT NULL, 
    PRIMARY KEY (id) 
); 

The model:

case class comments(id: Pk[Long], source: String, target: String, 
content: String, date: Long) { 
  override def toString = "|%s| |%s|, |%s|, |%s|".format(id.toString, 
source, target, content) 
  lazy val formattedDate = new SimpleDateFormat("dd.MM.yyyy HH:mm") 
format date 
} 

object comments extends Magic[comments] 

And the piece of code:

def loadComments(username: String) = SQL("""select c.*, u.* from 
comments c, usr u where c.source = u.ccall and c.target = {ccall} 
order by c.date desc""").on("ccall" -> username).as(comments ~< usr *) 

Can anyone give me some pointers? I'm really stuck on this.. Here is the stacktrace:

play.exceptions.JavaExecutionException: ColumnNotFound(comments.id)
    at play.mvc.ActionInvoker.invoke(ActionInvoker.java:228)
    at Invocation.HTTP Request(Play!)
Caused by: java.lang.RuntimeException: ColumnNotFound(comments.id)
    at scala.Predef$.error(Predef.scala:58)
    at play.db.anorm.Sql$.as(Anorm.scala:984)
    at play.db.anorm.Sql$class.as(Anorm.scala:919)
    at play.db.anorm.SimpleSql.as(Anorm.scala:829)
    at controllers.Profile$.loadacomments(Profile.scala:21)
    at controllers.Profile$.loadacommentsWithLikes(Profile.scala:46)
    at controllers.Profile$.comment(Profile.scala:91)
    at play.mvc.ActionInvoker.invokeWithContinuation(ActionInvoker.java:543)
    at play.mvc.ActionInvoker.invoke(ActionInvoker.java:499)
    at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:493)
    at play.mvc.ActionInvoker.invokeControllerMethod(ActionInvoker.java:470)
    at play.mvc.ActionInvoker.invoke(ActionInvoker.java:158)
开发者_开发问答

Thank you!

On this specific case, the mysql driver was an old one that made the names look really strange. I just updated the driver and everything came back to place.

You can check the thread in google groups here: http://groups.google.com/group/play-framework/browse_thread/thread/3bd8d3ccb5a51d10/e7074ad34ac637da?lnk=gst&q=Jos%C3%A9+Leal#e7074ad34ac637da

I assume that comments magic works for trivial queries? Have you tried not aliasing the table?

If that fails, I have a fairly hackish solution to it. Which is how I'm using Anorm with Postgres. I had to edit the Anorm source code to look for just the <column name> and not <table name>.<column name>. But that brings along a the problem that Anorm can't identify which column is which in a JOIN. So I had to resort to naming all my columns uniquely.

Alternatively, you could try pulling the latest play-scala code from github, but I don't know if there's any significant progress regarding this issue.

Not sure, but I've seen it where when you query multiple tables with Table1., Table2., and they BOTH have a column by the same name... such as "ID", then they get returned as

ID_A, ID_B, etc... then the other columns. So, if your USR table has a column called "ID" also, then this might be what you are running into.

If so, you could either explicitly list all the columns from the USR table and NOT include that table's ID column...

OR

add a column to your query

select C.ID as MyCTableID, C., U. ...

Then, you KNOW you will explicitly have a column called "MyCTableID" to run with.