How to perform a Join and a Group by without using raw SQL

[rcasula]

Hi All, I have this schema

@Table
struct Currency {
    let code: String
    let name: String
    let symbol: String
}

@Table
struct Expense {
    let id: UUID
    let name: String
    let amount: Decimal
    let currencyCode: Currency.ID
    let date: Date
}


// I want to achieve this end result:

@Selection
struct ExpenseModel {
    let id: UUID
    let name: String
    let amount: Decimal
    let currency: Currency
    let date: Date
}

@Selection
struct GroupedExpenses {
    let date: Date
    
    @Column(as: [ExpenseModel].JSONRepresentation.self)
    let expenses: [ExpenseModel]
}

and this is the query I'm trying to use:

Expense.
    .order { $0.date.desc(nulls: .last) }
    .join(Currency.all) { $0.currencyCode.eq($1.code) }
    .group(by: \.date)
    .select {
         // What to do here? I should have 2 arguments: expense and currency, 
         // but it seems that I have only one.
    }

If I do the same query without the group by clause, in the select I have 2 arguments correctly resolved, and I can create the ExpenseModel.
Do you have any suggestions?

Thanks in advance

[mbrandonw]

Hi @rcasula, this is quite a complex query to be performing and so it takes some work to get things in the right shape. The trick is to use a common table expression to first select all the data for the ExpenseModel, and then you aggregate that selection into a GroupedExpenses using jsonGroupArray.

First some fixes to your data types to be identifiable and codable:

@Table
struct Currency: Codable, Identifiable {
  let code: String
  let name: String
  let symbol: String
  var id: String { code }
}

@Table
struct Expense: Codable, Identifiable {
  let id: UUID
  let name: String
  let amount: Int
  let currencyCode: Currency.ID
  let date: Date
}


// I want to achieve this end result:

@Selection
struct ExpenseModel: Codable {
  let id: Expense.ID
  let name: String
  let amount: Int
  let currency: Currency
  let date: Date
}

@Selection
struct GroupedExpenses {
  let date: Date

  @Column(as: [ExpenseModel].JSONRepresentation.self)
  let expenses: [ExpenseModel]
}

I also dropped the Decimal in favor of Int because there really is no good way to use decimals in SQLite. At the end of the day SQLite has a very basic type system, and it cannot handle decimals in a lossless manner that also plays nicely with sorting. There was a recent discussion on this you may be interested in.

But with those data types defined you can now define your query using a With like so:

func fetch() {
  With {
    Expense
      .order { $0.date.desc(nulls: .last) }
      .join(Currency.all) { $0.currencyCode.eq($1.code) }
      .select {
        ExpenseModel.Columns(
          id: $0.id,
          name: $0.name,
          amount: $0.amount,
          currency: $1,
          date: $0.date
        )
      }
  } query: {
    ExpenseModel
      .group(by: \.date)
      .select {
        GroupedExpenses.Columns(
          date: $0.date,
          expenses: $0.jsonGroupArray()
        )
      }
  }
}

You can think of the query inside the With { … } trailing closure as defining a kind of "temporary" named selected of rows, and then the query in the query: { … } trailing closure can further select/aggregate on those rows.

[rcasula]

That's exactly what I needed, I was totally ignoring the common table expression part! Thank you so much!

And also, thanks for pointing out the Decimal discussion!

EDIT:

After testing a little bit, I got an error while decoding the json for the grouped expenses:

🟣 Sharing/Reference.swift:263: Caught error: keyNotFound(CodingKeys(stringValue: "currency", intValue: nil), Swift.DecodingError.Context(codingPath: [_CodingKey(stringValue: "Index 0", intValue: 0)], debugDescription: "No value associated with key CodingKeys(stringValue: \"currency\", intValue: nil) (\"currency\").", underlyingError: nil))

This is the generated SQL:

WITH "expenseModels" AS (
    SELECT "expenses"."id" AS "id", "expenses"."name" AS "name", "expenses"."amount" AS "amount", "currencies"."code" AS "code", "currencies"."name" AS "name", "currencies"."symbol" AS "symbol", "expenses"."date" AS "date"
    FROM "expenses"
    JOIN "currencies" ON ("expenses"."currencyCode") = ("currencies"."code")
    ORDER BY "expenses"."date" DESC NULLS LAST
)
SELECT "expenseModels"."date" AS "date", json_group_array(json_object('id', json_quote("expenseModels"."id"), 'name', json_quote("expenseModels"."name"), 'amount', json_quote("expenseModels"."amount"), 'code', json_quote("expenseModels"."code"), 'name', json_quote("expenseModels"."name"), 'symbol', json_quote("expenseModels"."symbol"), 'date', json_quote("expenseModels"."date"))) AS "expenses"
FROM "expenseModels"
GROUP BY "expenseModels"."date"

@mbrandonw

[mbrandonw]

I guess another option is to provide a custom Codable conformance that aligns with the query decodable. That is, you encode/decode the decimal as an integer with a 1000 factor. But also at that point you could also just create your own wrapper type so that it behaves exactly as you want. I really do not think Decimal is buying you much, and you can recreate what it offers quite easily as a small bit of library code.

[andrefmsilva]

just my 2 cents but why don't you simplify and save everything in Int but then use a computed var to get the decimal value where you need it. looks easy and avoids complicated logic...

[rcasula]

I guess another option is to provide a custom Codable conformance that aligns with the query decodable. That is, you encode/decode the decimal as an integer with a 1000 factor. But also at that point you could also just create your own wrapper type so that it behaves exactly as you want. I really do not think Decimal is buying you much, and you can recreate what it offers quite easily as a small bit of library code.

Yes sure, I could also do it with a custom codable conformance, that would work. I just don't like the fact that I am forced to have 2 implementations for the same thing (codable + querydecodable) just because I need to group some data (otherwise it would be sufficient having the custom querydecodable).
On the other hand, I don't get the wrapper type. Are you saying to create like a CustomDecimal and perform inside the calculations? That would also work, maybe even better because it's in a single place.
Something like:

@Selection
struct CustomDecimal: Codable {
    let amount: Int

    public var decimalAmount: Decimal {
        Decimal(amount / 1000)
    }

    init(decimal: Decimal) {
        self.amount = decimal * 1000
    }
}

[mbrandonw]

That's close to what I meant, but rather than a @Selection I envisioned it being just QueryBindable+QueryDecodable and having a custom Codable conformance that encodes/decodes into a single value container in order to avoid the {"amount": …} nesting.

You also have the option of just making a computed property on Int to convert to Decimal. And if you have a setter on that property you can even derive SwiftUI bindings for free:

extension Int {
  var decimal: Decimal {
    get { … }
    set { … }
  }
}

@State var expense: Expense
$expense.amount.decimal  // Binding<Decimal>

Overall I think there are lots of ways to approach this that minimize how much code you have to repeat and while keeping Decimal out of your model.

[rcasula]

I went with the custom Amount type that conforms to QueryBindable+QueryDecodable and has a custom Codable conformance.
It works great! Thanks for your suggestions!