Scala Plain query not giving the desired Result set

Question

I tried to bring the data after retriving from the database but it isnt retriving the data as i need how to map the data so that i can get it in the required format

This is my repository

package repositories

class UserRepository @Inject()(val dbConfigProvider: DatabaseConfigProvider)(implicit ec:ExecutionContext)extends HasDatabaseConfigProvider[JdbcProfile]{

 import profile.api._

 private val carts=TableQuery[Carts]
 private val cart_items=TableQuery[Cart_items]
 private val products=TableQuery[Products]


 def customquery = {
  val q=db.run(sql"""select c.cart_id,p.product_id,p.name,p.price,mg.parent_model,mg.child_model       from carts c
              inner join cart_items ci on c.cart_id=ci.cart_id
              inner join products p on ci.product_id = p.product_id
              join model_group mg on mg.child_model=p.product_id""".as[cartData])
  q
 }
}

Here is my UserTable mapped with the original postgres database

package Dao

import Form.{User, cart_items, carts, products}
import slick.jdbc.PostgresProfile.api._
import slick.lifted.Tag



class Carts (tag: Tag) extends Table[carts](tag,"carts"){

  def id = column[Int]("cart_id", O.PrimaryKey)
  def user_id = column[Int]("user_id")

  override def * = (id,user_id) <> (carts.tupled,carts.unapply)
}

class Cart_items (tag: Tag) extends Table[cart_items](tag,"cart_items"){

  def cart_item_id = column[Int]("cart_item_id", O.PrimaryKey)
  def cart_id = column[Int]("cart_id")
  def product_id = column[Int]("product_id")

  override def * = (cart_item_id,cart_id,product_id) <> (cart_items.tupled,cart_items.unapply)
}

class Products (tag: Tag) extends Table[products](tag,"products"){

  def product_id = column[Int]("product_id", O.PrimaryKey)
  def  name= column[String]("name")
  def price =column[Int]("price")

  override def * = (product_id,name,price) <> (products.tupled,products.unapply)
}

This is my case classes defined for the custom data coming for query and mapping it

package Form

import slick.jdbc.GetResult

case class Users(id:Int,firstName:String,lastName:String,email:String,password:String)

case class carts(id:Int,userid:Int)

case class cart_items(id:Int,cartid:Int,productid:Int)

case class products(id:Int,name:String,price:Int)

case class inventories(id:Int,productid:Int,quantity:Int,storeid:Int)

case class store(id:Int,storename:String,quantityAvailable:Int,inventoryid:Int)

case class modelgroup(id:Int,parent_model:Int,child_model:Int,quantity:Int)

case class cartData(id:Int,products:Seq[products])

object cartData{

  implicit val getCartResult = GetResult(
    r => cartData(r.<<,Seq(products(r.<<, r.<<, r.<<))))
}

Here is my controller

package controllers

import Dao.Carts
import Form.{User, Users, cartData, products}

import javax.inject._
import play.api._
import play.api.mvc._
import repositories.UserRepository
import play.api.libs.json
import play.api.libs.json.Format.GenericFormat

@Singleton
class HomeController @Inject()(val controllerComponents: ControllerComponents,userRepository: UserRepository)(implicit ec:ExecutionContext)extends BaseController {


  implicit val usersWrites: Writes[Seq[Users]] = Writes.seq(Json.writes[Users])



  import play.api.libs.json.{Json, Reads, Writes}


  implicit val productFormat: Format[products] = Json.format[products]
  implicit val cartDataFormat: Format[cartData] = Json.format[cartData]


  def custom(): Action[AnyContent] = Action.async{ implicit request: Request[AnyContent] =>

    val x = userRepository.customquery

    userRepository.customquery.map(users=>Ok(Json.toJson(users)))
  }

}

my output is coming like this

[
  {
    "id": 1,
    "products": [
      {
        "id": 3,
        "name": "COIRFIT Ortho Nirvana 5-Zone Pneumatic HR Foam 14 inch Queen Pocket Spring Mattress  (L x W: 75 inch x 66 inch)",
        "price": 190999
      }
    ]
  },
  {
    "id": 1,
    "products": [
      {
        "id": 10,
        "name": "Brown chair",
        "price": 1020
      }
    ]
  }
]

But i need this type of output

[
  {
    "id": 1,
    "products": [
      {
        "id": 3,
        "name": "COIRFIT Ortho Nirvana 5-Zone Pneumatic HR Foam 14 inch Queen Pocket Spring Mattress  (L x W: 75 inch x 66 inch)",
        "price": 190999
      },
      {
        "id": 10,
        "name": "Brown chair",
        "price": 1020
      }
    ]
  }
]

How should i get this ouput

Answer 1

You are using slick which is a Functional Relational Mapping for Scala. In this case, you choose to use Plain SQL Queries where you can write plain SQL as a String value and then map the result to some object. You have the interpolator sql (the one you are using), sqlu (used for DML statements which produce a row count instead of a result set) and tsql (that adds type-checked sql statements).

One quick solution to what you have could be instead of doing

case class cartData(id: Int, products:Seq[products])

object cartData{
  implicit val getCartResult = GetResult(
    r => cartData(r.<<,Seq(products(r.<<, r.<<, r.<<)))
  )
}

you can do

case class cartData(id: Int, product: products)

object cartData{
  implicit val getCartResult = GetResult(
    r => cartData(r.<<, products(r.<<, r.<<, r.<<))
  )
}

Your plain SQL query will be the same.

val queryResult: Future[Vector[cartData]] = db.run(
  sql"""SELECT c.cart_id,
               p.product_id,
               p.name,
               p.price,
               mg.parent_model,
               mg.child_model
          FROM carts c
    INNER JOIN cart_items ci ON c.cart_id=ci.cart_id
    INNER JOIN products p ON ci.product_id = p.product_id
          JOIN model_group mg ON mg.child_model=p.product_id"""
    .as[cartData]
)

As you can see, the return type here is a Future[Vector[cartData]] which means that we have a collection of cartData. From there, you can call the method groupBy that will return a Map[K,V] where K will be the key that you use to group the elements of the collection and V will be the same type of the collection that you have at the beginning.

case class cartDataGrouped(id: Int, products: Seq[products])

val desiredResult = for {
  result <- queryResult
} yield {
  result
    .groupBy(cartDataRecord => cartDataRecord.id) // <- returns a Map[Int, cartData]
    .map(
      groupedValuesById => cartDataGrouped(groupedValuesById._1, groupedValuesById._2.map(cartData => cartData.product)
    ) // <- here we are changing the type `Map[Int,cartData]` to `List[cartDataGrouped]` which is the desired output in your endpoint
}

I don't think this is a good approach. Doing this, you are populating a list (meaning that you are loading it in memory) with all the rows that the query returned, then you are applying an operation to group the elements based on some condition and again you are applying another operation to transform the result to what you want as an output of your endpoint. In a small collection this could be fine and you will not see any problem, but my main concern is that this code is not exactly readable and scalable. If you need to change the query or the objects you could end having some problems in runtime, but with the proper integration tests you will be able to caught everything before put the new code in production.

---

Slick offers the possibility to create objects that you can map to your schema. This lets you to work directly with scala objects instead of having to write plain SQL. You are already doing that when you declare

class Carts (tag: Tag) extends Table[carts](tag,"carts") {
  // columns and projections for table `carts`
}

class Cart_items (tag: Tag) extends Table[cart_items](tag,"cart_items") {
  // columns and projections for table `cart_items`
}

class Products (tag: Tag) extends Table[products](tag,"products") {
  // columns and projections for table `products`
}

then you have these lines

private val carts=TableQuery[Carts]
private val cart_items=TableQuery[Cart_items]
private val products=TableQuery[Products]

From there, you can write type-safe queries for selecting, inserting, updating and deleting data with Slick’s Scala-based query API.

With those vals you declare using TableQuery[A], you can do joins and many other sql operations directly from scala.

There are two ways of joining tables in slick:

• Applicative joins
• Monadic joins

In the following example I will use the second type:

• having the following TableQuerys

private val carts       = TableQuery[Carts]
private val cart_items  = TableQuery[Cart_items]
private val products    = TableQuery[Products]
private val model_group = TableQuery[Model_Gruop] // you need to create this one

• The following lines should produce a query similar to the one you are doing in the provided code

val query = for {
  c  <- carts_table
  ci <- cart_items_table if ct.id        === cit.cart_id
  p  <- products_table if cit.product_id === pt.product_id
  mg <- model_group if mg.child_model    === p.product_id
} yield (c.id, p.product_id, p.name, p.price, mg.parent_model, mg.child_model)

• Now that we have the query, we need to transform the result to the desired output

val queryWithTheDesiredOutput = 
  query
    .result
    .map { resultSet =>
      resultSet
        .groupBy(x => x._1) // `groupBy` will return a Tuple[K, Seq] where `K` in this case is the id of type Int
        .map { groupedById => // groupedById is of type (Int, Seq[(...)])
          cartData(
            id = groupedById._1, // the first element of the Tuple[Int, Seq] is the `id` that we use in the groupBy
            products = groupedById._2.map(product => products(product._2, product._3, product._4)) // the second element of the tuple is the tuple that we produce in the `yield`
          )
        }
    }

• And now we can execute the query and get the expected result

val expectedOutput = db.run(queryWithTheDesiredOutput)

---

As a side note, I suggest to adopt a convention to name the classes and objects. It will let you know easier what you have in the code and also it will help you others to understand your code (same case when you have to read your code again in a couple of days because you need to change something). It's also common to use a suffix that has some relation with the object. For example, a class that maps to a Table using that as a suffix, a class that maps to the row of that table you can use Row. For example:

case class CartRow(...) // instead of `carts`
class CartTable (tag: Tag) extends Table[CartRow](tag,"carts") // instead of `Carts`
val cartTableQuery = TableQuery[CartTable] // instead of `carts`
case class CartDataDTO(...) // instead of `cartsData`

Those names are just a suggestion based on the code provided in your question. I don't know anything about the project you are working on. You should think which naming convention is better for your case.