Syntax for AND / OR Query

Ask the community
,
1

Before running the .find() in below code, I would like to run multiple AND and OR conditions to query against the database.
e.g
(category = “HUM”) AND (keyword LIKE ”%ERICA%” OR keyword LIKE “%SOPHIA%”)

In below example I am unsure how to put the AND/OR conditions:

selectedCategory=“HUM”
keyword1=“ERICA”
keyword2=“SOPHIA”

query = wixData.query(‘DATABASENAME’).eq(‘category’, selectedCategory);
query = query.or(wixData.query(‘DATABASENAME’).contains(‘keywords’, keyword1));
query = query.or(wixData.query(‘DATABASENAME’).contains(‘keywords’, keyword2));
query.find()
.then( (results) => {
$w(“#repeater1”).data = results.items;
} )
.catch( (err) => {
let errorMsg = err;
} );

Any help will be appreciated.

1 Like
2

Hi Yashika,
you need to use two queries and OR them.
the first one will be:

query1 = wixData.query('DATABASENAME')
    .eq('category', selectedCategory)
    .contains('keywords', keyword1);

the second one will be:

query2 = wixData.query('DATABASENAME')
    .eq('category', selectedCategory)
    .contains('keywords', keyword2);

and then OR them and run:

let full_query = query1.or(query2);
full_query.find().then(...)

in SQL it would look like this:
((category = “HUM”) AND (keyword LIKE ”%ERICA%”))
OR
((category = “HUM”) AND (keyword LIKE “%SOPHIA%”))

hope this helps!

3

Thanks for the response.

Can the query be a dynamic array as the number of search words may vary at different times

e.g

let keyword = enteredkeyword.split(‘;’);
Below if keyword count is 2:
selectedCategory=“HUM” AND (keyword[1]=“ERICA” OR keyword[2]=“SOPHIA”)

Below if keyword count is 3:
selectedCategory=“HUM” AND (keyword[1]=“ERICA” OR keyword[2]=“SOPHIA” OR keyword[3]=“YASHIKA”)

4

examples of entered keyword:

enteredkeyword = “ERICA;SOPHIA”

enteredkeyword = “ERICA;SOPHIA;YASHIKA”

5

In simple words is there a way to define the wixData.query object as a dynamic array:

e.g
let query(X) = wixData.query(‘DATABASENAME’) – Where X is a variable i.e the size of the array

query(1) = wixData.query(‘DATABASENAME’)
.eq(‘category’, selectedCategory)
.contains(‘keywords’, keyword1);

query(2) = wixData.query(‘DATABASENAME’)
.eq(‘category’, selectedCategory)
.contains(‘keywords’, keyword2);

query(X) = wixData.query(‘DATABASENAME’)
.eq(‘category’, selectedCategory)
.contains(‘keywords’, keywordX);

let full_query = query1.or(query2).or(queryX); full_query.find().then(…)

6

Hey Yashika,

Of course you can do that. It’s just a matter of some javascript.

Look at this utility functions that we’re gonna use:

// a function that given a keyword returns a wixData query for that keyword
function queryByKeyword(keyword) {
  return wixData.query('DATABASENAME')
    .eq('category', 'CATEGORY')
    .contains('keywords', keyword)
}

// a function that given an array of queries returns just one query
// which merges all given queries using OR
function joinQueriesWithOr(queries) {
  return queries.reduce((joinedQuery, nextQuery) =>
    joinedQuery.or(nextQuery)
  )
}

And an example for how to use them:

// let's say that this is the input value we're dealing with
const inputValue = 'a;b;c;d'; 

// convert the input value to an array of keywords
const keywords = inputValue.split(';') 

// convert the keywords array to an array of queries (one for each keyword)
const allQueries = keywords.map(keyword => queryByKeyword(keyword))

// join the different queries into one query using OR
const joinedQuery = joinQueriesWithOr(allQueries)

Hope it’s clear enough.
Good luck!

7

Wow!! This worked like a charm. The only minor change I made was renamed “createQuery” to “queryByKeyword” in below code:

const allQueries = keywords.map(keyword => createQuery(keyword))

Thank You Dor!

8

You are right, thanks. I fixed it in the above code.
Glad i could help.

9

Hi,
I’m a beginner on Wix and I did not understand how to use the “AND” conditon.

For example I have to write the following query,from MyTable with WHERE condition:

" ((( A = ‘TOP’ ) OR ( A = ‘TAP’ )) AND ( B >= 2 )); "

“MyTable” is it the collection name, is it dataset name ( #dataset1 ), is it table( #table1 ) name or what?

The AND condition how do you use it?

Thanks and sorry for my english.

10

Hello Claudio,

We do not currently have a specific “and” condition.
Instead, you would build a query and apply additional conditions like ‘eq’, ‘gt’, etc… to it.

11

Ok thanks Dor you are right.
I followed the instructions of Ziv Shalev and my condition works now.

thank you all

12

Hi Dor,
can you give me an example query code using something like Claudio’s example (((A) or (B)) and (C))? Thanks!
cheers n’ blessings,
G

13

Does category stand for field name?