Failing to decode models when querying foreign tables

[bstillitano]

Bug report

Describe the bug

When providing a .select modifier that points to a foreign table, values fail to decode.

To Reproduce

Models

struct Transaction: Codable {
    var id: UUID
    var name: String
    var subtitle: String?
    var merchant: String?
    var amount: Decimal
    var tax_deductible: Bool = false
    var date: Date
    var account: TransactionAccount?
}

enum AccountType: String, Codable, CaseIterable {
    case debit
    case savings
    case loan
    case credit
    case cash
}

struct TransactionAccount: Codable, Hashable {
    var id: UUID
    var name: String
    var description: String?
    var type: AccountType
    var color: String
}

SDK Usage

do {
            return try await Database.shared
                .client
                .database
                .from("transactions")
                .select("*, account(*)")
                .in("type", value: scope.transactionTypes.map({ $0.rawValue }))
                .execute()
                .value
        } catch {
            return []
        }

Swift Error

Printing description of error:
▿ DecodingError
  ▿ typeMismatch : 2 elements
    - .0 : Swift.Dictionary<Swift.String, Any>
    ▿ .1 : Context
      ▿ codingPath : 2 elements
        ▿ 0 : _JSONKey(stringValue: "Index 2", intValue: 2)
          ▿ rep : Rep
            - index : 2
        - 1 : CodingKeys(stringValue: "account", intValue: nil)
      - debugDescription : "Expected to decode Dictionary<String, Any> but found a string instead."
      - underlyingError : nil

Expected behavior

The docs make it clear that I should be able to do this and get back the full object from the foreign table. https://supabase.com/docs/reference/swift/select?example=querying-foreign-table-with-count

Additional context

If I add every value to my query individually, the request works as expected:

do {
            return try await Database.shared
                .client
                .database
                .from("transactions")
                .select(
                """
            id,
            description,
            amount,
            type,
            date,
            name,
            tax_deductible,
            account(*)
            """
            )
                .in("type", value: scope.transactionTypes.map({ $0.rawValue }))
                .execute()
                .value
        } catch {
            return []
        }
    }

[bstillitano]

Digging a little deeper into this, it seems like the value being decoded, is not the value being returned from the server. Looking at the response from the url request, I can see that it is not the value that execute().data returns. In the screenshot below, you can see on the left you can see the raw response from the API, whereas on the right, you can see the value when I print data

[grdsdev]

Hi @bstillitano thanks for opening the issue, I'll investigate it.

The foreign key to account schema is named account, and then when you query for the account(*), I think it breaks because the query result and the foreign key has the same name account. I'd suggest you rename the foreign key to something like account_id and then retry the same query using the *,account(*).

While that, I'll investigate to check if there is any other issue.

Thanks again.

[bstillitano]

Thanks for getting back to me @grdsdev , I'm not quite sure how to do that. Are you able to point me in the right direction on how to do that? I can't see it in the GUI anywhere.

[grdsdev]

Sure, see the account column? Rename it to account_id, as it holds the reference for the account through its id.

[bstillitano]

@grdsdev have done that and get the following error:

{
  "code" : "PGRST200",
  "message" : "Could not find a relationship between 'transactions' and 'account' in the schema cache",
  "details" : "Searched for a foreign key relationship between 'transactions' and 'account' in the schema 'public', but no matches were found.",
  "hint" : "Perhaps you meant 'accounts' instead of 'account'."
}

For what it's worth:

1. Table is named accounts
2. Passing account(*) into the query (notice no s on the end)

It's worth noting also, that if I pass accounts that works fine, however the value comes back as accounts in the JSON response, whereas my column in Postgres as well as my local model has account (no s on the end)

[grdsdev]

When you pass account(*) it tries to loads the relationship for account schema which is wrong, as it is named accounts.

To fix that, you need to use field aliasing, use account:accounts(*) the first part before the : is the alias you want.

[grdsdev]

@bstillitano I hope that helped you, I'll be closing this issue for know, if you're still having issues with it, feel free to re-open it.

Thanks.

[bstillitano]

Apologies, this issue is unresolved. That didn't help, I've just had to provide the entire query as a string for now to work around this

[grdsdev]

@bstillitano can you provide me the full schema for the transactions and account table?

[bstillitano]

@grdsdev sure, see attached

[grdsdev]

@bstillitano I was able to make it work by applying the following changes:

1. Rename the account column in the transactions table to account_id.

alter table transactions rename column account to account_id;

2. Define your struct field as var account: TransactionAccount?
3. Query using *,account:accounts(*)

I'll chat with the team to add some best practices to the docs regarding the implementation of relationships.

Thanks.