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?

Ask 1 (specific researched non-duplicate) question. Your title & body asked different questions. Why is “Can someone help me?” not an actual question? Please avoid social & meta commentary in posts. — philipxy
– philipxy, Commented Feb 16, 2024 at 18:05
How much research effort is expected of Stack Overflow users? minimal reproducible example Tips for asking a good SQL question Asking query performance questions How to Optimize Queries in a Database - The Basics What are your most common sql optimizations? When and why are database joins expensive? How to Ask Help center — philipxy
– philipxy, Commented Feb 16, 2024 at 18:07

TSCAmerica.com · Accepted Answer · 2024-02-16 14:11:10Z

0

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$;

answered Feb 16, 2024 at 14:11

TSCAmerica.com

5,45712 gold badges57 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related