Use And in Waterline

Question

This is my query using waterline

   var queryObject = {
        where: {
          or: [
            {firstName: {'contains': search}},
            {lastName: {'contains': search}},
            {email: {'contains': search}},

          ],
          and: [
            {authType:'google'},
            {dealershipcompany: 11}
          ]
        }
      }

yet adding 'and' return an error:

error: Sending 500 ("Server Error") response:
 Error (E_UNKNOWN) :: Encountered an unexpected error
: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[object Object],[object Object])' at line 1
    at Query.Sequence._packetToError (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:48:14)
    at Query.ErrorPacket (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Query.js:83:18)
    at Protocol._parsePacket (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:274:23)
    at Parser.write (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/Connection.js:96:28)
    at Socket.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:765:14)
    at Socket.emit (events.js:92:17)
    at emitReadable_ (_stream_readable.js:427:10)
    at emitReadable (_stream_readable.js:423:5)
    at readableAddChunk (_stream_readable.js:166:9)
    at Socket.Readable.push (_stream_readable.js:128:10)
    at TCP.onread (net.js:529:21)
    --------------------
    at Protocol._enqueue (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:135:48)
    at PoolConnection.query (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/Connection.js:201:25)
    at Array.async.auto.processParent [as 0] (/vagrant/sharpOne_app/node_modules/sails-mysql/lib/adapter.js:586:24)
    at /vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/async/lib/async.js:574:38
    at _arrayEach (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/async/lib/async.js:78:13)
    at Object.async.auto (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/async/lib/async.js:536:9)

    at Cursor.populateBuffers [as $populateBuffers] (/vagrant/sharpOne_app/node_modules/sails-mysql/lib/adapter.js:581:19)
    at Cursor.run (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/waterline-cursor/cursor/cursor.js:45:8)
    at runJoins (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/waterline-cursor/index.js:51:10)
    at __JOIN__ (/vagrant/sharpOne_app/node_modules/sails-mysql/lib/adapter.js:514:9)
    at afterwards (/vagrant/sharpOne_app/node_modules/sails-mysql/lib/connections/spawn.js:84:5)
    at /vagrant/sharpOne_app/node_modules/sails-mysql/lib/connections/spawn.js:40:7
    at Ping.onOperationComplete [as _callback] (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/Pool.js:99:5)
    at Ping.Sequence.end (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:96:24)
    at Ping.Sequence.OkPacket (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/sequences/Sequence.js:105:8)
    at Protocol._parsePacket (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:274:23)
    at Parser.write (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/vagrant/sharpOne_app/node_modules/sails-mysql/node_modules/mysql/lib/Connection.js:96:28)
    at Socket.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:765:14)
    at Socket.emit (events.js:92:17)

Details:  Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[object Object],[object Object])' at line 1

How do I use AND in waterline correctly, and it is obvious that sails-mysql support 'and', yet I don't know what causes the error, 'or' works perfectly without a problem and I can't chain where, because waterline only bring me the result of the last where.

I'm using Sails v10.4 and sails-mysql v0.11.2 and the query that I want to use it is the following :

SELECT * FROM user where (firstName LIKE '%search%' OR lastName LIKE '%search%' OR email LIKE '%search%') AND (authType='google' OR dealershipcompany = 11);

it returns the result where people have authType of google or with dealership company number 11

Answer 1

I reached here looking for between in Waterline. Useful for range queries.

The snippet below works. Should also work for other criteria modifiers.

userId: { '<=': 100, '>': 10 }

The following modifiers are available to use when building queries.

'<' / 'lessThan'
'<=' / 'lessThanOrEqual'
'>' / 'greaterThan'
'>=' / 'greaterThanOrEqual'
'!' / 'not'
'like'
'contains'
'startsWith'
'endsWith'

Answer 2

You don't have to explicitly specify and, as waterline will exactly* match what you include as a normal key pair (waterline docs)

This should do the trick:

   var queryObject = {
    where: {
      or: [
        {firstName: {'contains': search}},
        {lastName: {'contains': search}},
        {email: {'contains': search}},

      ],
      authType: 'google',
      dealershipcompany: 11
    }
  }

That will match records where authType is exactly 'google' and dealership company is exactly 11 and then match anything in or.

---

*I believe waterline queries are case-insensitive.

Edit: removed errant {}