Query Interface

Alexey Gordeyev edited this page Apr 7, 2015 · 4 revisions

Rows and Documents can be retrieved through several static helper methods of models.

Any model method which involves specifying query conditions can be executed two ways:

When a callback function:

is passed, the operation will be executed immediately with the results passed to the callback. is not passed, an instance of Query is returned, which provides a special QueryBuilder interface for you. Let's take a look at what happens when passing a callback:

Query Interface API

Example Queries

var Query = User.find();
Query.where('active', 1);
Query.order('id DESC');
Query.run({}, function(err, users) {
   // your code here
});

#where(key, val)

var Query = User.find();
Query.where('userId', user.id);
Query.run({}, function(err, count){
   // your code here
});
// the same as prev
User.find({where: {userId: user.id}}, function(err, users){
   // your code here
});

#gt(key, val)

Specifies a greater than expression.

Query.gt('userId', 100);
Query.where('userId').gt(100);
// the same as prev
User.find({
      where: {
         userId: {
              gt : 100
         }
      }
    }}, function(err, users){
   // your code here
});

#gte(key, val)

Specifies a greater than or equal to expression.

Query.gte('userId', 100);
Query.where('userId').gte(100);
// the same as prev
User.find({
      where: {
         userId: {
              gte : 100
         }
      }
    }}, function(err, users){
   // your code here
});

#lt(key, val)

Specifies a less than expression.

Query.lt('visits', 100);
Query.where('visits').lt(100);
// the same as prev
Post.find({
      where: {
         visits: {
              lt : 100
         }
      }
    }}, function(err, posts){
   // your code here
});

#lte(key, val)

Specifies a less than or equal to expression.

Query.lte('visits', 100);
Query.where('visits').lte(100);
// the same as prev
Post.find({
      where: {
         visits: {
              lte : 100
         }
      }
    }}, function(err, posts){
   // your code here
});

#ne(key, val)

Matches all values that are not equal to the value specified in the query.

Query.ne('userId', 100);
Query.where('userId').ne(100);
// the same as prev
User.find({
      where: {
         userId: {
              ne : 100
         }
      }
    }}, function(err, users){
   // your code here
});

#in(key, val)

Matches any of the values that exist in an array specified in the query.

Query.in('userId', [1,5,7,9]);
Query.where('userId').in([1,5,7,9]);
// the same as prev
User.find({
      where: {
         userId: {
              in : [1,5,7,9]
         }
      }
    }}, function(err, users){
   // your code here
});

#regex(key, val)

Selects rows where values match a specified regular expression.

Query.regex('title', 'intel');
Query.where('title').regex('intel');
// the same as prev
Post.find({
      where: {
         title: {
              regex : 'intel'
         }
      }
    }}, function(err, posts){
   // your code here
});

#like(key, val)

Pattern matching using a simple regular expression comparison.

Query.like('title', 'intel');
// the same as prev
Post.find({
      where: {
         title: {
              like : 'intel'
         }
      }
    }}, function(err, posts){
   // your code here
});

#nlike(key, val)

Pattern not matching using a simple regular expression comparison.

Query.nlike('title', 'intel');
// the same as prev
Post.find({
      where: {
         title: {
              nlike : 'intel'
         }
      }
    }}, function(err, posts){
   // your code here
});

#nin(key, val)

Matches values that do not exist in an array specified to the query.

Query.nin('id', [1,2,3]);
// the same as prev
Post.find({
      where: {
          title : {
                   nin : [1,2,3]
          }
      }
    }}, function(err, posts){
   // your code here
});

#sort(key, val)

Sets the sort column and direction.

Query.sort('title DESC');
Query.sort('title', 'DESC');
// the same as prev
Post.find({
      order: 'title DESC'
    }}, function(err, posts){
   // your code here
});

#group(key)

Sets the group by column.

Query.group('title');
// is the same as
Post.find({
      group: 'title'
    }}, function(err, posts){
   // your code here
});

#asc(key)

Sets the sort column and direction ASC.

Query.asc('title');
// is the same as
Query.sort('title ASC');
// the same as prev
Post.find({
      order: 'title ASC'
    }}, function(err, posts){
   // your code here
});

#desc(key)

Sets the sort column and direction DESC.

Query.desc('title');
// is the same as
Query.sort('title DESC');
// the same as prev
Post.find({
      order: 'title DESC'
    }}, function(err, posts){
   // your code here
});

#skip(val)

The skip method specifies at which row the database should begin returning results.

Query.skip(10);
// the same as prev
Post.find({
      skip: 10
    }}, function(err, posts){
   // your code here
});

#limit(val)

The limit method specifies the max number of rows to return.

Query.limit(10);
// the same as prev
Post.find({
      limit: 10
    }}, function(err, posts){
   // your code here
});

#slice(val)

Limits the number of elements projected from an array. Supports skip and limit slices.

Query.slice([20,10]);
// the same as prev
Post.find({
      skip: 20,
      limit: 10
    }}, function(err, posts){
   // your code here
});

#between(key, val)

Check whether a value is within a range of values.

Query.between('created', ['2013-01-01','2013-01-08']);
// the same as prev
Post.find({
      where: {
         created: {
            between : ['2013-01-01','2013-01-08']
         }
      }
    }}, function(err, posts){
   // your code here
});