MYSQL XDEVAPI Multiple OR (II) Request

Question

I'm using the XDEVAPI and trying to use the or(||) statement in javascript, but it seems to break after 1 or/(||) statements, I have 5 separate statuses I'm trying to pull from the DB:

.select(['customer_name',
         'account_status',
                  ])
         .where('customer_name like :customer_name && account_status like :account_status || account_status like :account_status || account_status like :account_status')
         .bind('customer_name', customer_name)
         .bind('account_status', 'Some_Status_1')
         .bind('account_status','Some_Status_2')
         .bind('account_status', 'Some_Status_3')
         .execute()

Can I use REGEXP 'Some_Status_1'|Some_Status_2'| etc?

If so what would the syntax be?

Or is there another method that I am missing.

Thanks for any help you can offer.

score 1 · Accepted Answer · answered Sep 15 '21 at 17:33

1

I don't think the issue is related to OR per se but to the fact that you are using duplicate placeholders. CRUD style APIs use named parameters, as described here. By calling bind('account_status', 'something') multiple times, you are simply replacing the last value of the account_status placeholder.

To do what you want (I think), you need to use different placeholder names. For instance:

.select(['customer_name', 'account_status'])
  .where('customer_name like :customer_name && account_status like :account_status1 || account_status like :account_status2 || account_status like :account_status3')
  .bind('customer_name', customer_name)
  .bind('account_status1', 'Some_Status_1')
  .bind('account_status2', 'Some_Status_2')
  .bind('account_status3', 'Some_Status_3')
  .execute()

Disclaimer: I'm the lead developer of the MySQL X DevAPI connector for Node.js

answered Sep 15 '21 at 17:33

ruiquelhas

1,905
1
17
17

This worked perfectly except with the new update and to get the results right I added a parenthesis inline ` .where('customer_name like :customer_name && (account_status like :account_status1 || account_status like :account_status2 || account_status like :account_status3)') ` – nmsdev Oct 01 '21 at 16:27
Operator precedence rules are the typical ones, I guess. But what do you mean by “except with the new update”? Is the behavior broken somehow? – ruiquelhas Oct 03 '21 at 08:08
Rui, I guess to say that I updated the NPM package and then added the parenthesis it worked. Not sure if it was a javascript syntax problem or part of the update but it works great now. Have another question coming soon. – nmsdev Oct 08 '21 at 18:16

MYSQL XDEVAPI Multiple OR (II) Request

1 Answers1