miad Comments - Answer Overflow

miad

Posts Comments

DTDrizzle Team

•Created by Syuro on 3/1/2024 in #help

one-to-many self-relation

Sorry to bother, Found the solution. parent and children need to have the same relation name

7 replies

DTDrizzle Team

•Created by Syuro on 3/1/2024 in #help

one-to-many self-relation

@Brooks693 @Mykhailo @Syuro Hello there, I got the same problem and this solution fixed it, umm partially. Selecting comments with parentComment works. but selecting comment with replies doesn't. I my case, it's about categories. this is the categories schema:

export const categories = pgTable(
    'categories',
    {
        id: serial('id').primaryKey(),
        createdAt: timestamp('created_at', { precision: 6, withTimezone: true }).defaultNow(),
        updatedAt: timestamp('updated_at', { precision: 6, withTimezone: true })
            .defaultNow()
            .$onUpdate(() => new Date()),
        name: varchar('name', { length: 30 }).unique().notNull(),
        parentId: integer('parent_id'),
    },
    (categories) => {
        return {
            categoryNameIdx: uniqueIndex('category_name_idx').on(categories.name),
        };
    },
);

export const categoriesRelations = relations(categories, ({ one, many }) => ({
    parent: one(categories, {
        fields: [categories.parentId],
        references: [categories.id],
        relationName: 'child_to_parent_category',
    }),
    children: many(categories, { relationName: 'parent_to_children_category' }),
}));

export const categories = pgTable(
    'categories',
    {
        id: serial('id').primaryKey(),
        createdAt: timestamp('created_at', { precision: 6, withTimezone: true }).defaultNow(),
        updatedAt: timestamp('updated_at', { precision: 6, withTimezone: true })
            .defaultNow()
            .$onUpdate(() => new Date()),
        name: varchar('name', { length: 30 }).unique().notNull(),
        parentId: integer('parent_id'),
    },
    (categories) => {
        return {
            categoryNameIdx: uniqueIndex('category_name_idx').on(categories.name),
        };
    },
);

export const categoriesRelations = relations(categories, ({ one, many }) => ({
    parent: one(categories, {
        fields: [categories.parentId],
        references: [categories.id],
        relationName: 'child_to_parent_category',
    }),
    children: many(categories, { relationName: 'parent_to_children_category' }),
}));

Now this query works:

    const categories = await this.drizzleService.db.query.categories.findMany({
            where: ilike(schema.categories.name, getCategoryDto.name),
            with: {
                parent: true,
            },
        });

    const categories = await this.drizzleService.db.query.categories.findMany({
            where: ilike(schema.categories.name, getCategoryDto.name),
            with: {
                parent: true,
            },
        });

But this gives me an error:

        const categories = await this.drizzleService.db.query.categories.findMany({
            where: ilike(schema.categories.name, getCategoryDto.name),
            with: {
                children: true,
            },
        });

        const categories = await this.drizzleService.db.query.categories.findMany({
            where: ilike(schema.categories.name, getCategoryDto.name),
            with: {
                children: true,
            },
        });

And the error is:

There is not enough information to infer relation "categories.children"

There is not enough information to infer relation "categories.children"

Is this even possible? or only joining with parentId would work?

7 replies

Gaming

Programming