How would I get 2 unique values?

I got my post likes table.

export const postLike = sqliteTable(
  "post_like",
  {
    postId: text("post_id")
      .notNull()
      .primaryKey()
      .references(() => post.id),

    userId: text("user_id")
      .notNull()
      .references(() => user.id),
  },
  (self) => ({
    unq: unique("unique_post_likes").on(self.postId, self.userId),
  })
);

export const postLike = sqliteTable(
  "post_like",
  {
    postId: text("post_id")
      .notNull()
      .primaryKey()
      .references(() => post.id),

    userId: text("user_id")
      .notNull()
      .references(() => user.id),
  },
  (self) => ({
    unq: unique("unique_post_likes").on(self.postId, self.userId),
  })
);

But if I already have same post id with different user. It returns error "LibsqlError: SQLITE_CONSTRAINT: SQLite error: UNIQUE constraint failed: post_like.post_id"

5 Replies

Angelelz•15mo ago

You have the postId as the primary key, that will implicitly add a unique constrain on that column.

Angelelz•15mo ago

This should be the way you do it: https://orm.drizzle.team/docs/indexes-constraints#composite-primary-key

Indexes & Constraints - DrizzleORM

Drizzle ORM | %s

SamirOP•15mo ago

Thank you so much!

  const latestPosts = await db
    .select()
    .from(post)
    .limit(15)
    .innerJoin(user, eq(user.id, post.userId))
    .innerJoin(category, eq(category.slug, post.categorySlug))
    .fullJoin(postLike, eq(postLike.postId, post.id))
    .orderBy(desc(post.createdAt));

  const latestPosts = await db
    .select()
    .from(post)
    .limit(15)
    .innerJoin(user, eq(user.id, post.userId))
    .innerJoin(category, eq(category.slug, post.categorySlug))
    .fullJoin(postLike, eq(postLike.postId, post.id))
    .orderBy(desc(post.createdAt));

on line 7: Im attaching post like. How would I get them in an array? Or is there any way to just get the count of it?

Angelelz•15mo ago

You'll probably need to do some json aggregation if you want to do it at the database level

Angelelz•15mo ago

You could also do it in JS/TS: https://orm.drizzle.team/docs/joins#aggregating-results

Joins [SQL] - DrizzleORM

Drizzle ORM | %s

Gaming

Programming

How would I get 2 unique values?