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

vr7bdOP•7mo 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•7mo 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)

vr7bdOP•7mo 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•7mo ago

Interesting 👀 What's the code/full error?

vr7bdOP•7mo ago

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

A Dapper Raccoon•7mo 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 😅

vr7bdOP•7mo ago

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

A Dapper Raccoon•7mo 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!

vr7bdOP•7mo ago

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

A Dapper Raccoon•7mo 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 😁

vr7bdOP•7mo 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•7mo 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 👍

vr7bdOP•7mo ago

👊

Gaming

Programming

Conditional query failing