Learn More
Resend Email
2
b2plane
140d

What is the efficient way of querying database and fetch paginated posts AND also checking if the user viewing that post has liked it?

Just like on instagram or twitter, you can just like/unlike post.

Entities:
- user
- post
- user_post_like

Ive implemented fetching posts for 1 user profile and also liking unliking each post. Thats fine

But now how do i know which post has been liked by which user?

One way i can think of is:
1. Query paginated posts (e.g. 10)
2. Loop through each post and query in user_post_like table to check if this post has been liked and if it is then set flag liked to true. That way on the frontend i can easily set liked or unliked post via ui

But this means I'd have to query database 10 times all the time, aside from querying 10 paginated posts. This doesnt seem efficient... Or am i wrong? Is this normal?

How would you model this?

rant
Favorite
Ranter
Comments
  • 1
    140d
    select post.*, array_agg(upl.user_id) as liked_by
    from post
    join user_post_like upl
    on upl.post_id = post.id
    group by post.id
    limit 10

    or something like that
  • 0
    140d
    @localpost i figured out a query without aggregating

    -- Select all posts that were created by user with ID 1,
    -- and add a flag to check if each post has been liked
    -- by the user that's viewing those posts, which would
    -- be user with ID 2.
    SELECT p.*,
    CASE
    WHEN upl.user_id IS NOT NULL THEN true
    ELSE false
    END AS "liked_by_viewer"
    FROM "post" "p"
    LEFT JOIN "user_post_like" upl ON p.id = upl.post_id AND upl.user_id = 2
    WHERE p.user_id = 1
    ORDER BY p.created_at DESC;
  • 0
    140d
    @localpost turns out. Using sophisticated query is fucking impossible (or hard as SHIT) to do in a relational database postgres system, and especially in java spring boot backend. Everything gets fucked. The models need refactoring because it cant recognize the alias returned from "AS" Query etc.

    So fuck that. I'll have to scrape that shit and query manually in a reactive stream loop and manually set the flag. So 10 posts 10 new queries
  • 0
    140d
    Skill issue
  • 0
    139d
    I might be missing something but isn't this just eager loading a Many to Many association?
  • 0
    139d
    @IHateForALiving it can be but imagine if posts had 10k likes, that means I'd have to eager load 10k many to many records, inside of just 1 post object, out of 10 paginated post objects. 10k like records * 10 posts = 100,000 database queries just to load 10 posts on page 1, per 1 user.

    That seems terrible
  • 0
    139d
    god i hate paginated filtering. good luck
devRant © 2021 Hexical Labs LLC
Privacy Policy  |  Terms of Service
Add Comment