How to add derived variables to a ResultSet

Question

Almost all guides/tutorials that I've seen only show how to parse values from columns that are directly available in the database. For example, the following is a very common pattern, and I understand how it can be useful in many ways:

case class Campaign(id: Int, campaign_mode_id: Int, name: String)

class Application @Inject()(db: Database) extends Controller {

  val campaign = {
    get[Int]("campaign.id") ~
    get[Int]("campaign.campaign_mode_id") ~
    get[String]("campaign.name") map {
      case id ~ campaign_mode_id ~ name  => Campaign(id, campaign_mode_id, name)
    }
  }

  def index = Action {
    val data : List[Campaign] = db.withConnection { implicit connection =>
      SQL("SELECT id, campaign_mode_id, name FROM campaign").as(campaign.*)
    }

    Ok(Json.toJson(data))
  }
} 

And it'd produce a result that might look like the following:

[
    {
        id: 2324,
        campaign_mode_id: 13,
        name: "ABC"
    },
    {
        id: 1324,
        campaign_mode_id: 23,
        name: "ABCD"
    }   
]

Now what if there were an additional date field in the campaign table like started_on that referred to when the campaign was started? Or another field called num_followers that was an integer referring to the number of followers?

And suppose that I wanted to do some calculations after running the DB query and before returning my JSON. For example, I want to include a latest_compaign_date that references the started_on date of the newest campaign. Or say that I wanted to include an average_num_followers that referred to the average number of followers for all campaigns.

My final result would look like:

{
    latest_compaign_date: 12 Dec 2018,
    average_num_followers: 123,
    campaigns: [
        {
            id: 2324,
            campaign_mode_id: 13,
            name: "ABC"
        },
        {
            id: 1324,
            campaign_mode_id: 23,
            name: "ABCD"
        }   
    ]
}

I know that for the examples I've given it's better to do those calculations in my DB query and not in my application code. But what if I had to do something really complicated and wanted to do it in my application code for some reason? How should I modify my ResutSetParser to facilitate this?

Here are a couple of approaches that I've tried:

Do not use ResultSetParser and instead do everything manually

  case class CampaignData(newestCampaignDate: Long, newestCampaignId: Long, averageNumFollowers: Float, campaigns: Seq[Campaign])

  def aggregater(rows: Seq[Row]): CampaignData = {
    val newestCampaignDate: Long = getNewestDate(rows)
    val newestCampaignId: Long = getNewestCampaign(rows)
    val averageNumFollowers: Float = getAverageNumFollowers(rows)

    val campaigns: Seq[Campaign] = rows.map(row => {
      val rowMap: Map[String, Any] = row.asMap

      Campaign(
        rowMap("campaign.id").asInstanceOf[Int],
        rowMap("campaign.campaign_mode_id") match { case None => 0 case Some(value) => value.asInstanceOf[Int]},
        rowMap("campaign.name") match { case None => "" case Some(value) => value.asInstanceOf[String]}
      )
    })

    CampaignData(newestCampaignDate, newestCampaignId, averageNumFollowers, campaigns)
  }

  def index = Action {
    val data : Seq[Row] = db.withConnection { implicit connection =>
      SQL("SELECT id, campaign_mode_id, name, started_on, num_followers FROM campaign")
    }

    Ok(Json.toJson(aggregater(data)))
  }

This approach smells bad because having to deal with every field using asInstanceOf and match is very tedious and honestly feels unsafe. And also intuitively, I feel that Anorm should have something better for this since I'm probably not the first person who has run into this problem.

Use ResultSetParser in combination with another function

case class Campaign(id: Int, campaign_mode_id: Int, name: String)
case class CampaignData(newestCampaignDate: Long, newestCampaignId: Long, averageNumFollowers: Float, campaigns: Seq[Campaign])

val campaign = {
  get[Int]("campaign.id") ~
  get[Int]("campaign.campaign_mode_id") ~
  get[Int]("campaign.num_followers") ~
  get[Long]("campaign.started_on") ~
  get[String]("campaign.name") map {
    case id ~ campaign_mode_id ~ num_followers ~ started_on ~ name  => Map(
      "id" -> id, 
      "campaign_mode_id" -> campaign_mode_id, 
      "num_followers" -> num_followers,
      "started_on" -> started_on,
      "name" -> name
    )
  }
}

def index = Action {
  val data : Map[String, Any] = db.withConnection { implicit connection =>
    SQL("SELECT id, campaign_mode_id, name, started_on, num_followers FROM campaign").as(campaign.*)
  }

  Ok(Json.toJson(aggregator(data)))
}

def aggregator(data: Map[String, Any]): CampaignData = {
  val newestCampaignDate: Long = getNewestDate(data)
  val newestCampaignId: Long = getNewestCampaign(data)
  val averageNumFollowers: Float = getAverageNumFollowers(data)
  val campaigns: Seq[Campaign] = getCampaigns(data)

  CampaignData(newestCampaignDate, newestCampaignId, averageNumFollowers, campaigns)
}

This approach is better in the sense that I don't have to deal with isInstanceOf, but then there is a bigger problem of having to deal with the intermediate Map. And it makes all the getter functions (e.g. getCampaigns) so much more complicated. I feel that Anorm has to offer something better out of the box that I'm not aware of.

Answer 1

As you posted in your first code snippet, the following code

def index = Action {
    val data : List[Campaign] = db.withConnection { implicit connection =>
      SQL("SELECT id, campaign_mode_id, name FROM campaign").as(campaign.*)
    }

    Ok(Json.toJson(data))
  }

returns a typesafe List of Campaign thanks to Anorm extractors.

Typically, you will pre-process the result with a typesafe function like so

case class CampaignAggregateData(campaigns:List[Campaign], averageNumFollowers:Int, newestCampaignId:Option[Long])

def aggregate(f:List[Campaign]):CampaignAggregatedData

def index = Action {
    val manyCampaign : List[Campaign] = db.withConnection { implicit connection =>
      SQL("SELECT id, campaign_mode_id, name FROM campaign").as(campaign.*)
    }

    val aggregatedData:CampaignAggregateData = aggregate(manyCampaign)

    Ok(Json.toJson(data))
  }

In cases where you would need aggregation to be executed by the database engine, you would typically have multiple db.withConnection statements inside a single action