OR in filters

[GoogleCodeExporter]

Right now I can only select one value in the filters.
Example:
COL_NO='SF'

I would like to be able to do this;
COL_NO IN ('SF','FX','LL')
or 
COL_NO='SF' OR COL_NO='FX' OR COL_NO='LL'

--------------------------------------------------------------------------
NOTE: Use this form to suggest new features for Google Fusion Tables
website or API.  For bugs, use the "Defect report" template.

Your "star" vote on requests in this issue tracker helps the Fusion Tables
team prioritize work on the most useful features.

By submitting this form you agree to the Google Terms of Service
(http://www.google.com/accounts/TOS) and the following additional term:
In addition to the Content license you grant to Google in Section 11 of the
Google Terms of Service, you agree that Google may, at its option, use your
Content to provide support for, or to develop, correct or improve Google's
current or future products and services, without any compensation to you.

Thanks for the suggestion!
--------------------------------------------------------------------------

Original issue reported on code.google.com by gfil...@gmail.com on 26 Oct 2010 at 7:45

[GoogleCodeExporter]

6 votes in prior feature request system for this same topic: 

Add 'OR' to the list of logical operators allowed in WHERE statement 
(FusionTablesLayer.setQuery).

The typical case is when there are points of different types in the table (e.g. 
shops, schools etc.) and it's needed to display certain types only 

Original comment by rshap...@google.com on 5 Nov 2010 at 11:18

• Changed state: Accepted

[GoogleCodeExporter]

We need this to clearly expose our data to the public.

Original comment by SFGOV...@gmail.com on 24 Dec 2010 at 6:38

[GoogleCodeExporter]

This is a bit of bookkeeping for all our feature requests - please excuse the 
extra email. 

Original comment by rshap...@google.com on 13 Jan 2011 at 9:21

• Changed state: Acknowledged

[GoogleCodeExporter]

Any updates on possible progress?

Original comment by maartenv...@gmail.com on 13 Jan 2011 at 9:39

[GoogleCodeExporter]

Another for OR here!

Original comment by mercy.re...@gmail.com on 20 Jan 2011 at 1:47

[GoogleCodeExporter]

Ditto...  I am not sure if I voted before, but count me in.  :-)

Thanks Google, BTW.  I don't get a chance to thank you enough for the great 
products.  Can't wait to completely ditch Windows using Chrome OS.  ;)

Thanks,

Dave

Original comment by DaveLala...@gmail.com on 20 Jan 2011 at 3:13

[GoogleCodeExporter]

I agree. Seems like a standard requirement.

Original comment by sid.mur...@gmail.com on 10 Feb 2011 at 10:27

[GoogleCodeExporter]

+ another one

Original comment by phg...@gmail.com on 26 Feb 2011 at 11:28

[GoogleCodeExporter]

... a short workaround is in some cases to use not equals instead.

Original comment by steffen....@gmail.com on 1 Mar 2011 at 6:06

[GoogleCodeExporter]

>>... a short workaround is in some cases to use not equals instead.
yeh, You're totally right. there is a standart conversion: id = 1 OR id = 2 OR 
id = 3 is equal to 
!(id !=1 AND id != 2 AND id != 3).
but here - 
http://code.google.com/intl/ru/apis/fusiontables/docs/developers_reference.html#
Select I don see anything about possibilyty to make !(...) or NOT( .. )
Irealy hope I'm wrong and there is a way.
any ideas?

Original comment by phg...@gmail.com on 1 Mar 2011 at 6:14

[GoogleCodeExporter]

... a short workaround is in some cases to use not equals instead.

... it is not documented, but it is there : ) The trick is to use the Fusion 
Table via you browser and build your query visually... than take a look at the 
generated 'Get embeddable link'... done.

This works: 
https://www.google.com/fusiontables/api/query?sql=SELECT+ROWID+FROM+<YOUR-TABLE-
ID>+where+<YOUR-COLUMN>+not+equal+to+'<YOUR-LIMITING-STRING>'

Original comment by steffen....@gmail.com on 1 Mar 2011 at 6:30

[GoogleCodeExporter]

it's in the docs under column_condition:
http://code.google.com/apis/fusiontables/docs/developers_reference.html#Select

"contains ignoring case" is also very powerful, gives you some lightweight full 
text search...

Original comment by bfl...@spatialdatalogic.com on 1 Mar 2011 at 6:36

[GoogleCodeExporter]

The not equals solution quickly begins to lose value as the number of unique 
values in your column increases. With > 20 uniques and say only two matches in 
that set, that is a very big query.  

A practical example: I have a table listing 100 gas stations in 50 cities in 30 
states, I want to find all gas stations in three of those states to plan my 
road trip.  what would be a usable query without using OR? It does not seem 27 
'and not equal to' would be ideal.

Original comment by w...@adwencreative.com on 1 Mar 2011 at 6:41

[GoogleCodeExporter]

>>>It does not seem 27 'and not equal to' would be ideal.
NO! no, not 27!
if You want only from 3 cities - You'll need something like this - NOT(city_id 
!= 1 AND city_id != 2 AND city_id != 3) to get stations from city # 1,2 and 3 - 
just use phpMyAdmin f.e. to check.

Original comment by phg...@gmail.com on 1 Mar 2011 at 6:48

[GoogleCodeExporter]

>>> ...it is not documented, but it is there : ) ...
and what about another part of condition - is there a possibility to make NOT() 
on several conditions  - something like that - "NOT(city_id != 1 AND city_id != 
2 AND city_id != 3)"
if yes - so we have a desicion, if no - we have to wait :(

Original comment by phg...@gmail.com on 1 Mar 2011 at 6:51

[GoogleCodeExporter]

Great potential all this stuff. Would like to see the proposed OR specified to 
allow any columns to be accessed, just as the fusion table AND currently does 
(as per normal SQL), as opposed to the previous examples, where only the one 
column is part of the condition. I assume it would be specified as such but 
assumptions can go badly wrong!! 

eg say with a table that has firstName and lastName columns:

.. WHERE firstName contains ignoring case $searchString OR lastName contains 
ignoring case $searchString

Original comment by bigya...@gmail.com on 26 Mar 2011 at 10:29

[GoogleCodeExporter]

Can we get the priority for this kicked up with the new 'won't fix' decision 
for #332 (expanded map layers) ?  

thanks!

Original comment by BSUJoeGa...@gmail.com on 16 May 2011 at 5:09

[GoogleCodeExporter]

Yea - let's get this one fixed google staff, please.

Original comment by sid.mur...@gmail.com on 16 May 2011 at 5:16

[GoogleCodeExporter]

I actually got a question in at the google io fireside chat and essentially 
they just acknowledged that it's on the issue list and recommended that you use 
layers (#332) and dynamic styles.  So 5 layers and one layer can have 5 dynamic 
styles.   It won't work for my use case but it may be enough for others.  

http://www.youtube.com/watch?v=Qo8g4x2OkPs&feature=player_embedded#t=2994s

Original comment by todd.fea...@gmail.com on 16 May 2011 at 5:31

[GoogleCodeExporter]

Definitely need an OR :)

Original comment by banuy...@gmail.com on 8 Jun 2011 at 1:16

[GoogleCodeExporter]

Here's partial solution based on Yahoo Pipes that might help some people: 

http://pipes.yahoo.com/pipes/pipe.info?_id=0c7ac7dd12223f4aef547dc671438506

You pass in your Fusion query as a parameter like this:

sql=SELECT * FROM 317391 where Source contains ('Tokyo'!'Cairo'!'Stockholm') 
and Tags contains 'UK'

and it expands out the brackets into multiple Fusion queries, which it executes 
and merges. 

Eg 

http://pipes.yahoo.com/pipes/pipe.run?_id=0c7ac7dd12223f4aef547dc671438506&_rend
er=csv&sql=SELECT+%2A+FROM+317391+where+Source+contains+%28%27Tokyo%27%21%27Cair
o%27%21%27Stockholm%27%29+and+Tags+contains+%27UK%27

You can also do something like this:

sql=SELECT * FROM 317391 where (Source contains 'Tokyo' ! Address contains 
'Japan') and Tags contains 'UK'

Note you can only expand brackets once.

-Greg.

Original comment by gregory....@gmail.com on 23 Jun 2011 at 1:57

[GoogleCodeExporter]

Definitely a vote FOR this feature. 

We explored various options: regular expressions, bitwise operators, "IN" 
operator, none of which are supported in the SQL API. It all comes back to that 
basic feature, "OR" conditions. 

Original comment by jus...@hellodesign.com on 25 Jun 2011 at 12:27

[GoogleCodeExporter]

Thanks everyone for the feature request!  

This has been so popular, and I am super-stoked to announce that we now have IN 
support in the API. And we're rolling it out in the UI shortly.  

It works just as the original request wrote.  Instead of a single equality, you 
can provide a comma-separated list of values to match that filter condition, 
such as: 

COL_NO IN ('SF','FX','LL')

So if the value in that column is IN the list, it's IN your search results too!

Bring your questions & feedback to the Fusion Tables user group or our direct 
feedback email, googletables-feedback @ google.com.  

Thanks!

Original comment by rshap...@google.com on 7 Jul 2011 at 1:19

• Changed state: Available

[GoogleCodeExporter]

Nice! Really terrific, useful addition.

Original comment by anthony....@gmail.com on 7 Jul 2011 at 1:24

[GoogleCodeExporter]

Issue 619 has been merged into this issue.

Original comment by rshap...@google.com on 7 Jul 2011 at 1:53

[GoogleCodeExporter]

Wo-ho! thanks, guys!

Original comment by phg...@gmail.com on 7 Jul 2011 at 6:10

[GoogleCodeExporter]

Is there a limitation to the number of comma separated values passed in IN. 
passing more than around 116 does not return me anything passing 100 returns me 
data on map.

Original comment by poojas...@gmail.com on 21 Jul 2011 at 3:02

[GoogleCodeExporter]

There isn't a limit to the number of items listed in the IN clause. 

However, there is a limit to the URL length that can be used with the Maps API. 
See: 
http://support.microsoft.com/kb/208427

Thanks, 

-Rebecca

Original comment by rshap...@google.com on 21 Jul 2011 at 10:21

[GoogleCodeExporter]

Thanks Rebecca.
Is there any way where I can add dynamic data to Google Fusion Table. That
way if I add 300 zipcodes and do select * from table, that should display
all the zipcodes.

Please suggest.

Original comment by poojas...@gmail.com on 21 Jul 2011 at 10:25

[GoogleCodeExporter]

Issue 681 has been merged into this issue.

Original comment by rshap...@google.com on 21 Jul 2011 at 10:27

[GoogleCodeExporter]

Reopening because there is still a need for OR.

Original comment by kbris...@google.com on 28 Jul 2011 at 9:07

• Changed state: Accepted
• Added labels: Priority-Low
• Removed labels: Priority-Medium

[GoogleCodeExporter]

Need to  describe what is still needed in OR in a separate feature request. 

Original comment by rshap...@google.com on 3 Aug 2011 at 3:21

• Changed state: Available

[GoogleCodeExporter]

I appreciate the addition of the IN, but I still desperately need an OR.

I have a map that user's can add or remove different categories, such as bars, 
restaurants, theaters, shops, etc. If a user selects bars, restaurants and 
shops, I want to show all three. Right now, to display all three, I have to 
query "WHERE 'cat' IN ('bars', 'restaurants', 'shops')".

The problem is many businesses overlap, such as many bars/restaurants or places 
like Hard Rock Cafe which is bars/restaurants/shops, or the Rainforest Cafe 
which is restaurants/shops. As it is, I have to choose one category for each 
business.

I would like the OR command so, after restructuring my table, I could query 
"WHERE 'bars'=1 OR 'restaurants'=1 OR 'shops'=1". This will allow me to display 
all the listings with at least a bar, or restaurant, or a shop.

Original comment by jer...@pfgp.com on 27 Aug 2011 at 1:18

[GoogleCodeExporter]

New feature request opened for tracking requests for OR not served by "IN":

http://code.google.com/p/fusion-tables/issues/detail?id=709

Please add your star vote and use cases there. 

Thank you, 

-Rebecca

Original comment by rshap...@google.com on 29 Aug 2011 at 10:28

[GoogleCodeExporter]

+ another

Original comment by jesus.ba...@gmail.com on 13 Sep 2012 at 4:48

[GoogleCodeExporter]

IN operator does not work for the ROWID.
In my app I have all ids I want to get and I still need to get one by one since.
SELECT ROWID,column1 FROM table WHERE ROWID in (201,202,...) doesn't work :(

Original comment by hgabreu on 11 Apr 2013 at 11:48

[GoogleCodeExporter]

The new V1.0 API does not implement OR logical operator either. Is this gonna 
be added any time soon?

Original comment by e...@google.com on 31 Jan 2014 at 9:01

[GoogleCodeExporter]

We are not currently working on this, so no, it's not likely to appear any time 
soon.

Original comment by rmcch...@google.com on 31 Jan 2014 at 11:17

• Changed state: Acknowledged

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Issue 1449 has been merged into this issue.

Original comment by kad...@google.com on 9 Feb 2015 at 3:08