How to map multi-column values?

[kutchar]

How can I map a single Scala value to multiple columns in the database? For example:

Consider mapping class Transaction as defined below:

case class Transaction(time: Date, amount: MonetaryValue)

Where MonetaryValue is defined as:

case class MonetaryValue(amount: BigDecimal, currencyCode: String)

to a table like so:

transactions:
  time  datetime
  amount decimal
  currency_code varchar

Looking at the code, ColumnType is sealed so not sure how something like this would be possible.

[DavidGregory084]

Do you mean something like this?

val transactionExtractor = extract[Transaction](
  time = TransactionsTable.time
  amount = monetaryValueExtractor
)

val monetaryValueExtractor = extract[MonetaryValue](
  amount = TransactionsTable.amount
  currencyCode = TransactionsTable.currencyCode
)

insert
  .into(TransactionsTable)
  .values(transactionExtractor.settersFor(
    Transaction(DateTime.now, MonetaryValue(BigDecimal(13.00), "GBP")
  ))

[kutchar]

Maybe, only if I can accomplish the following:

Say we have a REST API for transactions like so:

GET /transactions

Response:
{
  "values": [
    {
      "time" : "2015-12-06T12:15:44Z",
      "amount": "100 USD"
    },
    {
      "time" : "2015-12-04T15:35:13Z",
      "amount": "120 GBP"
    },
    ...
  ]
}

Now we want to be able to allow callers only asking for certain fields, say:

GET /transactions?fields=amount

Response:
{
  "values": [
    {
      "amount": "100 USD"
    },
    {
      "amount": "120 GBP"
    },
    ...
  ]
}

How would your approach allow me to select only currency columns?

Also, looking at the code I can't tell how I would be able to filter rows by amount, i.e. GET /transactions?amount=100 USD using ColumnSyntax

Thanks,

Drew

[brendanator]

sqlest determines at compile time what the result of the select statement running will be. Therefore if you want the fields within Transaction to be optional they would have to be Options, then you could do something like this:

case class Transaction(time: Option[Date], amount: Option[MonetaryValue])

def transactionExtractor(includeTime: Boolean, includeAmount: Boolean) = extract[Transaction](
  time = if (includeTime) TransactionsTable.time.asOption else extractConstant(Option.empty[Date])
  amount = if (includeAmount) monetaryValueExtractor else extractConstant(Option.empty[MonetaryValue])
)

Regarding filtering by amount: unfortunately there's nothing built in for filtering by composite values at the moment. I think it should be easy enough to implement this:

  monetaryValueExtractor ==== amount

  implicit class ExtractorComparisionOps[A](extractor: sqlest.extractor.Extractor[ResultSet, A]) {
    def ====(value: A): Column[Boolean] =
      extractor.settersFor(value).map {
        case Setter(column, value) => sqlest.ast.InfixFunctionColumn[Boolean]("=", column, value)
      }.reduce(_ && _)
  }

I will look into adding this