Spell

Spell

Spell is the query builder of Leoric which has several important characters that made a powerful querying interface possible.

  • Deferred
  • Method chaining
  • Parsing SQL expressions

Some of the methods of Spell follow such pattern:

  • Methods that begin with $, such as Spell#$group, are methods that support chaining. For example, we can do new Spell().$select('foo').$group('bar') to construct an SQL query.
  • These methods have twin methods without the $, which have the same functionalities but differ in only one thing, every call of these methods makes changes on a duplicate of current spell and returns the duplicated one. For example, new Spell().select('foo').group('bar') works and each call, new Spell(), .select('foo'), and .group('bar'), returns a new spell.

Spell adapts this pattern to make following usage possible:

const query = Post.find('createdAt > ?', new Date(2012, 4, 15));
const [ { count } ] = query.count();
const posts = query.offset(page * pageSize).limit(pageSize);
this.body = { count, posts }

query.count() and query.offset() won't interfere with each other because new Spell gets returned every time these methods were called. For brevity, only the methods start with $ are documented.

For performance reason, Bone use the prefixed with $ ones mostly.

Constructor

new Spell(Model, opts)

Create a spell.

Parameters:
Name Type Description
Model

A sub class of Bone.
opts

Extra columnAttributes to be set.

Members

Model

A sub-class of Bone.

dup

Get a duplicate of current spell.

Methods

$delete()

Set query command to DELETE.

$forceIndex()

Example:
.forceIndex('idx_id')
.forceIndex('idx_id', 'idx_title_id')
.forceIndex('idx_id', { orderBy: ['idx_title', 'idx_org_id'] }, { groupBy: 'idx_type' })

$from(table)

Set the table of the spell. If an instance of Spell is passed, it will be used as a derived table.

Parameters:
Name Type Description
table

$get(index)

Get nth record.

Parameters:
Name Type Description
index

$group(…names)

Set GROUP BY columnAttributes. select_expr with AS is supported, hence following expressions have the same effect:

.select('YEAR(createdAt)) AS year').group('year');
Parameters:
Name Type Attributes Description
names <repeatable>
Example:
.group('city');
.group('YEAR(createdAt)');

$having(conditions, …values)

Set the HAVING conditions, which usually appears in GROUP queries only.

Parameters:
Name Type Attributes Description
conditions
values <repeatable>
Example:
.having('average between ? and ?', 10, 20);
.having('maximum > 42');
.having({ count: 5 });

$ignoreIndex()

Example:
.ignoreIndex('idx_id')
.ignoreIndex('idx_id', 'idx_title_id')
.ignoreIndex('idx_id', { orderBy: ['idx_title', 'idx_org_id'] }, { groupBy: 'idx_type' })

$join(Model, onConditions, …values)

LEFT JOIN arbitrary models with specified ON conditions.

Parameters:
Name Type Attributes Description
Model
onConditions
values <repeatable>
Example:
.join(User, 'users.id = posts.authorId');
.join(TagMap, 'tagMaps.targetId = posts.id and tagMaps.targetType = 0');

$limit(rowCount)

Set the LIMIT of the query.

Parameters:
Name Type Description
rowCount

$offset(skip)

Set the OFFSET of the query.

Parameters:
Name Type Description
skip

$optimizerHints(…hints)

add optimizer hints to query

Parameters:
Name Type Attributes Description
hints <repeatable>
Example:
.optimizerHints('SET_VAR(foreign_key_checks=OFF)')
.optimizerHints('SET_VAR(foreign_key_checks=OFF)', 'MAX_EXECUTION_TIME(1000)')

$order(name, direction)

Set the ORDER of the query

Parameters:
Name Type Description
name
direction
Example:
.order('title');
.order('title', 'desc');
.order({ title: 'desc' });
.order('id asc, gmt_created desc')

$select(…names)

Whitelist columnAttributes to select. Can be called repeatedly to select more columnAttributes.

Parameters:
Name Type Attributes Description
names <repeatable>
Example:
.select('title');
.select('title', 'createdAt');
.select('IFNULL(title, "Untitled")');

$useIndex()

Example:
.useIndex('idx_id')
.useIndex('idx_id', 'idx_title_id')
.useIndex('idx_id', { orderBy: ['idx_title', 'idx_org_id'] }, { groupBy: 'idx_type' })

$where(conditions, …values)

Set WHERE conditions. Both string conditions and object conditions are supported.

Parameters:
Name Type Attributes Description
conditions
values <repeatable>

only necessary when using templated string conditions
Example:
.where({ foo: null });
.where('foo = ? and bar >= ?', null, 42);

$with(…qualifiers)

LEFT JOIN predefined associations in model.

Parameters:
Name Type Attributes Description
qualifiers <repeatable>
Example:
.with('attachment');
.with('attachment', 'comments');
.with({ comments: { select: 'content' } });

(async, generator) batch()

Get the query results by batch. Returns an async iterator which can then be consumed with an async loop or the cutting edge for await. The iterator is an Object that contains a next() method:

const iterator = {
  i: 0,
  next: () => Promise.resolve(this.i++);
}

See examples to consume async iterators properly. Currently async iterator is proposed and implemented by V8 but hasn't made into Node.js LTS yet.

Example:
async function consume() {
  const batch = Post.all.batch();
  while (true) {
    const { done, value: post } = await batch.next();
    if (value) handle(post);
    if (done) break
  }
}
// or
for await (const post of Post.all.batch()) {
   handle(post);
}

catch(reject)

Parameters:
Name Type Description
reject

finally(onFinally)

Parameters:
Name Type Description
onFinally

then(resolve, reject)

Fake spell as a thenable object so it can be consumed like a regular Promise.

Parameters:
Name Type Description
resolve
reject
Example:
const post = await Post.first
Post.last.then(post => handle(post));

toSqlString()

Format current spell to SQL string.

toString()

Alias of Spell#toSqlString