Return .single() for an inner join?

[jimmiejackson414]

I have a query I'm running like this:

const { data, error } = await supabase
  .from<User>('users')
  .select('*, customers!inner(*), subscriptions!inner(*)')
  .eq('id', userId)
  .single();
console.log({ data });

This returns a User object, with a nested array of Customers and Subscriptions. Each User however should only have one Subscription and Customer object attached though. Is there a way to essentially call .single() on those inner joins?

EDIT: To clarify, I have a customers and subscriptions table, and each have a user_id column tied to users.

[steve-chavez]

Hm, so would you say that for your model, a user always has a single subscription and it's a single customer? user has a one-to-one relationship with subscription and customer?

[steve-chavez]

@Kamahl19 Can you format your snippet so it's all declarative (no ALTER TABLE, instead declare the FKs with REFERENCES and inline PK), otherwise it's hard to visualize.

[steve-chavez]

Also note that you can define one-to-one relationships manually, see https://postgrest.org/en/stable/references/api/resource_embedding.html#computed-relationships

[Kamahl19]

CREATE TABLE
  "books" (
    "id" UUID NOT NULL DEFAULT gen_random_uuid(),
    "title" TEXT NOT NULL,
    "author" TEXT NOT NULL,
    "description" TEXT NOT NULL,
    "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    CONSTRAINT "books_pkey" PRIMARY KEY ("id")
  );

CREATE TABLE
  "reading_list" (
    "userId" UUID NOT NULL,
    "bookId" UUID NOT NULL,
    "finished" BOOLEAN NOT NULL DEFAULT false,
    "rating" SMALLINT NOT NULL DEFAULT '0'::smallint,
    "note" TEXT NOT NULL DEFAULT ''::text,
    "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    CONSTRAINT "reading_list_pkey" PRIMARY KEY ("userId", "bookId"),
    CONSTRAINT "reading_list_userId_fkey" FOREIGN KEY ("userId") REFERENCES "auth"."users" ("id") ON DELETE CASCADE,
    CONSTRAINT "reading_list_bookId_fkey" FOREIGN KEY ("bookId") REFERENCES "books" ("id") ON DELETE CASCADE
  );

[steve-chavez]

Hm, I don't see the one-to-one according to your table structure. If anything it looks like you have a many-to-many on books and users using the reading_list as join table. Check https://postgrest.org/en/stable/references/api/resource_embedding.html#many-to-many-relationships

[steve-chavez]

books and reading_list should form a many-to-one though. Maybe try doing NOTIFY pgrst, 'reload schema', in case the schema cache didn't get updated somehow.

[RyannGalea]

Any progress on this? I'm in the same situation where the inner join for a parent should always be singular.

[madeleineostoja]

This has been patched in PostgREST, supabase just needs a version bump. It’s a breaking change so I assume it’ll be rolled into the v2 RC

[activenode]

Is there a commit/patch link?

[chirastefan]

is there a plan to implement this in supabase_flutter also?

[steve-chavez]

Should work transparently for flutter, new projects already come with this change. See supabase/postgrest-js#223.

[gabrielsestrem]

@steve-chavez have you guys published something related to this recently? After adding a Small add-on to my plan, my inner joins are now returning {} instead of an array [{}].

That change broke my entire application.

[gabrielsestrem]

nevermind... I already changed all the code to the new format

[steve-chavez]

@gabrielsestrem Hm, sounds this is related to this newish feature supabase/postgrest-js#223

[nlarusstone]

Has anyone gotten the correct typescript types to be generated here? I'm on the new version of Postgrest (10.1.2) but typescript still thinks this could be either a list or a single item (even though it always returns just a single object)

[Rykuno]

Has anyone gotten the correct typescript types to be generated here? I'm on the new version of Postgrest (10.1.2) but typescript still thinks this could be either a list or a single item (even though it always returns just a single object)

Same. This is a very annoying issue :/

[RyannGalea]

In most cases I'll just create my own types to inject the data how it should be seen in the app, in this example I Omit the 'last_message' field as it thinks it's a string as it's a JSONB field.

I then type it along with some other joined data.

type BaseConversation    = Omit<Database['public']['Tables'][Tables.conversations]['Row'], 'last_message'>
export type Conversation = BaseConversation & {
  contact?: Partial<Contact>,
  user?: Partial<User>,
  last_message?: { body?: string, dateCreated?: string | Date }
}

[hipdev]

What is working in my case is:
if is a one to one relation I give the unique option in the dashboard, then after I just generate types again. The isOneToOne option comes as true. The type is a mess but is working if I use the foreignKeyName value in the supabase generated types in the join query.

[hipdev]

@logemann try it using this approach:
Create the relation with the foreign key name

Seems to resolve the issue in the meantime

[logemann]

I fixed it by upgrading to latest supabase-js npm package ;-)

[hipdev]

So, the Supa squad fixed it 😄

[whollacsek]

I confirm switching to foreignKeyName works but we should be able to use column name as well, still not an ideal DX

[louisprp]

For me it works as it's supposed to work, picking up all relations as they are intended, while also dynamically providing the correct type from the complex query to reuse.

Having a function defined like this

export const getOrders = async (
  client: SupabaseClient<Database>
) => {
  const query = client
    .from("orders")
    .select(
      "id, customer_id:users!orders_customer_id_fkey(*), supplier_id:users!orders_supplier_id_fkey(*), status, created_at, orders_products (*)"
    )
  return query
}

I can now get the type of a single order like this:

export type Order = NonNullable<AsyncReturnType<typeof getOrders>["data"]>[number]

Where AsyncReturnType looks like this:

// Types for getting the return type of an asynchronous function
type PromiseType<T> = T extends Promise<infer U> ? U : never

export type ErrorType = { error: string }

/**
 * Represents the return type of an asynchronous function.
 * It extracts the resolved value from a Promise and excludes any potential error type.
 * Used primarily to get supabase action return types.
 */
export type AsyncReturnType<T extends (...args: any) => Promise<any>> = Exclude<
  PromiseType<ReturnType<T>>,
  ErrorType
>