Inconsistency with inferred Types using Join

Open Lucasdsk opened this issue 8 months ago • 0 comments

Bug report

[ ] I confirm this is a bug with Supabase, not with my own application.
[x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Context:

I have the following relationship between the tables categories and sub-categories:

Also the database.types.ts created by the supabase gen types:

categories: {
        Row: {
          color: string | null
          created_at: string
          id: number
          name: string | null
          user_id: string
        }
        Insert: {
          color?: string | null
          created_at?: string
          id?: number
          name?: string | null
          user_id?: string
        }
        Update: {
          color?: string | null
          created_at?: string
          id?: number
          name?: string | null
          user_id?: string
        }
        Relationships: []
      }
      "sub-categories": {
        Row: {
          category_id: number
          color: string | null
          created_at: string
          id: number
          name: string | null
        }
        Insert: {
          category_id: number
          color?: string | null
          created_at?: string
          id?: number
          name?: string | null
        }
        Update: {
          category_id?: number
          color?: string | null
          created_at?: string
          id?: number
          name?: string | null
        }
        Relationships: [
          {
            foreignKeyName: "sub-categories_category_id_fkey"
            columns: ["category_id"]
            isOneToOne: false
            referencedRelation: "categories"
            referencedColumns: ["id"]
          },
        ]
      }

Goal:

Select the sub-categories and its corresponding category using the relationship category_id and categories:id and also get the types definition as described in the doc: https://supabase.com/docs/guides/database/joins-and-nesting?queryGroups=language&language=js#typescript-types-for-joins

const subcategoriesDataQuery = supabase.from('sub-categories').select(`
    id,
    name,
    color,
    categories(
      id,
      name,
      color
    )
  `)

Problems:

The inferred type brings an array of categories instead of a single object:

But the query result brings the categories as an object not an array:

Trying to find a solution for it I found this discussion where one of the suggestions are to use the foreign key in the query: https://github.com/orgs/supabase/discussions/7610#discussioncomment-8513745

Then if I change the query to:

const subcategoriesDataQuery = supabase.from('sub-categories').select(`
    id,
    name,
    color,
    category:categories!sub-categories_category_id_fkey(
      id,
      name,
      color
    )
  `)

I get this error related to the type: