CODEWITHSUNDEEP
scalaplayframework-2.1anorm
notbrain
notbrain

Reputation: 3396

Why is this Anorm Result an empty List? (Play 2.1.0)

Scala noob here; for the life of me cannot see why I am not getting a result for this Anorm SQL call. When I run the SQL debug output it returns a result just fine but when run through the code I end up with an empty List().

Is there an issue with my RowParser? Why am I seeing good SQL in the debug output but it's not being collected by my result val?

Am I missing something in my SQL .as() to properly map the result rows to the Parser? When I remove the last result line my result val evaluates to a Unit, which is definitely suspicious. 

// Case class - SQL results rows go into List of these
case class PerformanceData(
    date: String, 
    kwh: String
)

// RowParser
val perfData = {
    get[String]("reading_date") ~ get[String]("kwh") map{ 
        case reading_date~kwh => PerformanceData(reading_date, kwh) 
    }
}

// SQL Call - function ret type is Seq[PerformanceData]
DB.withConnection("performance") { implicit connection => 

    val result: Seq[PerformanceData] = SQL(
    """
        SELECT CONCAT(reading_date) AS reading_date,
           CONCAT(SUM(reading)) AS kwh
        FROM perf
        WHERE reading_date >= DATE_SUB(NOW(), INTERVAL 45 DAY)
        AND sfoid IN ({sf_account_ids})
        GROUP BY reading_date
        ORDER BY reading_date DESC
        LIMIT 30
    """
    ).on(
        'sf_account_ids -> getSQLInValues(SFAccountIDs)
    ).as(
        User.perfData *
    )

//  Logger.debug(result.toString) -> EMPTY LIST!??
    result // Why is this necessary to return proper type?

}

Upvotes: 0

Views: 715

Answers (2)

Kazuhiro Sera
Kazuhiro Sera

Reputation: 1877

Unfortunately, you need to use not bind variables but replacing in string value for IN clause.

see also: "In" clause in anorm?

Edit: I meant that sf_account_ids will be a single bind varibale. Maybe sfoid IN (?, ?, ?) is expected but the statement will be sfoid IN (?).

Upvotes: 2

korefn
korefn

Reputation: 955

For the first question id suggest you check your case statement in perDataand ensure that it's accurate. The function getSQLInValues(...) may also be the cause.

For the question as to why you need the last result and that is because scala uses the last statement in a closure to infer the return type when not explicitly defined. So val result = SQ(...) being an assignment would return Unit

To avoid this you can do something like:

DB.withConnection("performance") { implicit connection =>
  SQL(...).on(...).as(...)
}

By not assigning a return from SQL it is used to infer the type.

Upvotes: 0

Related Questions