Anorm (Scala) How to return Json from a joint table?

Question

I'm trying to link 2 tables together and return json from it, unfortunatly the documentations and online are not very clear about this, so if you know how to solve this please provide a snippet that I learn from.

I have difficulties rendering def All: List[(Country, City)]{....} parameters in the json call bellow:

this is how I'd like to return the json:

Ok(Json.obj("success" -> true, "CountryAndCities" -> CountryAndCities.All)

All being:

package models

import play.api.db._
import play.api.Play.current

import anorm._
import anorm.SqlParser._

case class Country(id: Option[Int] = None, name: String)
case class City(id: Option[Int] = None, countryId: Int, name: String, population: Int)

object Country {

  val simple: RowParser[Country] = {
    get[Option[Int]]("country.id") ~
    str("country.name") map {
      case id~name => Country(id, name)
    }
  }
}

object City {

  def All: List[(Country, City)] = {

    DB.withConnection { implicit connection =>

      SQL(
        """
          SELECT *
          FROM city
            INNER JOIN country ON city.country_id = country.id
        """
      ).as(City.withCountry *)
    }
  }
}

Answer 1

So, your code is missing list parsing part, I will include it for completeness:

object City {
  def All: List[(Country, City)] = {

    DB.withConnection { implicit connection =>
      SQL(
        """
        SELECT *
        FROM city
        INNER JOIN country ON city.country_id = country.id
      """
      ).as(City.withCountry.*)
    }
  }

  def simple: RowParser[City] =
    int("city.id").? ~ int("city.countryId") ~
      str("city.name") ~ int("city.population") map {
      case id ~ countryId ~ name ~ population => City(id, countryId, name, population)
    }

  def withCountry: RowParser[(Country, City)] = simple ~ Country.simple map {
    case city ~ country => country -> city
  }
}

now, if you want to render it as JSON, the simple way would be to type:

implicit val userFormat = Json.format[Country]
implicit val cityFormat = Json.format[City]

Ok(Json.arr(City.All))