Theo's Typesafe Cult•3y ago

need help with a complex db query

im making a hackernews clone rn. on the home page i want to display the "trending posts": posts ordered by upvotes which were posted in the last 24 hours. but if there were no posts in the last 24 hours i wanna show the previous posts as well. and also if the user scrolls down enough i still want to show posts before 24 hours as well. how would a db query for this look like?

18 Replies

nexxelOP•3y ago

      return await prisma.post.findMany({
        where: {
          createdAt: {
            // check if the post was created in the last 24 hours
            gte: new Date(Date.now() - 24 * 60 * 60 * 1000),
          },
        },
        orderBy: {
          upvotes: "desc",
        },
      });

      return await prisma.post.findMany({
        where: {
          createdAt: {
            // check if the post was created in the last 24 hours
            gte: new Date(Date.now() - 24 * 60 * 60 * 1000),
          },
        },
        orderBy: {
          upvotes: "desc",
        },
      });

this is what i have so far to fetch posts sorted by upvotes which were made in the last 24 hours

vrba•3y ago

the easiest way is to check if you get any posts if not to go to the db again and do the other query if you want to do this directly on the db level you would need to use

CASE

CASE

but i am not sure can prisma help you with that

nexxelOP•3y ago

lemme look at the docs rq yeah they don't support it yeah i guess i'll have to this then thanks

Scot•3y ago

sounds like a read model to me

nexxelOP•3y ago

whats a read model sorry im a noob

Scot•3y ago

or just do the above but applicaiton level Theyre used a lot in CQRS (ie complicated domains), but pretty much you read from the main source of truth table and make some sort of projection ie take data in from main table and create a trending table that is updated over time means you can scale out reads on it and things like that thats how big tech stacks would do it, for your use case id probs just do the above that was mentioned with the fallbck if you can't do it at db layer do it in app layer maybe add some caching (if you care about performance, maybe you don't for your project)

nexxelOP•3y ago

yeah im gonna implement caching soon this sounds complicated LUL

should read about this thank you

Scot•3y ago

compared to other things, yes CQRS is used a lot with DDD and Event Sourcing. These patterns are only meant to be used with your most complex domains in enterprise If you've done a lot of read models though they become less complicated and you end up just being like "oh yeah ill just write a readmodel for that" which can lead you down a bad path of over complicating simple solutions

nexxelOP•3y ago

that makes sense i guess this is a way to optimize at higher scale

Scot•3y ago

yeah thats correct

vibbin•3y ago

Just do them as two separate queries and then combine both results into an array pre sorted?

Unknown User•3y ago

Message Not Public

Scot•3y ago

if it can be recomputed then yeah ie if your read model were to drop or whatever you could read the sorce data again and recompute it

Unknown User•3y ago

Message Not Public

vrba•3y ago

i think this is the best way if there is a limit (and there should be)

nexxelOP•3y ago

oh very interesting! i will try to translate this thank you if not just raw sql works thanks!

Unknown User•2y ago

Message Not Public

nexxelOP•2y ago

how is this related?

Gaming

Programming

need help with a complex db query