king.edwards Posts - Answer Overflow

king.edwards

•Created by king.edwards on 9/13/2023 in #help

Help with improving database query

Hello, I am seeking advice on how to improve a database query I am currently working with. My objective is to get the data of a prompt using its ID, and this data includes the names of the tags associated with it.The currently way I am doing this works, but feels hacking so wondering if theres a more efficient way to achieve this, possibly through restructuring the database or altering the query itself. Below are the definitions for my "prompts" and "tags" tables from supabase DB:

CREATE TABLE public.prompts (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    profile_id UUID NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    is_favorited BOOLEAN NOT NULL DEFAULT FALSE,
    tag_ids UUID[] NULL,
    visibility public.prompt_visibility NOT NULL DEFAULT 'private'::prompt_visibility,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT prompts_pkey PRIMARY KEY (id),
    CONSTRAINT prompts_profile_id_fkey FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
) TABLESPACE pg_default;

CREATE TABLE public.prompts (
    id UUID NOT NULL DEFAULT gen_random_uuid(),
    profile_id UUID NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    is_favorited BOOLEAN NOT NULL DEFAULT FALSE,
    tag_ids UUID[] NULL,
    visibility public.prompt_visibility NOT NULL DEFAULT 'private'::prompt_visibility,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT prompts_pkey PRIMARY KEY (id),
    CONSTRAINT prompts_profile_id_fkey FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
) TABLESPACE pg_default;

create table
  public.tags (
    id uuid not null default gen_random_uuid (),
    profile_id uuid not null,
    name text not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    constraint tags_pkey primary key (id),
    constraint unique_user_tag_name unique (profile_id, name),
    constraint tags_profile_id_fkey foreign key (profile_id) references profiles (id) on delete cascade
  ) tablespace pg_default;

create table
  public.tags (
    id uuid not null default gen_random_uuid (),
    profile_id uuid not null,
    name text not null,
    created_at timestamp with time zone not null default now(),
    updated_at timestamp with time zone not null default now(),
    constraint tags_pkey primary key (id),
    constraint unique_user_tag_name unique (profile_id, name),
    constraint tags_profile_id_fkey foreign key (profile_id) references profiles (id) on delete cascade
  ) tablespace pg_default;

50 replies

Gaming

Programming