This chapter will be used in querying.

Key name

The key name in querying should be name defined in Model.

eg.

toshihiko.define("model", [
    { name: "bar", column: "baz", ... },
    { column: "foo", ... }
]);

You should use foo and bar while querying instead of using foo and baz which baz indicates the real column name in collection / table and bar is the logic name in your project code.

Where

This will be useful in Model.where or Query.where.

Basic Querying

The basic querying is the "equal" querying. The format will be:

key: value

For an example:

{
    a: 1,
    b: 2
}

means a = 1 AND b = 2.

Advanced Comparation

The format will be:

key: {
    COMMAND: value
}

The command is listed below:

  • $eq / ===: equality;
  • $neq / !==: not equality;
  • $lt / <: less than;
  • $gt / >: greater than;
  • $lte / <=: less than or equality;
  • $gte / >=: greater than or equality;
  • $like: like;
  • $in: in an array.
{
    a: { $eq: 1 },
    b: { "!==": 2 },
    c: { $lt: 3 },
    d: { ">": 4 },
    e: { $lte: 5 },
    f: { ">=": 6 },
    g: { $like: "%hello" },
    h: { $in: [ 1, 2, 3, 4 ] }
}

Logic Combination

This section will tell you how to use OR and AND.

All querys without specified logic are default to AND.

{
    a: 1,
    b: 2
}

querying above will be translate to something like a = 1 AND b = 2.

You could add logic combination when necessary. The format will be:

LOGIC: [ several_logics ]

// or

LOGIC: { several_logics }

// or nested

LOGIC: [ { LOGIC: ... }, ... ]

And LOGIC can be one of $or and $and.

Here's some examples:

{
    $or: {
        a: 1,
        b: 2
    }
}

{
    $or: {
        a: { $or: [ 1, 2 ] },
        b: 2
    }
}

{
    $and: {
        $or: [
            { a: 1, b: 2 },
            { a: 2, b: 1 },
            { a: { $gt: 3 }, b: { $lt: 5 } },
            { a: { $or: { $gt: 100, $lt: -100 } }, b: { $gt: -100, $lt: 100 } }
        ],
        foo: 1
    }
}

Ordering

This will be useful in Model.order / Model.orderBy or Query.order / Query.orderBy.

Order String

If you're passing a string, the format should like:

"KEY1, KEY2 asc, KEY3 desc, ..."

One or several key(s) and optional order sign splited with comma.

Order Object

If you're passing an object, the format should like:

{
    KEY1: 1,
    KEY2: -1,
    KEY3: "asc",
    KEY4: "DESC"
}

1 indicates "asc" while -1 indicates "desc".

Order Array

If you're passing an array, the format should like:

[ "KEY1", "KEY2 asc", { KEY3: 1 }, { KEY4: -1 } ]

Each key should be one element of the array, and each of their format may reference above.

Field

This will be useful in Model.field / Model.fields or Query.field / Query.fields.

Field String

If you're passing a string, the format should like:

"KEY1, KEY2, ..."

One or several key(s) splited with comma.

Field Array

If you're passing an array, the format should like:

[ "KEY1", "KEY2", ... ]

Each key per element in the array.

Limit / Pagination

This will be useful in Model.limit or Query.limit.

Limit String

If you're passing a string, the format should like:

"1, 2"

// or

"1"

Assume you're using MySQL, the first string will be translated to LIMIT 1, 2 and the second one will be translate to LIMIT 1.

Limit Array

If you're passing an array, the format should like:

[ 1, 2 ]

// or

[ 1 ]

Assume you're using MySQL again, the first string will be translated to LIMIT 1, 2 and the second one will be translate to LIMIT 1 again.

Limit Number

If you're passing a single number or two numbers like:

.limit(1, 2);
.limit(1);

Assume you're using MySQL again and again, the first string will be translated to LIMIT 1, 2 and the second one will be translate to LIMIT 1 again and again.

Index

This will be useful in Model.index.

The only argument you should pass is a single index key name (NOT A COLUMN NAME).

eg. If you've created an index key named this_is_an_index and you want to forcely use this index while querying, you only need to pass the key name this_is_an_index into .index().

Conn

This will be useful in Model.conn or Query.conn.

Force using a connection in this query. Usually be used in transaction.

.conn(conn);

E.g.

Model.beginTransaction(function(err, conn) {
    Model.where({ foo: 1 }).conn(conn).findOne(function(err, foo) {
        foo.foo = 2;
        foo.update(conn, function(err) {
            Model.commit(conn, function(err) {
                //
            });
        });
    });
});

Refer: Model::beginTransaction().

Crowd Update

This will be useful in Model.update(data, callback).

The data should be an object and format like:

{
    KEY1: SPECIFIED_VALUE,
    KEY2: ROW_COMMAND
}

Specified Value

You only need to consider the value of the key-value pair is a specified value.

eg.

{
    a: 1,
    b: "2"
}

a will be updated to 1 and b will be updated to "2".

Raw Command

String wrapped with {{ / }} will be considered as a raw command. Substring inside of {{ / }} is the raw command.

eg.

{
    a: "{{a + 1}}",
    b: "{{BIT_COUNT(a ^ 1)}}"
}

Assume you're using MySQL, data above will be translate to SET a = a + 1, b = BIT_COUNT(a ^ 1).