Sails: using multiple 'or' clause ignores 2nd 'or' clause

[grd2345]

when writing a query using multiple 'or' clauses, it will return the records that match the first 'or' clause and ignore the 2nd or clause. This is the code that I have attempted but doesnt work.

Employee.find()
   .where({  
   or:[  
      {  
         locationId:employee.currentLocation.id
      },
      {  
         facilityId:employee.facilityId,
         userName:employee.userName,
         accountOwner:true
      }
   ]
},
{  
   or:[  
      {  
         employeeId:{  
            startsWith:params.search.value
         }
      },
      {  
         fullName:{  
            startsWith:params.search.value
         }
      },
      {  
         street:{  
            startsWith:params.search.value
         }
      },
      {  
         emailAddress:{  
            startsWith:params.search.value
         }
      },
      {  
         employeeType:{  
            startsWith:params.search.value
         }
      },
      {  
         status:{  
            startsWith:params.search.value
         }
      }
   ]
})     

[tjwebb]

You're sending your second or clause as a second argument into .where. That method accepts a single object.

It might help if you format your JSON since what you pasted is kind of hard to read.

[grd2345]

I tried this in many different ways including putting the 'or' clauses in seperate where clauses, in the same where clause, using an 'and' clause, but seems to not work. I event tried to put the two or clauses in the same json object as follows.

Employee.find()
   .where({  
   or:[  
      {  
         locationId:employee.currentLocation.id
      },
      {  
         facilityId:employee.facilityId,
         userName:employee.userName,
         accountOwner:true
      }
   ],
   or:[  
      {  
         employeeId:{  
            startsWith:params.search.value
         }
      },
      {  
         fullName:{  
            startsWith:params.search.value
         }
      },
      {  
         street:{  
            startsWith:params.search.value
         }
      },
      {  
         emailAddress:{  
            startsWith:params.search.value
         }
      },
      {  
         employeeType:{  
            startsWith:params.search.value
         }
      },
      {  
         status:{  
            startsWith:params.search.value
         }
      }
   ]
})

[particlebanana]

You can only have a single or key in the object. Imagine an object like this:

{
  name: 'bob',
  name: 'tom'
}

Which is what you end up with in the above object. Im not sure what your above query would look like in SQL but for this it might be easier to run two separate queries.

[grd2345]

if i were writing this in sql, it would be something like this.

select * from employees where (locationid = :locationid or (facilityId= :facilityid and userName = :username and accountowner = true)) and (employeeId like :searchvalue or fullname like :searchvalue or street like :searchvalue or emailAddress like :searchvalue or employeetype like :searchvalue or status like :searchvalue)

[RSSGergelyMunkacsy]

What If i want to use multiple OR in my query and ?
For example:

SELECT *
FROM "notification"
WHERE ("endDate" >= '2015-02-16'
       OR "endDate" IS NULL)
  AND ("startDate" <= '2015-02-16'
       OR "startDate" IS NULL)

In Waterline, this need to be look like:

Notification.find({
  or: [
    {
      "endDate": {
        '>=': '2015-02-16'
      },
    {
      "endDate": null
    }
  ],
  or: [
    {
      "startDate": {
        '>=': '2015-02-16'
      },
    }
    {
      "startDate": null
    }
  ]
});

But the generated SQL is wrong :(

[dmarcelino]

This looks like a new feature, but a new feature which would need to be implemented in the adapters. If we are talking SQL, waterline-sequel may be the right place to start.

[RWOverdijk]

or :[[], []] would make sense. Nesting could go as deep as wanted.

[sailsbot]

Thanks for posting, @grd2345. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

• review our contribution guide to make sure this submission meets our criteria (only _verified bugs_ with documented features, please; no questions, commentary, or bug reports about undocumented features or unofficial plugins)
• create a new issue with the latest information, including updated version details with error messages, failing tests, and a link back to the original issue. This allows GitHub to automatically create a back-reference for future visitors arriving from search engines.

Thanks so much for your help!

[pranaysonisoft]

How to write this query in waterline ?
select * from test_mas where
(perameter='test1' and type='asd') or
(perameter='test1' and ref_no='reg3' and order_no=2)

[RWOverdijk]

@pranaysonisoft This isn't a question for github issues. Either post it on stackoverflow, IRC, or on gitter.

But because I'm such a nice guy (ha), here's your answer:

TestMas.find({
  or: [
    {
      parameter: 'test1',
      type     : 'asd'
    },
    {
      parameter: 'test1',
      ref_no   : 'reg3',
      order_no : 2
    }
  ]
});

[ibtisamniche-vteams]

How to write this query in waterline ?
select * from test_mas where
(perameter='test1' or type='asd') and
(perameter='test2' or type='def' )

[mfpereira]

Hi @ibtisamniche-vteams , I want to do exactly the same thing! Did you find a solution?

[sromic]

You can write query: select * from test_mas where (perameter='test1' or type='asd') and (perameter='test2' or type='def' )

like following:

filter.and = [
        {or: [
          {perameter:'test1'},
          {type: 'asd}
        ]}, 
        {or: [
          {perameter:'test2'},
          {type: 'def'}
        ]}
      ];

Test.find(filter)...

[ellenaua]

This last query works for Mongo, but does not work for sails-mysql