CODEWITHSUNDEEP
javascriptdatabasesqlitecordovacross-platform
aB9
aB9

Reputation: 592

SQLite query to perform search and multiple filters in JavaScript

I have a set of data (table) as shown below.

+---------+---------+---------+-----------+
|  Name   | Surname | Country |  Status   |
+---------+---------+---------+-----------+
| Ryan    | Gosling | U.S.A.  | Citizen   |
| Raymond | Green   | Germany | Citizen   |
| Andrew  | Hollow  | U.S.A.  | Immigrant |
| Sam     | Giggs   | U.S.A.  | Citizen   |
+---------+---------+---------+-----------+

A user can perform following tasks,

  1. Search (name or surname) no filters (country and status)
  2. Search (name or surname) + one or both the filters (country and status)
  3. No search (name or surname) but one or both the filters (country and status)

What queries should I write to solve this issue?

Edit: As Rob has refused to do my homework 😜, let me explain what I have done! It's not ethical but manages to work.

If a user searches through the table using name or surname as a keyword I bring those results using an OR query. And then run that result through the selected filter arrays (if any) in a for loop and show the final result by removing duplicate entries. If there is no search performed and just a set of filters applied then I bring entire table and run through for loop as described above. In case only search is performed then I bring the strip down result using OR query as said above.

Upvotes: 1

Views: 2154

Answers (1)

Titus
Titus

Reputation: 22484

You're approaching this the wrong way, there is no filter or search in sqlite, you have the WHERE clause and some logical operators, you can combine those to do what you want. Here is an example for 2: 

SELECT * FROM [tableName] WHERE (Name = "something" OR Surname = "somethingElse") AND (Country = "someContry" OR Status = "someStatus");

You just need to figure out the logic, the important part is to delimit each part using parenthesis.

Upvotes: 2

Related Questions