Conditional query failing

I have a query like so:

const userId = 'abcdefg'
let selectAll = true; // or false (Condition)
let arr = [];
if (urlCondition1) {
    statusFilter.push(eq(table.status, param1));  
}
if (urlCondition2) {
    statusFilter.push(eq(table.status, param2));  
}
if (urlCondition3) {
  nameFilter.push(or(ilike(table.name, `%${param3}%`)))
}
// Below produces wrong output
await db.select().from(table).where(
  and(
    eq(table.userId, userId),
    selectAll
      ? (or(...statusFilter), or(...nameFilter))
      : inArray(table.userId, arr)
  )
);
// Below produces correct output
await db.select().from(table).where(
  and(
    eq(table.userId, userId),
    or(...statusFilter),
    or(...nameFilter))
  )
);

const userId = 'abcdefg'
let selectAll = true; // or false (Condition)
let arr = [];
if (urlCondition1) {
    statusFilter.push(eq(table.status, param1));  
}
if (urlCondition2) {
    statusFilter.push(eq(table.status, param2));  
}
if (urlCondition3) {
  nameFilter.push(or(ilike(table.name, `%${param3}%`)))
}
// Below produces wrong output
await db.select().from(table).where(
  and(
    eq(table.userId, userId),
    selectAll
      ? (or(...statusFilter), or(...nameFilter))
      : inArray(table.userId, arr)
  )
);
// Below produces correct output
await db.select().from(table).where(
  and(
    eq(table.userId, userId),
    or(...statusFilter),
    or(...nameFilter))
  )
);

Why is this happening?

13 Replies

vr7bd•4mo ago

I did this

// Below produces wrong output
await db.select().from(table).where(
  and(
    eq(table.userId, userId),
    selectAll
      ? and(or(...statusFilter), or(...nameFilter)) // added and condition here
      : inArray(table.userId, arr)
  )
);

// Below produces wrong output
await db.select().from(table).where(
  and(
    eq(table.userId, userId),
    selectAll
      ? and(or(...statusFilter), or(...nameFilter)) // added and condition here
      : inArray(table.userId, arr)
  )
);

and it works. Is there a reason why these 2 are different?

A Dapper Raccoon•4mo ago

Because JavaScript ✨

(or(...statusFilter), or(...nameFilter))

(or(...statusFilter), or(...nameFilter))

The comma operator evaluates both operands then returns the one on the right. So the expression above evaluates to the same value as just

or(...nameFilter)

or(...nameFilter)

Meanwhile, this works

and(or(...statusFilter), or(...nameFilter))

and(or(...statusFilter), or(...nameFilter))

because you're using each or() as an argument here - the comma here is not the comma operator, but rather an argument delimiter. I think the closest thing to what you were originally trying to do is to put both conditions in an array and then spread it into the and() call, such that each element in the array end up as a different positional argument:

? ...[or(...statusFilter), or(...nameFilter)]

? ...[or(...statusFilter), or(...nameFilter)]

(wherein the comma here is an array element delimiter instead of the comma operator. I guess, succinctly, if you use a comma anywhere where it does not have some other well defined purpose, JavaScript will interpret it as the rarely-used comma operator)

vr7bd•4mo ago

Ah got it! I tried putting it in an array which is what I intended to do. But it's not valid js ig (eslint: parsing error)

A Dapper Raccoon•4mo ago

Interesting 👀 What's the code/full error?

vr7bd•4mo ago

I'm getting typescript: expression expected I'm guessing because it needs a function?

A Dapper Raccoon•4mo ago

Hmm... Maybe the spread within the ternary is invalid, as that's not really spreading into the args. Might need to be like

  and(
    eq(table.userId, userId),
    ...(selectAll
      ? [or(...statusFilter), or(...nameFilter)]
      : [inArray(table.userId, arr)]
    )
  )

  and(
    eq(table.userId, userId),
    ...(selectAll
      ? [or(...statusFilter), or(...nameFilter)]
      : [inArray(table.userId, arr)]
    )
  )

...but admittedly this all seems like something TypeScript would be mad at me for 😅

vr7bd•4mo ago

Nope that didn't work too. I'm getting must have a Symbol.iterator

A Dapper Raccoon•4mo ago

Strange error... I'll play with it a bit - my TS skills aren't great and it would be nice to know what's happening here!

vr7bd•4mo ago

Me too I keep getting humbled by js. But honestly I'm find with and here since it's associative

A Dapper Raccoon•4mo ago

I think that's probably a good choice - it'll probably end up being much more readable than whatever it takes to satisfy TS anyway 😁

vr7bd•4mo ago

Hey I'm sorry! This is working! I had to restart language server. I'll stick to using and which is easier to read as you said

A Dapper Raccoon•4mo ago

Oh no problem - I did test it myself and TS seemed happy, but I didn't want to come back with "works on my system" when and() was working fine. I'm glad you've got a solution in any scenario 👍

vr7bd•4mo ago

👊

Gaming

Programming

Conditional query failing