king.edwards Comments - Answer Overflow

king.edwards

Posts Comments

DTDrizzle Team

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

Help with improving database query

Thank you!

50 replies

DTDrizzle Team

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

Help with improving database query

and the update query:

    const promptData = await drizzleClient.query.promptsTable.findFirst({
            where: eq(promptsTable.id, promptId),

            columns: {
                title: true,
                description: true
            },
            with: {
                profile: {
                    columns: {
                        username: true,
                        fullName: true,
                        avatarUrl: true
                    }
                },
                tagPromptLink: {
                    with: {
                        tag: true
                    }
                }
            }
        });

    const promptData = await drizzleClient.query.promptsTable.findFirst({
            where: eq(promptsTable.id, promptId),

            columns: {
                title: true,
                description: true
            },
            with: {
                profile: {
                    columns: {
                        username: true,
                        fullName: true,
                        avatarUrl: true
                    }
                },
                tagPromptLink: {
                    with: {
                        tag: true
                    }
                }
            }
        });

50 replies

DTDrizzle Team

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

Help with improving database query

for any one who had a similar question to this post here is the new tagPromptLinkTable and it's relations

export const tagPromptLinkTable = pgTable(
    'tag_prompt_link_table',
    {
        promptId: uuid('prompt_id')
            .notNull()
            .references(() => promptsTable.id, { onDelete: 'cascade' }),
        tagId: uuid('tag_id')
            .notNull()
            .references(() => tagsTable.id, { onDelete: 'cascade' }),
        createdBy: uuid('created_by')
            .notNull()
            .references(() => profilesTable.id, { onDelete: 'cascade' }),
        createdAt: timestamp('created_at', { withTimezone: true, mode: 'string' })
            .defaultNow()
            .notNull()
    },
    (table) => {
        return {
            tagPromptLinkTablePkey: primaryKey(table.promptId, table.tagId, table.createdBy)
        };
    }
);

export const profilesRelations = relations(profilesTable, ({ many }) => ({
    createdTagPromptLinks: many(tagPromptLinkTable)
}));

export const promptsRelations = relations(promptsTable, ({ one, many }) => ({
    profile: one(profilesTable, {
        fields: [promptsTable.profileId],
        references: [profilesTable.id]
    }),

    tagPromptLink: many(tagPromptLinkTable)
}));

export const tagsRelations = relations(tagsTable, ({ one, many }) => ({
    profile: one(profilesTable, {
        fields: [tagsTable.profileId],
        references: [profilesTable.id]
    }),

    tagPromptLink: many(tagPromptLinkTable)
}));

export const tagPromptLinkTableRelations = relations(tagPromptLinkTable, ({ one }) => ({
    profile: one(profilesTable, {
        fields: [tagPromptLinkTable.createdBy],
        references: [profilesTable.id]
    }),

    prompt: one(promptsTable, {
        fields: [tagPromptLinkTable.promptId],
        references: [promptsTable.id]
    }),

    tag: one(tagsTable, {
        fields: [tagPromptLinkTable.tagId],
        references: [tagsTable.id]
    })
}));

export const tagPromptLinkTable = pgTable(
    'tag_prompt_link_table',
    {
        promptId: uuid('prompt_id')
            .notNull()
            .references(() => promptsTable.id, { onDelete: 'cascade' }),
        tagId: uuid('tag_id')
            .notNull()
            .references(() => tagsTable.id, { onDelete: 'cascade' }),
        createdBy: uuid('created_by')
            .notNull()
            .references(() => profilesTable.id, { onDelete: 'cascade' }),
        createdAt: timestamp('created_at', { withTimezone: true, mode: 'string' })
            .defaultNow()
            .notNull()
    },
    (table) => {
        return {
            tagPromptLinkTablePkey: primaryKey(table.promptId, table.tagId, table.createdBy)
        };
    }
);

export const profilesRelations = relations(profilesTable, ({ many }) => ({
    createdTagPromptLinks: many(tagPromptLinkTable)
}));

export const promptsRelations = relations(promptsTable, ({ one, many }) => ({
    profile: one(profilesTable, {
        fields: [promptsTable.profileId],
        references: [profilesTable.id]
    }),

    tagPromptLink: many(tagPromptLinkTable)
}));

export const tagsRelations = relations(tagsTable, ({ one, many }) => ({
    profile: one(profilesTable, {
        fields: [tagsTable.profileId],
        references: [profilesTable.id]
    }),

    tagPromptLink: many(tagPromptLinkTable)
}));

export const tagPromptLinkTableRelations = relations(tagPromptLinkTable, ({ one }) => ({
    profile: one(profilesTable, {
        fields: [tagPromptLinkTable.createdBy],
        references: [profilesTable.id]
    }),

    prompt: one(promptsTable, {
        fields: [tagPromptLinkTable.promptId],
        references: [promptsTable.id]
    }),

    tag: one(tagsTable, {
        fields: [tagPromptLinkTable.tagId],
        references: [tagsTable.id]
    })
}));

50 replies

DTDrizzle Team

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

Help with improving database query

forgot to give an update, was able to successfully refactor to using a many-to-many model

50 replies

DTDrizzle Team

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

Help with improving database query

@angelelz @dane.stevens And thank you both for taking the time to help, much appreciated!! I am going to apply these lessons and work on getting the new structure with the intermediate table in place

50 replies

DTDrizzle Team

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

Help with improving database query

sweet i'll give that a shoot

50 replies

DTDrizzle Team

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

Help with improving database query

ok great, cause i was using a trigger for my current method of storing the tagIds in array, but casdaing makes that part simpler

50 replies

DTDrizzle Team

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

Help with improving database query

posgres

50 replies

DTDrizzle Team

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

Help with improving database query

ah i see, thank you for taking the time to help and also make that visual ! that would mean i would take more more space in the DB, would that be a problem if each year has about 100 rows in their promptTagLink table? also if a tag get's deleted, i imagine that i would be able to delete all the rows that had it in the promptTagLink table with a trigger function?

50 replies

DTDrizzle Team

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

Help with improving database query

ok so say if <prompt1> has <tag1> <prompt2> has <tag1, tag2> <prompt3> has <tag1, tag7> that would mean 3 rows is created in the intermediate promptTagLink table?

50 replies

DTDrizzle Team

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

Help with improving database query

ah ok i'll try that out

50 replies

DTDrizzle Team

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

Help with improving database query

I did it that way partly due to my lack of experiences with DB's and so that user can be able to assign as many tags to a prompt as they want from the list of tags they created how would i go about doing this without keeping track of the tags currently assigned to a prompt without an array?

50 replies

DTDrizzle Team

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

Help with improving database query

ah ok so i am guessing i would have to do a many-to-many relation if I want a prompt to be able to have multiple tags, and tag to be able to be assigned to multiple prompts?

50 replies

DTDrizzle Team

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

Help with improving database query

part 2:

Query: select "id", "profile_id", "title", "description", "is_favorited", "tag_ids", "visibility", "created_at", "updated_at" from "prompts" where "prompts"."profile_id" = $1 order by "prompts"."created_at" desc -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]
Query: select "id", "profile_id", "name", "created_at", "updated_at" from "tags" where "tags"."profile_id" = $1 order by "tags"."created_at" desc -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]

Query: select "id", "profile_id", "title", "description", "is_favorited", "tag_ids", "visibility", "created_at", "updated_at" from "prompts" where "prompts"."profile_id" = $1 order by "prompts"."created_at" desc -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]
Query: select "id", "profile_id", "name", "created_at", "updated_at" from "tags" where "tags"."profile_id" = $1 order by "tags"."created_at" desc -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]

50 replies

DTDrizzle Team

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

Help with improving database query

yes here it is have to break it into two parts due to text limit on discord part 1:

Query: select "prompts"."title", "prompts"."description", "prompts"."tag_ids", "profiles"."username", "profiles"."full_name", "profiles"."avatar_url", "tags"."name" from "prompts" inner join "profiles" on "profiles"."id" = "prompts"."profile_id" inner join "tags" on "tags"."id" in "prompts"."tag_ids" where "prompts"."id" = $1 -- params: ["bf3c8f29-7880-4a5d-a57e-ae88ce0f959f"]
Query: select "prompts"."title", "prompts"."description", "prompts"."tag_ids", "profiles"."username", "profiles"."full_name", "profiles"."avatar_url", "tags"."name" from "prompts" inner join "profiles" on "profiles"."id" = "prompts"."profile_id" inner join "tags" on "tags"."id" in "prompts"."tag_ids" where "prompts"."id" = $1 -- params: ["bf3c8f29-7880-4a5d-a57e-ae88ce0f959f"]
Query: select "email", "username", "full_name", "avatar_url" from "profiles" where "profiles"."id" = $1 -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]
Query: select "id", "profile_id", "title", "description", "is_favorited", "tag_ids", "visibility", "created_at", "updated_at" from "prompts" where "prompts"."profile_id" = $1 order by "prompts"."created_at" desc -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]
Query: select "id", "profile_id", "name", "created_at", "updated_at" from "tags" where "tags"."profile_id" = $1 order by "tags"."created_at" desc -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]
Query: select "email", "username", "full_name", "avatar_url" from "profiles" where "profiles"."id" = $1 -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]

Query: select "prompts"."title", "prompts"."description", "prompts"."tag_ids", "profiles"."username", "profiles"."full_name", "profiles"."avatar_url", "tags"."name" from "prompts" inner join "profiles" on "profiles"."id" = "prompts"."profile_id" inner join "tags" on "tags"."id" in "prompts"."tag_ids" where "prompts"."id" = $1 -- params: ["bf3c8f29-7880-4a5d-a57e-ae88ce0f959f"]
Query: select "prompts"."title", "prompts"."description", "prompts"."tag_ids", "profiles"."username", "profiles"."full_name", "profiles"."avatar_url", "tags"."name" from "prompts" inner join "profiles" on "profiles"."id" = "prompts"."profile_id" inner join "tags" on "tags"."id" in "prompts"."tag_ids" where "prompts"."id" = $1 -- params: ["bf3c8f29-7880-4a5d-a57e-ae88ce0f959f"]
Query: select "email", "username", "full_name", "avatar_url" from "profiles" where "profiles"."id" = $1 -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]
Query: select "id", "profile_id", "title", "description", "is_favorited", "tag_ids", "visibility", "created_at", "updated_at" from "prompts" where "prompts"."profile_id" = $1 order by "prompts"."created_at" desc -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]
Query: select "id", "profile_id", "name", "created_at", "updated_at" from "tags" where "tags"."profile_id" = $1 order by "tags"."created_at" desc -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]
Query: select "email", "username", "full_name", "avatar_url" from "profiles" where "profiles"."id" = $1 -- params: ["67732809-e7b0-451c-8f21-7c343b7bf9da"]

50 replies

DTDrizzle Team

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

Help with improving database query

and my query

const promptData = await drizzleClient.query.promptsTable.findFirst({
            where: eq(promptsTable.id, promptId),
            with: {
                tags: true
            }
        });

const promptData = await drizzleClient.query.promptsTable.findFirst({
            where: eq(promptsTable.id, promptId),
            with: {
                tags: true
            }
        });

50 replies

DTDrizzle Team

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

Help with improving database query

My schema's

export const promptsTable = pgTable('prompts', {
    id: uuid('id').defaultRandom().primaryKey().notNull(),
    profileId: uuid('profile_id')
        .notNull()
        .references(() => profilesTable.id, { onDelete: 'cascade' }),
    title: text('title').notNull(),
    description: text('description').notNull(),
    isFavorited: boolean('is_favorited').default(false).notNull(),
    tagIds: uuid('tag_ids').array(),
    visibility: promptVisibility('visibility').default('private').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true, mode: 'string' }).defaultNow().notNull()
});


export const tagsTable = pgTable(
    'tags',
    {
        id: uuid('id').defaultRandom().primaryKey().notNull(),
        profileId: uuid('profile_id')
            .notNull()
            .references(() => profilesTable.id, { onDelete: 'cascade' }),
        name: text('name').notNull(),
        createdAt: timestamp('created_at', { withTimezone: true, mode: 'string' })
            .defaultNow()
            .notNull(),
        updatedAt: timestamp('updated_at', { withTimezone: true, mode: 'string' })
            .defaultNow()
            .notNull()
    },
    (table) => {
        return {
            uniqueUserTagName: unique('unique_user_tag_name').on(table.profileId, table.name)
        };
    }
);

export const promptsRelations = relations(promptsTable, ({ many }) => ({
    tags: many(tagsTable, { fields: [promptsTable.tagIds], references: [tagsTable.id] })
}));

export const tagsRelations = relations(tagsTable, ({ one }) => ({
    prompts: one(promptsTable, { fields: [tagsTable.id], references: [promptsTable.tagIds] })
}));

export const promptsTable = pgTable('prompts', {
    id: uuid('id').defaultRandom().primaryKey().notNull(),
    profileId: uuid('profile_id')
        .notNull()
        .references(() => profilesTable.id, { onDelete: 'cascade' }),
    title: text('title').notNull(),
    description: text('description').notNull(),
    isFavorited: boolean('is_favorited').default(false).notNull(),
    tagIds: uuid('tag_ids').array(),
    visibility: promptVisibility('visibility').default('private').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true, mode: 'string' }).defaultNow().notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true, mode: 'string' }).defaultNow().notNull()
});


export const tagsTable = pgTable(
    'tags',
    {
        id: uuid('id').defaultRandom().primaryKey().notNull(),
        profileId: uuid('profile_id')
            .notNull()
            .references(() => profilesTable.id, { onDelete: 'cascade' }),
        name: text('name').notNull(),
        createdAt: timestamp('created_at', { withTimezone: true, mode: 'string' })
            .defaultNow()
            .notNull(),
        updatedAt: timestamp('updated_at', { withTimezone: true, mode: 'string' })
            .defaultNow()
            .notNull()
    },
    (table) => {
        return {
            uniqueUserTagName: unique('unique_user_tag_name').on(table.profileId, table.name)
        };
    }
);

export const promptsRelations = relations(promptsTable, ({ many }) => ({
    tags: many(tagsTable, { fields: [promptsTable.tagIds], references: [tagsTable.id] })
}));

export const tagsRelations = relations(tagsTable, ({ one }) => ({
    prompts: one(promptsTable, { fields: [tagsTable.id], references: [promptsTable.tagIds] })
}));

50 replies

DTDrizzle Team

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

Help with improving database query

Using relational queries looks awesome and does look to be better approach but I am getting en error with it as well 😭 but this is due me being new to DB and not knowing how to do things properly this was the error i got

  message: 'operator does not exist: uuid = uuid[]',
    stack: 'PostgresError: operator does not exist: uuid = uuid[]\n'

  message: 'operator does not exist: uuid = uuid[]',
    stack: 'PostgresError: operator does not exist: uuid = uuid[]\n'

50 replies

DTDrizzle Team

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

Help with improving database query

I tried this but getting this error:

 error: {
    name: 'PostgresError',
    message: 'syntax error at or near ""prompts""',
    stack: 'PostgresError: syntax error at or near ""prompts""\n' 
  },

 error: {
    name: 'PostgresError',
    message: 'syntax error at or near ""prompts""',
    stack: 'PostgresError: syntax error at or near ""prompts""\n' 
  },

50 replies

DTDrizzle Team

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

Help with improving database query

My query

/**
 * Fetches a prompt based on its ID.
 * @param {string} promptId - The ID of the prompt.
 * @returns {Promise<object|null>} The prompt data or null if not found.
 * @throws {Error} Will throw an error if the query fails.
 */
export async function fetchPromptById(promptId: string) {
    try {
        // Fetch the prompt data
        const promptData = await drizzleClient
            .select({
                prompt: {
                    title: promptsTable.title,
                    description: promptsTable.description,
                    tagIds: promptsTable.tagIds
                },
                creator: {
                    username: profilesTable.username,
                    fullName: profilesTable.fullName,
                    avatarUrl: profilesTable.avatarUrl
                }
            })
            .from(promptsTable)
            .where(eq(promptsTable.id, promptId))
            .innerJoin(profilesTable, eq(profilesTable.id, promptsTable.profileId));

        // If no prompt data is found, return null
        if (!promptData) {
            return null;
        }

        // Fetch the tag names associated with the tag IDs
        const tagNames = await drizzleClient
            .select({ name: tagsTable.name })
            .from(tagsTable)
            .where(inArray(tagsTable.id, promptData[0].prompt.tagIds));

        // Add the tag names to the prompt data
        promptData[0].prompt.tagNames = tagNames.map(tag => tag.name);

        // Return the first (and only) item in the prompt data array
        return promptData[0];
    } catch (error) {
        console.error('Error fetching prompt:', error, { promptId });
        throw new Error('Error fetching prompt');
    }
}

/**
 * Fetches a prompt based on its ID.
 * @param {string} promptId - The ID of the prompt.
 * @returns {Promise<object|null>} The prompt data or null if not found.
 * @throws {Error} Will throw an error if the query fails.
 */
export async function fetchPromptById(promptId: string) {
    try {
        // Fetch the prompt data
        const promptData = await drizzleClient
            .select({
                prompt: {
                    title: promptsTable.title,
                    description: promptsTable.description,
                    tagIds: promptsTable.tagIds
                },
                creator: {
                    username: profilesTable.username,
                    fullName: profilesTable.fullName,
                    avatarUrl: profilesTable.avatarUrl
                }
            })
            .from(promptsTable)
            .where(eq(promptsTable.id, promptId))
            .innerJoin(profilesTable, eq(profilesTable.id, promptsTable.profileId));

        // If no prompt data is found, return null
        if (!promptData) {
            return null;
        }

        // Fetch the tag names associated with the tag IDs
        const tagNames = await drizzleClient
            .select({ name: tagsTable.name })
            .from(tagsTable)
            .where(inArray(tagsTable.id, promptData[0].prompt.tagIds));

        // Add the tag names to the prompt data
        promptData[0].prompt.tagNames = tagNames.map(tag => tag.name);

        // Return the first (and only) item in the prompt data array
        return promptData[0];
    } catch (error) {
        console.error('Error fetching prompt:', error, { promptId });
        throw new Error('Error fetching prompt');
    }
}

50 replies

Gaming

Programming