I have a query that fetches results based on different id's, My query looks like this: I have shortened the query, still, there are two more WHERE In conditions.
SELECT DISTINCT restaurant.*,branch.* from restaurant,branch,restaurant_cuisines,restaurant_collections WHERE restaurant.restaurant_id=branch.restaurant_id AND restaurant_cuisines.cuisine_id IN (2,3) AND restaurant_collections.collection_id IN (); How to execute this query when there are no values in second WHERE in Condition? or How to handle this in application level(Java), I need to write around 28 if else conditions to check empty conditions. How to overcome this?
14 Answers
Answers 1
It all depends on your needs. Normally you should simply skip entire part.
As you use AND for all conditions I assume that by empty list you want to return all rows.(I guess that end users have some sort of multiple checkboxes or something like that).
If column is defined as NOT NULL you could generate your code in JAVA like this:
... AND restaurant_collections.collection_id IN(restaurant_collections.collection_id) -- which is always true and good query optimizer should skip entire part When you want specific values you use it as previous:
... AND restaurant_collections.collection_id IN (1,2,3) Answers 2
If all you want is for the query to work, you should be able to use:
SELECT DISTINCT restaurant.*,branch.* from restaurant,branch,restaurant_cuisines,restaurant_collections WHERE restaurant.restaurant_id=branch.restaurant_id AND restaurant_cuisines.cuisine_id IN (2,3) AND restaurant_collections.collection_id IN (NULL); But this will always return no results.
If you want something a little more flexible, or if you are expecting all results if collection_id does not have any values passed to it, I recommend changing from implicit to explicit joins.
Right now you're using an implicit join syntax:
SELECT A.*, B.*, C.* FROM A,B,C WHERE A.j=B.j AND A.j=C.j The problem with this is that if C.j is empty, even if you fix your syntax, your query will return nothing.
Explicit joins allow for a more nuanced approach:
SELECT A.*, B.*, C.* FROM A INNER JOIN B ON A.j=B.j AND B.v IN (1,2) LEFT JOIN C ON A.j=C.j AND C.v IN (NULL); When you code, instead of passing an empty string, pass a null value (or, if you're not using prepared statements, pass a string containing NULL). Otherwise, pass the values as a list.
If you need the rows to be ignored only when there are no values, you can do something like this instead:
SELECT A.*, B.*, C.* FROM A INNER JOIN B ON A.j=B.j AND B.v IN (1,2) LEFT JOIN C ON A.j=C.j WHERE (COALESCE(<val>) IS NULL OR C.v IN (<val>)); When you add the LEFT JOIN and then refer to it in the WHERE clause, you can easily check if the C.v contains the list of values, if the value passed is not null by using the COALESCE function.
- Scenario 1, there are values in , return only those values.
- Scenario 2, there are no values in and =NULL. Return everything.
In summary, LEFT joins are very flexible and allow you to go back later and optionally enforce a strict INNER JOIN. They should not be used when an INNER JOIN does the job, but most definitely have their uses, such as here.
A note on LEFT vs INNER:
- If you want the query to execute even if the list is empty, use
LEFT JOIN. - If you want the query to return results only if there are values in the list, then use
INNER JOIN.
Answers 3
This is an app task, not an SQL task.
When I have an API with several optional inputs, I construct the WHERE dynamically. Something like (sorry, this is in PHP, not Java):
$wheres = array(); // declare empty array if (user entered x) $wheres[] = "x = '$x'; // conditionally append item to array if (user entered y) $wheres[] = "y = '$y'; if (user entered a non-empty zlist) $wheres[] = "z IN (" . implode(',', $zlist), ")"; $where_clause = empty($wheres) ? '' : 'WHERE ' . implode(' AND ', $wheres); $SQL = "SELECT ... $where_clause ..."; Notes:
- If there is no entry for
x, then that part of theWHEREis omitted. - If none of the parameters are provided, I even get rid of the
WHEREclause entirely. - Handling
INis a little messier, but still not bad. - Handling a date range, and other variations, are also possible.
Answers 4
I usually solve this kind of conditions by having additional boolean variable. In your example, I'd have an additional boolean param named searchByCollectionIds so that it would like something like:
SELECT DISTINCT restaurant.*,branch.* from restaurant,branch,restaurant_cuisines,restaurant_collections WHERE restaurant.restaurant_id=branch.restaurant_id AND restaurant_cuisines.cuisine_id IN (2,3) AND (true = :searchByCollectionIds or restaurant_collections.collection_id IN (:collectionIds); When :collectionIds is empty, you'd put -1 into it, :searchByCollectionIds set to false and your query would be translated into:
SELECT DISTINCT restaurant.*,branch.* from restaurant,branch,restaurant_cuisines,restaurant_collections WHERE restaurant.restaurant_id=branch.restaurant_id AND restaurant_cuisines.cuisine_id IN (2,3) AND (true = false or restaurant_collections.collection_id IN (-1); Since first condition in the bracket does not meet, it will NOT check second one. So you'll get everything.
Answers 5
The SQL has no really sense if you don't relate "restaurant_cuisines" and "restaurant_collections" with the other items. If these two tables are not related with the other ones then the SQL is a cartesian product of this tables.
SELECT DISTINCT restaurant.*,branch.* from restaurant,branch,restaurant_cuisines,restaurant_collections WHERE restaurant.restaurant_id=branch.restaurant_id AND restaurant_cuisines.cuisine_id IN (2,3) AND restaurant_collections.collection_id IN (); For example y we have the tables A and B with two columns(C1 and C2)
Table values (A & B) C1 | C2 ------|------ 1 | 1 2 | 2 Making the SQL
SELECT * FROM C1, C2 selects all the combinations between A & B keeping the "holes" with a null value.
A.C1 | A.C2 | B.C1 | B.C2 ------|------|------|------ 1 | 1 | 1 | 1 2 | 2 | 1 | 1 1 | 1 | 2 | 2 2 | 2 | 2 | 2 ... First, relate the tables with the desired fields. Then make the relations using a proper JOIN (LEFT JOIN or OUTER JOIN) if you make this correct the rows with out relation with the second part of the JOIN will be null (like an empty collection) and you could select all rows with null value on it (ej: restaurant_collections.collection_id IS NULL)
Example: ( Requires to fill the missing relations)
SELECT DISTINCT restaurant.*, branch.* FROM restaurant r LEFT JOIN branch b ON r.restaurant_id = b.restaurant_id LEFT JOIN restaurant_cuisines rcu ON ???? LEFT JOIN restaurant_collections rco ON ???? WHERE rcu.cuisine_id IN (2,3) AND rco IS NULL Answers 6
Assuming that you have a variable between those brackets you could do this little trick.
DECLARE @Variable AS VARCHAR(250) SELECT DISTINCT restaurant.*,branch.* FROM restaurant, branch, restaurant_cuisines, restaurant_collections WHERE restaurant.restaurant_id=branch.restaurant_id AND restaurant_cuisines.cuisine_id IN (2,3) AND (restaurant_collections.collection_id IN (@VARIABLE) OR @VARIABLE IS NULL); Essentially it tests to see if @VARIABLE is NULL and does not include the parameter.
Answers 7
How to handle this in application level(Java), I need to write around 28 if else conditions to check empty conditions.
Change your query to a DYNAMIC QUERY like so
StringBuffer sql = "SELECT DISTINCT restaurant.*,branch.*"+ "from restaurant,branch,restaurant_cuisines,restaurant_collections"+ " WHERE 1=1 "; The above will always execute with all the values(basically saying its true), now based on your if/else conditions you just have to append your "AND condition" is not empty like so
How to execute this query when there are no values in second WHERE in Condition?
if(restaurant_cuisines.cuisine_id != null) { sql.append("AND restaurant_cuisines.cuisine_id IN (2,3)"); } So the above will only be added if cuisine_id is not null. What this does is makes your query run only based on the conditions required and not add unnecessary additional conditions which might alter your result or face the issues that you are current suffering with.
Hope this helps :)
Answers 8
I'm a SQL Server developer so I would do it like this:
/* @collection_id have to be a string with all the id's separated by comma like '1,2,3,4' */ CREATE PROCEDURE [dbo].[getRestaurant] @collection_id VARCHAR(MAX) AS DECLARE @query nVARHCAR(MAX)='' SET @query= ' SELECT DISTINCT restaurant.*,branch.* FROM restaurant JOIN branch ON restaurant.restaurant_id=branch.restaurant_id JOIN restaurant_cuisines ON restaurant.restaurant_id = restaurant_cuisines.restaurant_id JOIN restaurant_collections WHERE restaurant_cuisines.cuisine_id IN (2,3) AND restaurant_collections.collection_id IN ('+@collection_id+')' EXEC sp_executesql @query Then just call this SP from your Java Code
Answers 9
You can try this:
SELECT DISTINCT restaurant.*,branch.* from restaurant,branch,restaurant_cuisines,restaurant_collections WHERE restaurant.restaurant_id=branch.restaurant_id AND restaurant_cuisines.cuisine_id IN (2,3) AND restaurant_collections.collection_id IN (true); Answers 10
The condition IN () will throw syntax exception in MySQL.
You can remove the AND clause if the list is detected as empty. Parse the query and if () is detected, strip out the AND condition.
Another approach is to always have a condition there which will not meet any criteria in the data. For example, AND restaurant_collections.collection_id IN (-1) and then keep appending to the list. For example, AND restaurant_collections.collection_id IN (-1,10,12). This way, there will never be an empty list situation.
Answers 11
First of all next time try to copy the whole code generating that query, by the way you said it seams to me you are getting that from several input fields and merging then into the IN clause
You should create a Set to store these fields before inflate then into the IN
Set<String> collectionIds = new Hashset<>(); collectionIds.add(field1); collectionIds.add(field2); .... collectionIds.remove(null); then you can just check if the id set is empty before create the query and handle it the way you want (simple ignore the query, or build it without this statement)
By the way you should consider using Hibernate or other persistence framework, it is strongly not adivsed to build queries in java or any other language by concating strings. It leads to sql injection
Answers 12
If your database support it, you could use ANY (as in following example with jdbi3)
String sql = "SELECT * FROM table where collection_id = ANY (:ids);" List<Long>ids = ...; org.jdbi.v3.core.Handle db = getHandle(); db.createQuery(sql).bind("ids", ids.toArray(new Long[ids.size()]))
ANY works also with empty array (at least in postgres).
Answers 13
You come with the right direction, write if/else to strip the empty array condition.
But you don't need to write that much (~28 functions), just abstract them to a single function.
StringBuilder baseQuery = new StringBuilder("SELECT DISTINCT restaurant.*,branch.* from restaurant,branch,restaurant_cuisines,restaurant_collections WHERE restaurant.restaurant_id=branch.restaurant_id "); public void addInCondition(String field, Collection<Integer> values) { if (values != null && !values.isEmpty()) { // Append your values baseQuery.append(field + " IN (" + String.join(values, ",") + ") "); } } Hope you get the idea.
0 comments:
Post a Comment