Scala Slick subqueries and returns

Question

I'm new to Scala with Slick library, I've work just with the Spark framework which is really strightforward with SQL sentences, and I find Slick really hard to understand. My problem is the following, I have 4 methods that are going to use almost the same SQL sentence:

Method One (getListOne(QueryToJoin: ??): List[String]) is going to use:

SELECT DISTINCT(tr.trx_cashier) FROM trxRepo tr JOIN (
    SELECT ts.* FROM (
        SELECT cp.* FROM trxstate 
        WHERE cp.sequence = (SELECT MAX(sc.sequence) FROM trxstate sc WHERE sc.trx_id = cp.trx_id)
    ) ts WHERE ts.status = 'open' 
) trs ON (tr.trx_id = trs.trx_id) WHERE trx_type IN ('sell', 'refund')

Method two (getListTwo(QueryToJoin: ??): List[Int]) is going to use:

SELECT DISTINCT(tr.trxpaymethodid) FROM trxRepo tr JOIN (
    SELECT ts.* FROM (
        SELECT cp.* FROM trxstate 
        WHERE cp.sequence = (SELECT MAX(sc.sequence) FROM trxstate sc WHERE sc.trx_id = cp.trx_id)
    ) ts WHERE ts.status = 'open' 
) trs ON (tr.trx_id = trs.trx_id) WHERE trx_type IN ('sell', 'refund')

Method three (getAllTrxs(QueryToJoin: ?): Future[Seq(Trx)]?) is going to use:

SELECT tr.* FROM trxRepo tr JOIN (
    SELECT ts.* FROM (
        SELECT cp.* FROM trxstate 
        WHERE cp.sequence = (SELECT MAX(sc.sequence) FROM trxstate sc WHERE sc.trx_id = cp.trx_id)
    ) ts WHERE ts.status = 'open' 
) trs ON (tr.trx_id = trs.trx_id) WHERE trx_type IN ('sell', 'refund')

Method four (getAllTrxs(QueryToJoin: ?, DTOLists): Future[Seq(Trx)]?) is going to use:

SELECT tr.* FROM trxRepo tr JOIN (
    SELECT ts.* FROM (
        SELECT cp.* FROM trxstate 
        WHERE cp.sequence = (SELECT MAX(sc.sequence) FROM trxstate sc WHERE sc.trx_id = cp.trx_id)
    ) ts WHERE ts.status = 'open' 
) trs ON (tr.trx_id = trs.trx_id) WHERE trx_type IN ('sell', 'refund')
AND tr.trx_cashier IN (...DTOLists.ListOne...) AND tr.trxpaymethodid IN (...DTOLists.Listtwo...)

As you can see it uses

SELECT ts.* FROM (
    SELECT cp.* FROM trxstate 
    WHERE cp.sequence = (SELECT MAX(sc.sequence) FROM trxstate sc WHERE sc.trx_id = cp.trx_id)
) ts WHERE ts.status = 'open' 

For every method and I don't want to write four methods with the same sentence. And to be honest, I haven't figure out how to write the previous sentences on Scala with Slick.

The following is a diagram with the classes and relations I've got so far:

https://drive.google.com/file/d/11lAqxd7JNUBtv4LgnDYmjKiHzmHqEP8t/view?usp=sharing

My classes code according to the diagram are:

Class TrxService which is the only who is supposed to access the TrxRepository

class TrxService @Inject()(
    trxRepository: TrxRepository, trxStateService: TrxStateService
)(implicit executionContext: ExecutionContext) {

  def getListsOne()(implicit mapMarkerContext: MapMarkerContext):
  Future[DTOLists] = {
    val trxState = trxStateService.getAllTrxState()
    DTOLists(
      transactionRepository.getListOne(trxState),
      transactionRepository.getListTwo(trxState)
    )
  }
}

Class TrxStateService which is the only who is supposed to access the TrxStateRepository

class TrxStateService @Inject()(trxStateRepository: TrxStateRepository) {

  def getAllTrxState()(implicit mapMarkerContext: MapMarkerContext)
  : Future[Seq[TrxState]] = {
    trxStateRepository.getAllTrxState()
  }
}

Class TrxRepository which is the only who is supposed to access the data from the database for Trx table

@Singleton
class TrxRepository @Inject()(override val dbConfigProvider: DatabaseConfigProvider)(implicit override val ec: ExecutionContext)
extends BaseRepository[Transaction]
with QrApplicationErrorHandler
with Logging {

  private val trxs = TableQuery[TrxTable]

  def getAllTxByLists(dtoLists: DTOLists)
  (implicit mapMarkerContext: MapMarkerContext): Future[Seq[Transaction]] = {
    db.run(
      trxs.filter(trxs => trx.terminalNumber == dtoLists.listOne)
        .filter(trxs => trx.paymentMethodId == dtoLists.listTwo).result
    )
  }

  def getAllTrx(trxState: Seq[TrxState])(implicit mapMarkerContext: MapMarkerContext): Future[Seq[Transaction]] = {
    val query = sql"""SELECT tr.* FROM trxRepo tr JOIN (
    SELECT ts.* FROM (
        SELECT cp.* FROM trxstate 
        WHERE cp.sequence = (SELECT MAX(sc.sequence) FROM trxstate sc WHERE sc.trx_id = cp.trx_id)
    ) ts WHERE ts.status = 'open' 
    ) trs ON (tr.trx_id = trs.trx_id) WHERE trx_type IN ('sell', 'refund')""".as[Seq[Transaction]]
    db.run(query)
  }

  def getListOne(trxState: Seq[TrxState])(implicit mapMarkerContext: MapMarkerContext): Future[Seq[Int]] = {
    /*db.run(
      trxs.join(trxState).on(_.trxId = _.trxId)
      .filter(trx => trx.trxType === "sell" || trx.trxType === "refund").map(_.trxCashier)
      .result
      .map(_.toList)
    )
    */
    db.run(sql"""SELECT tr.trx_cashier FROM trxRepo tr JOIN (
    SELECT ts.* FROM (
        SELECT cp.* FROM trxstate 
        WHERE cp.sequence = (SELECT MAX(sc.sequence) FROM trxstate sc WHERE sc.trx_id = cp.trx_id)
    ) ts WHERE ts.status = 'open' 
    ) trs ON (tr.trx_id = trs.trx_id) WHERE trx_type IN ('sell', 'refund')""".as(Seq[Int])
    )
  }

  def getListTwo(trxState: Seq[TrxState])(implicit mapMarkerContext: MapMarkerContext): Future[Seq[Int]] = {
    /*db.run(
      trxs.join(trxState).on(_.trxId = _.trxId)
      .filter(trx => trx.trxType === "sell" || trx.trxType === "refund").map(_.trxCashier)
      .result
      .map(_.toList)
    )
    */
    db.run(sql"""SELECT tr.trxpaymethodid FROM trxRepo tr JOIN (
    SELECT ts.* FROM (
        SELECT cp.* FROM trxstate 
        WHERE cp.sequence = (SELECT MAX(sc.sequence) FROM trxstate sc WHERE sc.trx_id = cp.trx_id)
    ) ts WHERE ts.status = 'open' 
    ) trs ON (tr.trx_id = trs.trx_id) WHERE trx_type IN ('sell', 'refund')""".as(Seq[String])
    )
  }

  private class TrxTable(tag: Tag) extends Table[Trx](tag, "TrxRepo") {

    def trxId = column[Long]("trx_id", O.PrimaryKey)
    def trxType = column[String]("trx_type")
    def trxPatMethodId = column[Int]("trxpaymethodid")
    def trxCashier = column[String]("trx_cashier")

    def * =
      (
        trxId,
        trxType,
        trxPatMethodId,
        trxCashier
      ) <> (Trx.tupled, Trx.unapply)

  }
}

Class TrxStateRepository which is the only who is supposed to access the data from the database for trxState table

@Singleton
class TrxStateRepository @Inject()(override val dbConfigProvider: DatabaseConfigProvider
)(implicit val ec: ExecutionContext)extends BaseRepository[TransactionStatus]
with QrApplicationErrorHandler with Logging {

  private val trxState = TableQuery[TransactionStatusTable]

  def getLastStatusForTrx(trxId: Long)(implicit mapMarkerContext: MapMarkerContext)
  : Future[TransactionStatus] = {
    trxState.filter(_.trxId === trxId).sortBy(_.sequence.desc).result.headOption
  }

  def getAllTrxState()(implicit mapMarkerContext: MapMarkerContext)
  : Future[Seq[TrxState]] = {
    logger.info(EnumLogger.LastStatusTxsForClosure.toString)
    val sequence = (trxState.map(_.sequence).max.asColumnOf[Int]).asColumnOf[Int]
    /*
    trxState.sortBy(_.sequence)
      .filter(trxStates => trxState.status === "open")
     */
    for {
      t1 <- trxState
      if !(trxState.filter(t2 => t1.trxId === t2.trxId).map(_.sequence).max)
    } yield t1
    /*
    db.run(sql"""SELECT ts.* FROM (
    SELECT cp.* FROM trxstate 
    WHERE cp.sequence = (SELECT MAX(sc.sequence) FROM trxstate sc WHERE sc.trx_id = cp.trx_id)
    ) ts WHERE ts.status = 'open'""".as(Seq[String]))
    */
  }

  private class TrxStateTable(tag: Tag) extends Table[TrxState](tag, "transaction_status") {

    def trxStateId: Rep[Long]      = column[Long]("trx_state_id", O.PrimaryKey, O.AutoInc)
    def trxId: Rep[Long]            = column[Long]("trx_id")
    def sequence: Rep[Int]                  = column[Int]("sequence")
    def status: Rep[String]                 = column[String]("status")

    def * =
      (
        trsxStateId.?,
        trxId,
        sequence,
        status
      ) <> (TrxState.tupled, TrxState.unapply)
  }

}

DTOLists that is the DTO to return

case class DTOLists(
  listOne: List[String],
  listTwo: List[Int]
)

I couldn't create the query using Slick so I tried to "hardcode" the sentence for every method using Plain SQL, but it generated an error always like Mismatch with the return value.

How can I create these queries with Slick and the return types to make a join with the result of others methods? I tried with Query[], Seq, TableQuery, Future[Seq[]] but I couldn't make it.

I really want the code to be extensible.