Sql query with optional fields for search web application
Not an SQL expert:
I am currently implementing a search web application. The user will send GET request
to a specific one Endpoint
.
Endpoint
will accept a set URL Params
, and the request can come with optional fields. For example, 1, 2 or 3 fields.
My database table for USER
looks like this.
+--------------+---------+------+-----+---------+----------------+
| id | firstName | lastName | email | zip | phone |
+--------------+---------+------+-----+---------+----------------+
The web application will now receive GET request
with Phone
or Zip
or Email
.
I have two solutions for this, but both look bad:
Solution 1:
-
I have several
SQL Queries
andExecute
according to url parameters.Select * from User where phone=1111111111 and zip=12345 and email=test@email.com;
Select * from User where phone=1111111111 and zip=12345;
... ...
And so on ........ I end up with a lot of requests and a bad implementation. It will also be poor to maintain.
Solution 2:
Another solution I am thinking of is to have a method that will build a SQL query based on the URLs I receive.
Example:
buildSQLQuery(phone,zip,email){
String sql = "Select * from User where "
if(phone!=null && email!=null && zip!=null ){
sql = sql + "phone = " + phone + " and zip = " + zip + " and email = " + email;
}else if (phone!=null && email!=null && zip==null){
sql = sql + "phone = " + phone + " and email = " + email;
}
.......
......
And so on have all conditions and build that particular query.
}
I don't like both of these solutions.
Is there a way to write a Single SQL query that will handle all of the above conditions.
Something like, if the value of the URL parameter is equal NULL
then it shouldn't affect the request and I will get the expected results.
As in my case, the optional values that are not included are set to NULL
.
Is it possible to implement something like this?
Select * from User where (if notNull (phone))(phone = phone ) and (if notNull (email))(email = email ) and (if notNull (zip))(zip = zip )
If any of the above is null, then don't use that part in where Condition
.
Also I will always have one field, so there will be no case where all values are zero.
I am using this web application in Java and Spring MVC. If someone can guide me in the right direction.
Thank.
source to share
I think there is another possible solution like:
String sql = "Select * from User where 1=1 "
if(phone!=null){
sql += " and phone = " + phone ;}
if(email!=null){
sql += " and email = " + email ;}
if(zip!=null){
sql += " and zip = " + zip ;}
OR you can try one query:
select * from User
where (@phone is null OR phone = @phone) AND (@email is null OR email = @email) AND (@zip is null OR zip = @zip)
source to share
You can do the following:
SELECT * FROM User where(
IF($phone != 0, IF(phone = $phone,1,0), 0) AND
IF($email != 0, IF(email = $email,1,0),0) AND
IF($zip != 0, IF(zip = $zip,1,0),0)
)
intended for PHP, you can change the syntax, maybe this query doesn't do the job completely, but if you provide a fiddle, I'll modify it to give the correct result.
source to share