Allow @Selection struct "columns" to be used in ORDER BY

[pfandrade]

Being able to issue queries returning a custom struct suitable for the current view is super nice. But it would be even nicer if we could use that new @Selection struct's columns in the ORDER BY expression.

For example, given this selection:

@Selection
struct WishedItemEntry: Identifiable {
    var id: Int { item.id }
    var item: WishedItem // this is the struct represents the table in the database
    var discountAmount: Double?
    var discountPercentage: Double?
 }

And the following query:

WishedItem.notDeleted
    .where { $0.wishlistID == id }
    .select { item in
        ListFeature.WishedItemEntry.Columns(item: item,
                                                      discountAmount: #sql("(\(item.lastKnownPrice) - \(item.originalPrice))"),
                                                      discountPercentage: #sql("(1-(\(item.lastKnownPrice) / \(item.originalPrice)))"))
    }
    .order { columns in
           // columns only refers to WishedItem column and not the columns available on WishedItemEntry
     }

It's not possible to order the results based on the columns of the WishedItemEntry. This is a powerful feature in SQL and it would be really nice to be able to write using the SQL builder syntax.

[mbrandonw]

Hi @pfandrade, I'm not sure we will be able to add this functionality to @Selection because it's not a table itself. It's really just a means of decoding data from a SQLite query.

But what you want can be done quite simply already. You can define helpers for your expressions on WishedItem.TableColumns:

extension WishedItem.TableColumns {
  var discountAmount: some QueryExpression<Double?> {
    #sql("(\(lastKnownPrice) - \(originalPrice))")
  }
  var discountPercentage: some QueryExpression<Double?> {
    #sql("(1-(\(lastKnownPrice) / \(originalPrice)))"))
  }
}

And then sorting is as simple as this:

WishedItem.notDeleted
  .where { $0.wishlistID.eq(id) }
  .select {
    ListFeature.WishedItemEntry.Columns(
      item: $0,
      discountAmount: $0.discountAmount,  
      discountPercentage: $0.discountPercentage
    )
  }
  .order(by: \.discountAmount)

[pfandrade]

Hi,

That's a great idea. Thanks!

I think that, in spite of the extension being on WishedItem's columns (instead of WishedItemEntry), it's a perfectly acceptable solution.

I just tried it and it works just fine.