The orderBy method allows you to sort the result of the query by a given column. The first argument to the orderBy method should be the column you wish to sort by, while the second argument controls the direction of the sort and may be either asc or desc:
//qb
var getResults = query.from('users')
.where('age','>=','18')
.orderBy('modifiedDate','desc')
.get();
writeDump(getResults);
//sql
SELECT * FROM `users` WHERE `age` >= 18 ORDER BY `modifiedDate` DESC
If you want to order by multiple columns, you can call orderBy multiple times.
groupBy / having
The groupBy and having methods may be used to group the query results. The having method's signature is similar to that of the where method:
//qb
var getResults = query.from('users')
.groupBy('FK_departmentID')
.having('age','>','21')
.orderBy('age','desc')
.get();
writeDump(getResults);
//sql
SELECT * FROM `users` GROUP BY `FK_departmentID` HAVING `age` > 21 ORDER BY `age` DESC
take / limit
To limit the number of results returned from the query, you may use the take method:
//qb
var getResults = query.from('users')
.where('age','>=','18')
.orderBy('modifiedDate','desc')
.take(5)
.get();
writeDump(getResults);
//sql
SELECT * FROM `users` WHERE `age` >= ? ORDER BY `modifiedDate` DESC LIMIT 5
Alternatively, you may use the limit method:
//qb
var getResults = query.from('users')
.where('age','>=','18')
.orderBy('modifiedDate','desc')
.limit(5)
.get();
writeDump(getResults);
//sql
SELECT * FROM `users` WHERE `age` >= ? ORDER BY `modifiedDate` DESC LIMIT 5
offset
To offset the number of results returned from the query, use the offset method:
// This will return rows 26 through 50
query.from( "users" )
.offset( 25 )
.limit( 25 )
.get();
forPage
Combine limit and offset in one method. Pass the current page number and the number of results per page (limit) and we'll calculate the rest.
// This will return records 51 to 75
query.from( "users" )
.forPage( 3, 25 )
.get();