CODEWITHSUNDEEP
mysqlscalaslick
Philipp
Philipp

Reputation: 21

Slick: Select columns not working

I want to simply write a query, that only fetches one column (select titel from...) for each resulting row instead of all (select * ... from). My SQL-Query would be:

select titel from Lied;

According to http://slick.typesafe.com/doc/3.1.1/sql-to-slick.html?highlight=case#id3 I should by able to select only a couple of columns by simply doing something like this in slick 3.1:

people.map(p => (p.age, p.name ++ " (" ++ p.id.asColumnOf[String] ++ ")")).result

For me that would mean:

lieds.map(x => (x.titel)).result.map { println }

This doesn't work. Eclipse says:

No matching Shape found. Slick does not know how to map the given types. Possible causes: T in Table[T] does not match your * projection. Or you use an unsupported type in a Query (e.g. scala List). Required level: slick.lifted.FlatShapeLevel Source type: slick.lifted.Rep[String] Unpacked type: T Packed type: G

How should I solve this? I guess I cannot yet combine some basics here even though reading scala slick method I can not understand so far (not sure if this is still valid for slick 3.1) and the slick 3.1 documentation.

My Table (generated from slick):

  case class LiedRow(id: Long, titel: String, rubrikId: Option[Long] = None, stichwoerter: Option[String] = None, bemerkungen: Option[String] = None, createdAt: Option[java.sql.Timestamp] = None, updatedAt: Option[java.sql.Timestamp] = None, externallink: Option[String] = None, lastedituserId: Long, tonality: Option[String] = None)

  implicit def GetResultLiedRow(implicit e0: GR[Long], e1: GR[String], e2: GR[Option[Long]], e3: GR[Option[String]], e4: GR[Option[java.sql.Timestamp]]): GR[LiedRow] = GR{
    prs => import prs._
    LiedRow.tupled((<<[Long], <<[String], <<?[Long], <<?[String], <<?[String], <<?[java.sql.Timestamp], <<?[java.sql.Timestamp], <<?[String], <<[Long], <<?[String]))
  }

  class Lied(_tableTag: Tag) extends Table[LiedRow](_tableTag, "lied") {
    def * = (id, titel, rubrikId, stichwoerter, bemerkungen, createdAt, updatedAt, externallink, lastedituserId, tonality) <> (LiedRow.tupled, LiedRow.unapply)
    def ? = (Rep.Some(id), Rep.Some(titel), rubrikId, stichwoerter, bemerkungen, createdAt, updatedAt, externallink, Rep.Some(lastedituserId), tonality).shaped.<>({r=>import r._; _1.map(_=> LiedRow.tupled((_1.get, _2.get, _3, _4, _5, _6, _7, _8, _9.get, _10)))}, (_:Any) =>  throw new Exception("Inserting into ? projection not supported."))


    val id: Rep[Long] = column[Long]("id", O.AutoInc, O.PrimaryKey)
    val titel: Rep[String] = column[String]("Titel")
    val rubrikId: Rep[Option[Long]] = column[Option[Long]]("rubrik_id", O.Default(None))
    val stichwoerter: Rep[Option[String]] = column[Option[String]]("Stichwoerter", O.Default(None))
    val bemerkungen: Rep[Option[String]] = column[Option[String]]("Bemerkungen", O.Default(None))
    val createdAt: Rep[Option[java.sql.Timestamp]] = column[Option[java.sql.Timestamp]]("created_at", O.Default(None))
    val updatedAt: Rep[Option[java.sql.Timestamp]] = column[Option[java.sql.Timestamp]]("updated_at", O.Default(None))
    val externallink: Rep[Option[String]] = column[Option[String]]("externalLink", O.Default(None))
    val lastedituserId: Rep[Long] = column[Long]("lastEditUser_id")
    val tonality: Rep[Option[String]] = column[Option[String]]("tonality", O.Length(30,varying=true), O.Default(None))

    lazy val rubrikFk = foreignKey("liedRubrik", rubrikId, Rubrik)(r => Rep.Some(r.id), onUpdate=ForeignKeyAction.Cascade, onDelete=ForeignKeyAction.NoAction)
    lazy val userFk = foreignKey("liedLastEditUser", lastedituserId, User)(r => r.id, onUpdate=ForeignKeyAction.Cascade, onDelete=ForeignKeyAction.NoAction)
  }
  lazy val Lied = new TableQuery(tag => new Lied(tag))

Upvotes: 1

Views: 1798

Answers (2)

Philipp
Philipp

Reputation: 21

Problem solved.

I had to use

import slick.driver.MySQLDriver.api._

instead of 

import slick.driver.MySQLDriver.api.DBIO
import slick.driver.MySQLDriver.api.Database
import slick.driver.MySQLDriver.api.columnExtensionMethods
import slick.driver.MySQLDriver.api.longColumnType
import slick.driver.MySQLDriver.api.streamableQueryActionExtensionMethods
import slick.driver.MySQLDriver.api.valueToConstColumn

While I was playing around with different queries eclipse (or me?) ended up adding the above import statements to my class where I performed the select.

As you can see in my question I always got the same error (see Eclipse error mark) as soon as I wanted to use "map" even while other queries were working.

However. Replacing those import statements by simply import slick.driver.MySQLDriver.api._ solved my problem. Don't know why yet but it works now as all of us expected. If you know what's going wrong with the imports added by me / eclipse, please comment.

Upvotes: 0

pedrorijo91
pedrorijo91

Reputation: 7845

If you use this example: https://github.com/pedrorijo91/play-slick3-steps/blob/master/app/models/User.scala

Instead of having:

 def get(id: Long): Future[Option[User]] = {
    dbConfig.db.run(users.filter(_.id === id).result.headOption)
  }

you can select only email field using 

  def getField(id: Long): Future[Option[String]] = {
    dbConfig.db.run(users.filter(_.id === id).map(_.email).result.headOption)
  }

Upvotes: 0

Related Questions