-
Notifications
You must be signed in to change notification settings - Fork 2
query building
title: Query Building
The query-building part of tabel is a very thin layer over knex.js query builder. If you have used knex.js, you'll feel right at home using tabel. This section contains a list of all query building methods provided by tabel. Before we covering the query building API, we'll have a look at a mechanism called forking.
Internally, table queues up your query building operations using closures, and applies them on the knex.js query builder lazily. This allows you to "fork" a query chain into several different ones. Example given below:
const {table} = require('orm');
async function getDifferentTypesOfPosts(ids=[]) {
const postsTable = table('posts').where('id', 'in', ids); // starts a new query chain
const activePostsTable = postsTable.fork().where('is_active', true);
const inactivePostsTable = postsTable.fork().where('is_active', false);
const [posts, activePosts, inactivePosts] = await Promise.all([
postsTable.all(),
activePostsTable.all(),
inactivePostsTable.all()
]);
return {posts, activePosts, inactivePosts};
}Hope this example is enough to clear up the concept of forking. We'll cover all the query building methods available on table next.
Specifies the columns you want to select in your query. Supplied columns will be prefixed with the table's name if they are simple strings without a '.' in them. Example below:
const {table} = require('orm');
const rows = await table('posts').select('id', 'title').all();.select Can also be used to specify aggregate columns using the raw helper. Example below:
const {table, raw} = require('orm');
const postsTable = table('posts').comments().join().groupBy('posts.id');
// the above code joins the related table 'comments' to 'posts' via the relationship defined
// in the table-definition of 'posts'
const postsWithCommentCounts = await postsTable
.select('*', raw('count(comments.id) as num_comments'))
.all()
;
// the above code will translate to:
// select posts.*, count(comments.id) as num_comments ...Adds a where false clause in your query, which results in 0 rows being scoped, and you get an empty array as the result. Example below:
const posts = await table('posts').whereFalse().all();
console.log(posts.length); // outputs 0Adds a where clause on the key column of the table. Works on tables with composite-keys. Accepts either single value or an array of values and applies either a where clause or a where in clause accordingly. Example below:
async function getPostById(id) {
return await table('posts').whereKey(id).first();
}
async function getPostsByIds(ids=[]) {
return await table('posts').whereKey(ids).all();
}
async function getRowByCompositeKey({foo_id, bar_id}) {
return await table('foo_bar').whereKey({foo_id, bar_id}).first();
}
async function getRowsByCompositeKeys(keys=[]) {
// keys = [{foo_id: ..., bar_id: ...}, {foo_id: ..., bar_id: ...}, ...];
return await table('foo_bar').whereKey(keys).all();
}Refer to the table-definitions section to read up on how to set up key columns.
Same as .whereKey method, just adds an or where clause instead of a where clause to the query chain, on the table's key columns.
Adds a where clause to the query chain. Takes arguments in several different formats. Examples below:
.where(conditions={})
const post = await table('posts').where({title: 'foo'}).first();
// adds a "where posts.title = 'foo'" clause to the query.where(conditions=(() => {}))
const posts = await table('posts')
.where((q) => {
q.where('created_at', '>=', startDate);
q.where('created_at', '<=', endDate);
})
.all()
;
// adds a nested where clause to the query.where(field, val)
const post = await table('posts').where('title', 'foo').first();
// adds a "where posts.title = 'foo'" clause to the query.where(field, op, val)
const posts = await table('posts').where('created_at', '>', someDate).all();
// adds a "where posts.created_at > someDate" clause to the query
// op can also be 'in', 'not in', 'between', 'not between'Same as .where method, just adds an or where clause instead of a where clause to the query chain.
Same as .where method, just adds a where not clause instead of a where clause to the query chain.
Same as .whereNot method, just adds a or where not clause instead of a where not clause to the query chain.
Adds a where ... in ... clause to the query chain. Can apply the clause on multiple columns at once. Can handle empty value arrays. Examples below:
.whereIn(key, values=[])
async function getPostsByTitles(titles=[]) {
return await table('posts').whereIn('titles', titles).all();
}.whereIn(keys=[], values=[])
async function getPostsByIdAndTities(idTitlesList=[]) {
// idTitlesList = [{id: '..', title: '..'}, {id: '..', title: '..'}, ...]
return await table('posts').where(['id', 'title'], idTitlesList).all();
}Same as .whereIn method, just adds a or where ... in ... clause instead of a where ... in ... clause to the query chain.
Same as .whereIn method, just adds a where ... not in ... clause instead of a where ... in ... clause to the query chain.
Same as .whereNotIn method, just adds a or where ... not in ... clause instead of a where ... not in ... clause to the query chain.
Adds a where ... is null clause to the query chain. Example below:
async function getPostsWithoutComments() {
return await table('posts').comments().leftJoin().whereNull('comments.id').all();
// we left join 'comments' table to 'posts' table via the relation defined in the
// table-definition of 'posts'. Relation joins covered in detail in their section.
}Same as .whereNull method, just adds a or where ... is null instead of a where ... is null clause to the query chain.
Same as .whereNull method, just adds a where ... is not null instead of a where ... is null clause to the query chain.
Same as .whereNotNull method, just adds a or where ... is not null instead of a where ... is not null clause to the query chain.
Adds a where ... between ... and ... clause to the query chain. Example below:
async function getPostsBetweenDates(minDate, maxDate) {
return await table('posts').whereBetween('created_at', [minDate, maxDate]).all();
}Same as .whereBetween method, just adds a or where ... between ... and ... instead of a where ... between ... and ... clause to the query chain.
Same as .whereBetween method, just adds a where ... not between ... and ... instead of a where ... between ... and ... clause to the query chain.
Same as .whereNotBetween method, just adds a or where ... not between ... and ... instead of a where ... not between ... and ... clause to the query chain.
Adds a where ... clause to the query chain. Variables can be supplied as bindings which are escaped by knex.js. Example below:
async function getPostsByTitle(title='') {
return await table('posts')
.whereRaw('lower(title) like ?', [`%${title.toLowerCase()}%`])
.all()
;
}Same as whereRaw method, just adds a or where ... clause instead of a where ... clause to the query chcain
Adds an offset ... clause to the query chain. Example below:
const posts = await table('posts').orderBy('created_at', 'desc').offset(20).all();Adds a limit ... clause to the query chain. Example below:
const posts = await table('posts').limit(10).all();Adds a limit ... offset ... clause to the query chain based on page and perPage. If perPage argument is not provided, value from table-definition is used. Example below:
const posts = await table('posts').forPage(2, 25).all();Adds an order by ... clause to the query chain. Example below:
const posts = await table('posts').orderBy('created_at', 'asc').all();Adds an order by ... clause to the query chain. More complex sql constructs can be applied using this method on the order by ... clause. Example below:
const {table, raw} = require('orm');
function getPostsOrderedByCommentsCount() {
return await table('posts')
.comments()
.join()
.select('*', raw('count(comments.id)'))
.groupBy('posts.id')
.orderByRaw('count(comments.id)')
.all()
;
}Adds a group by ... clause to the query. Example below:
const posts = await table('posts').author().join().groupBy('users.id');Adds a group by ... clause to the query chain. More complex sql constructs can be applied using this method on the group by ... clause. Example below:
const posts = await table('posts')
.select('posts.title', 'users.email', raw(count('posts.id')))
.author()
.join()
.groupBy('users.id with rollup')
;Adds a having ... clause to the query.
Adds a having ... clause to the query chain. More complex sql constructs can be applied using this method on the having ... clause.
Adds a distinct clause to the query chain.
Adds a join clause to the query chain. Takes arguments in 2 different ways. Examples below:
.join(tableName, fieldA, op, fieldB)
const posts = await table('posts').join('users', 'posts.author_id', '=', 'users.id').all();.join(tableName, joiner=((j) => {}))
const posts = await table('posts')
.comments().join()
.join('users', (j) => {
j.on('posts.author_id', '=', 'users.id')
j.orOn('comments.author_id', '=', 'users.id')
})
.all()
;Similar to .join. Just applies a left join instead of a join.