How do I optimize my query for a table with filters on two columns that has a one-to-many relationship

Question

Using Prisma and PostgreSQL:

model Chat {
  id       Int           @id @default(autoincrement())
  Users    UserChat[]    @relation(fields: [], references: [])
  Keywords ChatKeyword[]
  chatName String
}

model ChatKeyword {
  id      Int    @id @default(autoincrement())
  keyword String
  Chat    Chat?  @relation(fields: [chatId], references: [id])
  chatId  Int?
}

model UserChat {
  id     Int    @id @default(autoincrement())
  Chat   Chat   @relation(fields: [chatId], references: [id])
  chatId Int
  User   User   @relation(fields: [userId], references: [id])
  userId String
}

I want to query for chat records that:

• have a certain User.
• have a keyword.

const getChatSuggestions = ({ userId, keyword }) => {
  db.chat.findMany({
    where: {
      Users: { some: { userId: { equals: userId } } },
      Keywords: { some: { keyword: { contains: keyword } } },
    },
  })
}

How do I optimize my query for a table with filters on two columns that has a one-to-many relationship?

Does this query go through all the records of the Chat table?

I.e. if I have 1000 records in Chat, will it go through each Chat record's User and Keywords values?

Answer 1

You can utilize the RETURNING clause in PostgreSQL within your PL/pgSQL function like below

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
  -- Declare a variable to hold the id from the other_table insertion
  other_table_id bigint; -- Adjust the type according to your actual id column type
BEGIN
  -- First, insert into other_table and capture the generated id
  INSERT INTO other_table (created_by, somecolumn)
  VALUES (new.id, 100000)
  RETURNING id INTO other_table_id; -- Capture the returned id
  
  -- Next, insert into user_profile including the captured id as a foreign key
  INSERT INTO public.user_profile (id, email, full_name, foreign_column)
  VALUES (new.id, new.email, new.raw_user_meta_data->>'full_name', other_table_id);
  
  RETURN new;
END;
$function$;