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:
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.
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.
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