Nested @Selection query

[horatiu1921]

Hi,

I am in the process of converting my app from SwiftData to SQLiteData, but I am struggling with the mapping of SwiftData relationships to SQLiteData.

Consider the following tables:

    @Table
    struct SaleDocument: Identifiable, Codable {
        var id: UUID
        var date: Date
        var customer: Customer.ID
        var currency: Currency.ID
        var netAmount: Int
        var vatAmount: Int
        var totalAmount: Int
    }
    
    @Table
    struct SaleItem: Identifiable, Codable {
        var id: UUID
        var saleDocumentID: SaleDocument.ID
        var vat: VAT.ID?
        var quantity: Int
        var price: Int
        var amount: Int
        var vatAmount: Int
    }
    
    @Table
    struct Customer: Identifiable, Codable {
        var id: UUID
        var name: String
        var regsitrationNumber: String
        var address: String
    }
    
    @Table
    struct Currency: Identifiable, Codable {
        var id: UUID
        var code: String
        var name: String
    }
    
    @Table
    struct VAT: Identifiable, Codable {
        var id: UUID
        var code: String
        var name: String
        var value: Double
    }

And query the above tables to produce the following:

    @Selection
    struct Invoice: Codable {
        var id: UUID {
            invoice.id
        }
        
        let invoice: SaleDocument
        let currency: Currency
        let customer: Customer
        var items: [InvoiceItem]
    }
    
    @Selection
    struct InvoiceItem: Codable {
        var id: UUID {
            item.id
        }
        
        let item: SaleItem
        let vat: VAT?
    }

1. How would I write a query that fetches all invoices and their items? Fetching the sale invoices is clear, my issue is that InvoiceItem is also a @Selection that joins several tables. Is it possible to build InvoiceItem.Columns() inside the same select that builds Invoice.Columns()?

SaleDocument.all
            .join(Customer.all) { $0.customer.eq($1.id) }
            .join(Currency.all) { $0.currency.eq($2.id) }
            .join(SaleItem.all) { $0.id.eq($3.saleDocumentID) }
            .leftJoin(VAT.all) { $3.vat.eq($4.id) }
            .select {
                Invoice.Columns(invoice: $0.jsonObject(), currency: $1.jsonObject(), customer: $2.jsonObject(), items: ?)
            }

3. Is it possible to skip setting a column during select? If I set a default value to items and want only Invoice.Columns(invoice: currency: customer:), the compiler is unable to compile the query.
4. Somehow derived from the previous question, when fetching the list of invoices, the invoice items are not needed within the invoice row, they are used only inside InvoiceDetailView. From a performance perspective, is ok to fetch all tables at once or is better to fetch additional data (invoice items and other tables) only when needed?
5. Is it mandatory to use JSONRepresentation for fetched tables: @Column(as: Currency.JSONRepresentation.self) let currency: Currency? Or is ok with simple properties.

Thank you!
Horatiu