Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

Missing OR operator #96

Open
munro opened this Issue · 21 comments

9 participants

@munro

Currently it's not possible to leverage the OR operator in SQL or MongoDB.

It's possble to get by without the OR operator by doing JOINing at the application level. So WHERE name = 'munro' OR name = 'github' would could be split up into to WHERE name = 'munro' joined with WHERE name = 'github'

The problem is when one wants to OR on different fields, duplicate rows may be returned. So to correctly split up something like WHERE name = 'munro' OR language = 'js' at the application level, one would have to build two queries like this: WHERE name = 'munro' AND language != 'js' joined with WHERE name != 'munro' AND language = 'js'. And that solution is just as bad as concatenating SQL strings. :(

The new syntax could be inspired by how MongoDB currently does OR operations:
http://www.mongodb.org/display/DOCS/Advanced+Queries#AdvancedQueries-%24or

I could definitely see this operator being implemented in Redis, not quite sure about Neo4j. Let me know your thoughts about this, because I'm itching to fork!

@anatoliychakkaev
Collaborator
@munro

Awesome! There's also the $and syntax which is realized is necessary. So, one of the things I find an ORM useful for is generating queries on a page where a user has many options to filter data... Right now I have a bunch of if statements which concatenate SQL. I realized they're all joined with AND, and but some have sub-OR statements...

Here's an example filter using $and and $or statements:

{$and: [
    {$or: [{type: 'person', name: 'ryan'}, {type: 'service', name: 'github'}]},
    {$or: [{frequency: 'daily'}, {frequency: 'monthly', day: (new Date()).getDate()}]}
]}

I was just looking for your input on this—MongoDB, Redis, SQL, Memory (which appears to not support any advanced filter options like gt, lt, etc...) should all support this.

@octavioturra

It could be an $in or $nin operator eg.

{ 
   "name" :   {
        $in : [ "john", "ryian" ], 
        $nin : ["bob"]
   }
}

also, for strings, an $re operator,

{
    "name" : { $re : /.*\w.*/g }
}
@anatoliychakkaev
Collaborator
@octavioturra

It's useful to prevent misusing. It's an object and, if one property use the "nin" as name or something like this it can causes some conflicts.

@octavioturra

Other option is to use the filter concept like:

...{where : {"name", function(name){
        if(name=="test"){
            return name;
        }
    }
}}
@absynce

@making3 did this for mysql using mongo-esque style without the $ to be consist.

jugglingdb/mysql-adapter#42

@tolgaek

Is there any progress on this or any decision on what to use? Is it def. the mongoDB syntax?

@dgsan

I think it's okay skip the $. I'd just go ahead and do it, make a pull request. If Anatoliy has issues with the name he'll either tell you or change it himself when he merges. Probably.

@s0s0s0

Is it that hard to implement OR operation? the issue opened a year ago.

@tolgaek

@s0s0s0 I think the issue is having to implement it for all the adapters.. I'm going to be doing this for mysql adapter

@dgsan

I think MySQL adapter has an OR syntax now, though obviously if core adopts this, you'll be welcome to change it to match core.

@tolgaek
@dgsan

Mostly it just takes someone to implement and make a pull with tests and have Anatoliy be okay with it. The tests in core will force the adapters to match to pass unit tests.

@tolgaek

Sounds good.. I'm working on the mysql one, once completed I will write the tests for the core project

@tolgaek

@dgsan How does updating the tests in this project enforce it in other adapters.. It seems like every adapter has their own tests

@dgsan

So it's true that every adapter has its own tests for adapter specific features, but every adapter also runs all the tests in core for share features, such as this ones: https://github.com/1602/jugglingdb/blob/master/test/basic-querying.test.js

If you added OR functionality to JDB core, then you'd make a test like the one linked, or add to a test, with cases that illustrated the "OR" functionality. Hopefully you'd also make at least one adapter pass the tests, too.

@origin1tech

Where is this at fellas? Desperately need an $or much like mongodb. I don't see any support for this or am I missing something. if it doesn't exist HOW can I help? we've roughed out a framework (https://github.com/chazelton/xipframe) built on JDB so I'm serious about helping. thx.

@dgsan

Mysql adapter can handle OR as it stands, but discussions on getting an OR syntax into core are ongoing. See tolgaek's pull request.

@origin1tech
@Anachron

This is still open? Where is the dicussion?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.