Drizzle Team•2y ago

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.

  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)
}));

3 Replies

rykunoOP•2y ago

I've completed it like this at the moment, but man, im struggling to turn this into a relational query 😦

  const author = alias(users, "author");
  const hasLiked = alias(likes, "hasLiked");
  const postRows = await db
    .select({
      likes: sql<number>`COUNT(${likes.id})`.as("likes"),
      hasLiked: hasLiked,
      post: posts,
      author
    })
    .from(posts)
    .where(eq(posts.eventId, params.id))
    .leftJoin(likes, eq(likes.postId, posts.id))
    .leftJoin(
      hasLiked,
      and(
        eq(hasLiked.postId, posts.id),
        eq(hasLiked.userId, session?.user?.id || "")
      )
    )
    .innerJoin(author, eq(author.id, posts.authorId))
    .orderBy(desc(posts.createdAt))
    .groupBy(posts.id);

  const author = alias(users, "author");
  const hasLiked = alias(likes, "hasLiked");
  const postRows = await db
    .select({
      likes: sql<number>`COUNT(${likes.id})`.as("likes"),
      hasLiked: hasLiked,
      post: posts,
      author
    })
    .from(posts)
    .where(eq(posts.eventId, params.id))
    .leftJoin(likes, eq(likes.postId, posts.id))
    .leftJoin(
      hasLiked,
      and(
        eq(hasLiked.postId, posts.id),
        eq(hasLiked.userId, session?.user?.id || "")
      )
    )
    .innerJoin(author, eq(author.id, posts.authorId))
    .orderBy(desc(posts.createdAt))
    .groupBy(posts.id);

Jeyprox•2y ago

currently there is no way to use the SQL count inside a relational query, see the warning here https://orm.drizzle.team/docs/rqb#include-custom-fields. however, you can still achieve what you need with relational queries like this:

const postData = await db.query.posts.findMany({
  where: eq(posts.eventId, params.id),
  with: {
    likes: {
      columns: {
        id: true,
        userId: true,
      }
    },
    author: true
  }
})
const posts = postData.map((post) => ({
  likes: post.likes.length,
  hasLiked: post.likes.some((like) => like.userId === yourUserId),
  post: post,
  post: post.author
}));

const postData = await db.query.posts.findMany({
  where: eq(posts.eventId, params.id),
  with: {
    likes: {
      columns: {
        id: true,
        userId: true,
      }
    },
    author: true
  }
})
const posts = postData.map((post) => ({
  likes: post.likes.length,
  hasLiked: post.likes.some((like) => like.userId === yourUserId),
  post: post,
  post: post.author
}));

there might be some small things that are wrong since it wrote it here in discord, but generally this is how you could do it :)

Andrii Sherman•2y ago

it's true we will add count soon just need to decide on proper syntax for it but I guess someday June it will be in latest

Gaming

Programming

Relation Query - Get likes in post