multiple where conditions

[astroanu]

Is it possible to make multiple where conditions?

var q = db.from('people').where('age', '=', 25);

i want to get people who are age 25 and who live in state = ny

[yathit]

Definitely.checkout out http://dev.yathit.com/ydn-db/nosql-query.html

Calling where multiple times should works also, although implementation is unfinish.

[astroanu]

Thanks. i didn't dig the entire code. if the multiple where is possible by passing an array it would be cool? (may be this is a bad idea)

var q = db.from('people').where([['age', '=', 25],['state','=','ny']]);

[yathit]

Can you reasonable argue why it is better than where('age', '=', 25).where('state','=','ny') ?

[paolooo]

+1

[yathit]

Which file are you using? You need cur module.

On Fri, Aug 22, 2014 at 7:40 PM, Erez Pilosof [email protected]
wrote:

ydn.db.IndexValueIterator is undefined... what am I missing ?

—
Reply to this email directly or view it on GitHub
#17 (comment).

[gen-failure]

I also have problem with multiple conditions. I am using ydn.db-isw-sql-e-cur-qry-de and the query I want to make is simply:

where condition1 = '1'
where condition2 != '0'
order by fieldA which is integer.

Iterate over all objects...

[yathit]

Index base query is possible. But

That kind of complex query is work in progress. Checkout in /query folder.

[OopDeveloper89]

Hello yathit..
I am trying to realize multiple query too...
I downloaded your last release (1.0.3) and copied "ydn.db-dev.js" into my project.

Now I am trying to fetch all entries by two conditions. In Sql it would look like:
SELECT 'name' from 'mytable' WHERE id=5 AND age=15

How can I do this in ydn db?

I did it like this:
db
.from('mytable')
.where('id, age', '=', [5, 15])
.list()
.done(function(records) {
console.log(records);
});

[yathit]

@OopDeveloper89 check out compound index or key joining as described in http://dev.yathit.com/ydn-db/nosql-query.html

[OopDeveloper89]

I used as workaround sql query. Is this a correct way?
Like:
// var db = new ydn.db.Storage('MyAppName', this.getShema(), { mechanisms: ['websql', 'indexeddb']});
var sql = Select * FROM mytable where x = 4 and b = 5..
db.executeSql(sql).always(function(r) {});

[dpalou]

Hello there,

thank you for this library! In my case I'm struggling with multiple where conditions with OR operator. Is there any way to apply two where conditions with an OR operator without having to do two queries? I'm trying to do the following query:

WHERE (timestart >= X OR timeend >= X) AND timestart <= Y

If I chain .where functions they always use AND operator.

Thanks!

[yathit]

Multiple equal-AND over multiple OR query is possible.

Multiple keyrange-AND is not possible. You will have to use on memory filter for the rest of range query.

[bennycode]

Hello @yathit,

I also want to combine multiple where clauses but https://dev.yathit.com/ydn-db/nosql-query.html seems to be unavailable.

Do I need to add an index for all my field names which are used in the where clauses?

I always get the error: from(...).where(...).where(...).order is not a function

[yathit]

Yes, all field in where clause mutual be indexed.

It was old url. All pages are in documation section on the site.
On Feb 1, 2016 6:51 AM, "Benny Neugebauer" [email protected] wrote:

Hello @yathit https://github.com/yathit,

I also want to combine multiple where clauses but
https://dev.yathit.com/ydn-db/nosql-query.html seems to be unavailable.

Do I need to add an index for all my field names which are used in the
where clauses?

—
Reply to this email directly or view it on GitHub
#17 (comment).

[bennycode]

Thanks for your fast reply.

I created indexes for name, age and name, age but I still get the error: db.from(...).where(...).where(...).list.

Do you see what I am doing wrong?

var schema = {
  stores: [
    {
      name: 'users',
      autoIncrement: true,
      indexes: [
        {
          name: 'name',
          keyPath: 'name'
        },
        {
          name: 'age',
          keyPath: 'age'
        },
        {
          name: 'name, age',
          keyPath: ['name', 'age']
        }
      ]
    }
  ]
};

var db = new ydn.db.Storage('my-db', schema, {mechanisms: ['indexeddb']});

db.clear();

var values = [
  {"name": "Lara", "age": 45, "registered": "2015-12-21T10:14:59.661Z"}
  , {"name": "Sarah", "age": 25, "registered": "2016-01-12T09:00:32.736Z"}
  , {"name": "Benny", "age": 28, "registered": "2015-12-21T09:55:17.201Z"}
  , {"name": "John", "age": 39, "registered": "2015-12-18T11:14:50.426Z"}
  , {"name": "Benny", "age": 30, "registered": "2014-12-18T11:14:50.426Z"}
];

for (var i = 0; i < values.length; i++) {
  db.put('users', values[i]);
}

var query =
  db
  .from('users')
  .where('name', '=', 'Benny')
  .where('age', '<', 30)
  .list()
  .done(function (records) {
    records.forEach(function (record) {
      console.log('Result', record);
    });
  });

[bennycode]

Good news!

I solved the db.from(...).where(...).where(...).listissue by using a ydn.db custom build which has the cur module. But now my db query does not give me any result but it is expected to find 1 record. 😢

[yathit]

That function is not finished yet. The multiple where clause should generate query as follow:

query = db.from('users').where('name, age', '>', ['Benny', 30'], '<', ['Benny', '\uffff']);

[bennycode]

Thanks for your answer. I changed the following:

.where('name', '=', 'Benny')
.where('age', '<', 30)

->

.where('name, age', '<', ['Benny', 30])

These changes cause now this error: Uncaught ConstraintError: Require index "name, age" not found in store "users".

But I have there is an index name, age which is defined as:

{
  name: 'name, age',
  keyPath: ['name', 'age']
}

Also... Shouldn't there be two operators for the multiple where clause? Because I need = and <.

[yathit]

All look good. Please post to jsbin.

[bennycode]

Thanks for helping me!

I made a Fiddle: https://jsfiddle.net/bennyn/ynchm1tw/
If you click on "Run" you can see the output in your Browser's JavaScript console.

Your library is added under External Resources.

[yathit]

Definitely a bug.

currently use https://jsfiddle.net/ynchm1tw/2/

[bennycode]

Thanks for helping out!

This works:

var iterator =
  ydn.db.IndexValueIterator
  .where('users', 'name, age', '>', ['Benny'], '<', ['Benny', 30]);

var query =
  db
  .values(iterator)
  .done(function (records) {
    records.forEach(function (record) {
      console.log('Result', record);
    });
  });

[yathit]

As a note, none of my application use query api and the bug fix will be slow.