Drizzle Team•9mo ago

Ways to check if a user liked

Imagine the following objects in your schema: Post, Likes, User. Imagine also the following relations: User has many Likes (own made Likes) Post has many Likes Likes has one User Likes has one Post So Likes is the join table for the many-to-many connection between "One Post has many likes (from users) and one User has many liked Posts". If I now want to load a feed, how to effectively check, if the user liked the post, that the user see's in his feed? By the way: That is not the only part, where this kind of solution append. Imagine having a User, that has many bought products. And one Product has many users who bought that. The join table is then something like "purchase", which contain a one connection to both: Product and User

9 Replies

B33fb0n3OP•9mo ago

bump bump bump

Mykhailo•9mo ago

hello, @B33fb0n3! Am I right that you want to know, if some user liked specific posts?

B33fb0n3OP•9mo ago

yes. Imagine generating a feed and then effectively check if the user liked the specific post. Or in my purchase example imagine checking if someone bought a specific product. Or list all products and check for every specific one, if the customer bought it. Or or or..

Mykhailo•9mo ago

@B33fb0n3

 const userId = 1;

 const sq = db
   .select({ id: sql`1` })
   .from(likes)
   .where(and(eq(likes.userId, userId), eq(likes.postId, posts.id)));

 const response = await db.select({ likedByUser: exists(sq) }).from(posts);

 const userId = 1;

 const sq = db
   .select({ id: sql`1` })
   .from(likes)
   .where(and(eq(likes.userId, userId), eq(likes.postId, posts.id)));

 const response = await db.select({ likedByUser: exists(sq) }).from(posts);

So, you can accomplish it using subquery

B33fb0n3OP•9mo ago

Wow that looks complicated but also seems to be promising. Is that effective to check for every post inside the feed? 2 queries that query a big „likes“ table and that for each post from the feed… 🧐

Mykhailo•9mo ago

you should also create composite key for likes table

B33fb0n3OP•9mo ago

Yea, I have the composite primary key 👍 But is it still effective?

Mykhailo•9mo ago

yes

B33fb0n3OP•9mo ago

hm ok.. thanks 👍

Gaming

Programming

Ways to check if a user liked