Relation Query - Get likes in post
Playing around with relational queries and I'm not quite getting how I'd retrieve the count of likes on a given post.
I'm able to accomplish this in sql, but I'm having a hard time translating or failing to understand why i cant translate this to drizzle.
I'm able to accomplish this in sql, but I'm having a hard time translating or failing to understand why i cant translate this to drizzle.
const postResults = await db.query.posts.findMany({
where: eq(posts.eventId, params.id),
with: {
author: true,
likes: true
},
orderBy: desc(posts.createdAt)
}); const postResults = await db.query.posts.findMany({
where: eq(posts.eventId, params.id),
with: {
author: true,
likes: true
},
orderBy: desc(posts.createdAt)
});export const likes = mysqlTable(
"likes",
{
id: cuid2("id").primaryKey().notNull(),
postId: cuid2("post_id").notNull(),
userId: cuid2("user_id").notNull(),
createdAt: timestamp("created_at").notNull().defaultNow(),
updatedat: timestamp("updated_at").notNull().defaultNow().onUpdateNow()
},
like => ({
postUserIndex: uniqueIndex("likes__post_id__user_id__idx").on(
like.postId,
like.userId
),
postIndex: index("likes__post_id").on(like.postId)
})
);
export const posts = mysqlTable("posts", {
id: cuid2("id").primaryKey().notNull(),
text: varchar("text", { length: 750 }).notNull(),
authorId: cuid2("author_id").notNull(),
eventId: cuid2("event_id").notNull(),
createdAt: timestamp("created_at").notNull().defaultNow(),
updatedAt: timestamp("updated_at").notNull().defaultNow().onUpdateNow()
});
export const likesRelations = relations(likes, ({ one }) => ({
post: one(posts, {
fields: [likes.postId],
references: [posts.id]
}),
user: one(users, {
fields: [likes.userId],
references: [users.id]
})
}));
export const postsRelations = relations(posts, ({ one, many }) => ({
author: one(users, {
fields: [posts.authorId],
references: [users.id]
}),
event: one(events, {
fields: [posts.eventId],
references: [events.id]
}),
likes: many(likes)
}));export const likes = mysqlTable(
"likes",
{
id: cuid2("id").primaryKey().notNull(),
postId: cuid2("post_id").notNull(),
userId: cuid2("user_id").notNull(),
createdAt: timestamp("created_at").notNull().defaultNow(),
updatedat: timestamp("updated_at").notNull().defaultNow().onUpdateNow()
},
like => ({
postUserIndex: uniqueIndex("likes__post_id__user_id__idx").on(
like.postId,
like.userId
),
postIndex: index("likes__post_id").on(like.postId)
})
);
export const posts = mysqlTable("posts", {
id: cuid2("id").primaryKey().notNull(),
text: varchar("text", { length: 750 }).notNull(),
authorId: cuid2("author_id").notNull(),
eventId: cuid2("event_id").notNull(),
createdAt: timestamp("created_at").notNull().defaultNow(),
updatedAt: timestamp("updated_at").notNull().defaultNow().onUpdateNow()
});
export const likesRelations = relations(likes, ({ one }) => ({
post: one(posts, {
fields: [likes.postId],
references: [posts.id]
}),
user: one(users, {
fields: [likes.userId],
references: [users.id]
})
}));
export const postsRelations = relations(posts, ({ one, many }) => ({
author: one(users, {
fields: [posts.authorId],
references: [users.id]
}),
event: one(events, {
fields: [posts.eventId],
references: [events.id]
}),
likes: many(likes)
}));