Does orderBy guarantee evaluation order of where?

Given the example:

await db.query.posts.findMany({
  orderBy: [asc(posts.id)],
  where: <-- some logic that depends on knowing the post id -->
});

await db.query.posts.findMany({
  orderBy: [asc(posts.id)],
  where: <-- some logic that depends on knowing the post id -->
});

Would the where clause execute in the order specified by orderBy? This is important because my where logic depends on knowing the post id. If the where is executed in ascending order however, this is a non-issue.

5 Replies

Angelelz•12mo ago

This question is too wide, there are too many "depends" here In general, the where clause will execute before the order by

Angelelz•12mo ago

Take a look here for some insights https://www.geeksforgeeks.org/order-of-execution-of-sql-queries/#

GeeksforGeeks

Order of Execution of SQL Queries - GeeksforGeeks

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

francis•12mo ago

having your where clause be row order-dependent is asking for a bad time I'm genuinely not even sure how you would write one

Angelelz•12mo ago

I'm thinking about this, what kind of "depends on knowing post id" is this where? Feels like most of the wheres will depend on the id

HigherFlyerOP•12mo ago

Ok yeah probably terrible approach/architecture in the first place So let me restructure it a little: The original example I gave is misleading for simplicity sake, there's a bit more going on I've got chats each with their own messages:

await db.query.chats.findMany({
   with: {
      messages: true
   }
});

await db.query.chats.findMany({
   with: {
      messages: true
   }
});

But as you can imagine there can be A LOT of messages. So, given the client caches messaged, I only want to query for what the client doesn't already have caches (aka the delta) So essentially I want to get every group my user is in, with only messages for that group I don't already have Since messages are always in chronological order, that amounts to giving the server the timestamp of the most recent message the client has for each group and only getting the messages after that (In case it helps understand, another minimal example):

await db.query.posts.findMany({
   with: {
      comments: {
          where: gt(posts.timestamp, CLIENT_MOST_RECENT[??]
       }
   }
});

await db.query.posts.findMany({
   with: {
      comments: {
          where: gt(posts.timestamp, CLIENT_MOST_RECENT[??]
       }
   }
});

Before I was just doing all this in multiple requests for each group but I was trying to pair down the number of requests to just one Hmmm I suppose actually I could just send one timestamp of the most recent message the client has, as if that exists than it should also have every message older than that regardless of the group (or just store the timestamp of the last time I queried the server) I think I may have just solved my little issue... Rubber ducky effect🦆! I'll keep this open dor a bit longer in case anyone wants to add anything/for posterity

Gaming

Programming

Does orderBy guarantee evaluation order of where?