Skip to content

Query Builder API Documentation

Jump to bottom
Armand Kaufmann edited this page Nov 19, 2025 · 29 revisions

Running Database Queries

Retrieving All Rows From a Table

You may use the from method provided by the Eloquentify\Query to begin a query. The from method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally retrieve the results of the query using the get method:

import {Query} from "eloquentify";

const users = await Query.from('users').get();

The get method returns an array of objects containing the results of the query. You may access each column's value by accessing the column as a property of the object:

import {Query} from "eloquentify";

const users = await Query.from('users').get();

users.map((user) => console.log(user.name))

Retrieving a Single Row / Column From a Table

If you just need to retrieve a single row from a database table, you may use the Eloquentify\Query first method. This method will return a single object:

const users = await Query.from('users').first();

return user.email;

To retrieve a single row by its id column value, use the find method:

const user = await Query.from('users').find(3);

Aggregates

The query builder also provides a variety of methods for retrieving aggregate values like count, max, min, avg, and sum. You may call any of these methods after constructing your query:

import {Query} from "eloquentify";

const users = await Query.from('users').count();

const price = await Query.from('orders').max('price');

Of course, you may combine these methods with other clauses to fine-tune how your aggregate value is calculated:

const price = await Query.from('orders').where('finalized', 1).avg('price');

Select Statements

Specifying a Select Clause

You may not always want to select all columns from a database table. Using the select method, you can specify a custom "select" clause for the query:

import {Query} from "eloquentify";

const users = await Query.from('users')
        .select('name', Query.raw('email as user_email')))
        .get();

The distinct method allows you to force the query to return distinct results:

const users = await Query.from('users').distinct().get();

If you already have a query builder instance and you wish to add a column to its existing select clause, you may use the same select method:

const query = Query.select('name');

const users = await query.select('age')->get();

Raw Expressions

Sometimes you may need to insert an arbitrary string into a query. To create a raw string expression, you may use the static raw method provided by Eloquentify\Query:

const users = await Query.from('users')
        .select(Query.raw('count(*) as user_count, status'), 'id')
        .where('status', '<>', 1)
        .groupBy('status')
        .get();

⚠️ Raw statements will be injected into the query as strings, so you should be extremely careful to avoid creating SQL injection vulnerabilities.

Raw Methods

Instead of using the Query.raw method, you may also use the following methods to insert a raw expression into various parts of your query. Remember, Eloquentify cannot guarantee that any query using raw expressions is protected against SQL injection vulnerabilities.

selectRaw

The selectRaw method can be used in place of select(Query.raw(/* ... */)). This method accepts an optional array of bindings as its second argument:

const orders = await Query.from('orders')
        .selectRaw('price * ? as price_with_tax', [1.0825])
        .get();

whereRaw / orWhereRaw

The whereRaw and orWhereRaw methods can be used to inject a raw "where" clause into your query. These methods accept an optional array of bindings as their second argument:

const orders = await Query.from('orders')
        .whereRaw('price > IF(state = "TX", ?, 100)', [200])
        .get();

havingRaw / orHavingRaw

The havingRaw and orHavingRaw methods may be used to provide a raw string as the value of the "having" clause. These methods accept an optional array of bindings as their second argument:

const orders = await Query.from('orders')
        .select('department', Query.raw('SUM(price) as total_sales'))
        .groupBy('department')
        .havingRaw('SUM(price) > ?', [2500])
        .get();

orderByRaw

The orderByRaw method may be used to provide a raw string as the value of the "order by" clause:

const orders = await Query.from('orders')
        .orderByRaw('updated_at - created_at DESC')
        .get();

groupByRaw

The groupByRaw method may be used to provide a raw string as the value of the group by clause:

const orders = await Query.from('orders')
        .select('city', 'state')
        .groupByRaw('city, state')
        .get();

Joins

Inner Join Clause

The query builder may also be used to add join clauses to your queries. To perform a basic "inner join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. You may even join multiple tables in a single query:

import {Query} from "eloquentify";

const users = await Query.from('users')
        .join('contacts', 'users.id', '=', 'contacts.user_id')
        .join('orders', 'users.id', '=', 'orders.user_id')
        .select('users.*', 'contacts.phone', 'orders.price')
        .get();

Left Join Clause / Right Join Clause

ℹ️ SQLite does not support Right joins, so it probably won't be added in this package until it supports more than SQLite databases

If you would like to perform a "left join" instead of an "inner join", use the leftJoin method. This method has the same signature as the join method:

const users = await Query.from('users')
        .leftJoin('posts', 'users.id', '=', 'posts.user_id')
        .get();

Cross Join Clause

You may use the crossJoin method to perform a "cross join". Cross joins generate a cartesian product between the first table and the joined table:

const sizes = await Query.from('sizes')
        .crossJoin('colors')
        .get();

Basic Where Clauses

Where Clauses

You may use the query builder's where method to add "where" clauses to the query. The most basic call to the where method requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. The third argument is the value to compare against the column's value.

For example, the following query retrieves users where the value of the votes column is equal to 100 and the value of the age column is greater than 35:

const users = await Query.from('users')
        .where('votes', '=', 100)
        .where('age', '>' 35)
        .get();

For convenience, if you want to verify that a column is = to a given value, you may pass the value as the second argument to the where method. Eloquentify will assume you would like to use the = operator:

const users = await Query.from('users').where('votes', 100).get()

As previously mentioned, you may use any operator that is supported by SQLite:

const users = await Query.from('users')
        .where('votes', '>=', 100)
        .get();

const users = await Query.from('users')
        .where('votes', '<>', 100)
        .get();

const users = await Query.from('users')
        .where('name', 'like', 'T%')
        .get();

Or Where Clauses

When chaining together calls to the query builder's where method, the "where" clauses will be joined together using the and operator. However, you may use the orWhere method to join a clause to the query using the or operator. The orWhere method accepts the same arguments as the where method:

const users = await Query.from('users')
        .where('votes', '>', 100)
        .orWhere('name', 'John')
        .get();

If you need to group an "or" condition within parentheses, you may pass a callback as the first argument to the orWhere method:

import {Query} from "eloquentify";

const users = await Query.from('users')
        .where('votes', '>', 100)
        .orWhere((query) => {
            query.where('name', 'Abigail')
                .where('votes', '>', 50);
            })
        .get();

The example above will produce the following SQL:

SELECT * FROM users WHERE votes > 100 OR (name = 'Abigail' and votes > 50)

ℹ️ You should always group orWhere calls in order to avoid unexpected behavior when global scopes are applied.

Where Any / All / None Clauses

Sometimes you may need to apply the same query constraints to multiple columns. For example, you may want to retrieve all records where any columns in a given list are LIKE a given value. You may accomplish this using the whereAny method:

const users = await Query.from('users')
        .where('active', true)
        .whereAny([
            'name',
            'email',
            'phone',
        ], 'LIKE', 'Example%')
        .get();

The query above will result in the following SQL:

SELECT *
FROM users
WHERE active = true AND (
    name LIKE 'Example%' OR
    email LIKE 'Example%' OR
    phone LIKE 'Example%'
)

Similarly, the whereAll method may be used to retrieve records where all of the given columns match a given constraint:

const posts = await Query.from('posts')
        .where('published', true)
        .whereAll([
            'title',
            'content',
        ], 'LIKE', '%Eloquentify%')
        .get();

The query above will result in the following SQL:

SELECT *
FROM posts
WHERE published = true AND (
    title LIKE '%Eloquentify%' AND
    content LIKE '%Eloquentify%'
)

The whereNone method may be used to retrieve records where none of the given columns match a given constraint:

const posts = await Query.from('posts')
        .where('published', true)
        .whereNone([
            'title',
            'lyrics',
            'tags',
        ], 'LIKE', '%explicit%')
        .get();

The query above will result in the following SQL:

SELECT *
FROM albums
WHERE published = true AND NOT (
    title LIKE '%explicit%' OR
    lyrics LIKE '%explicit%' OR
    tags LIKE '%explicit%'
)

Additional Where Clauses

whereIn / whereNotIn / orWhereIn / orWhereNotIn

The whereIn method verifies that a given column's value is contained within the given array:

const users = await Query.from('users')
        .whereIn('id', [1,2,3])
        .get();

The whereNotIn method verifies that the given column's value is not contained in the given array:

const users = await Query.from('users')
        .whereNotIn('id', [1,2,3])
        .get();

whereBetween / orWhereBetween

The whereBetween method verifies that a column's value is between two values:

const users = await Query.from('users')
        .whereBetween('votes', [1, 100])
        .get();

whereNotBetween / orWhereNotBetween

The whereNotBetween method verifies that a column's value lies outside of two values:

const users = await Query.from('users')
        .whereNotBetween('votes', [1, 100])
        .get();

whereBetweenColumns / whereNotBetweenColumns / orWhereBetweenColumns / orWhereNotBetweenColumns

The whereBetweenColumns method verifies that a column's value is between the two values of two columns in the same table row:

const users = await Query.from('users')
        .whereBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
        .get();

The whereNotBetweenColumns method verifies that a column's value lies outside the two values of two columns in the same table row:

const users = await Query.from('users')
        .whereNotBetweenColumns('weight', ['minimum_allowed_weight', 'maximum_allowed_weight'])
        .get();

whereColumn / orWhereColumn

The whereColumn method may be used to verify that two columns are equal:

const users = await Query.from('users')
        .whereColumn('first_name', 'last_name')
        .get();

You may also pass a comparison operator to the whereColumn method:

const users = await Query.from('users')
        .whereColumn('updated_at', '>', 'created_at')
        .get();

Logical Grouping

Sometimes you may need to group several "where" clauses within parentheses in order to achieve your query's desired logical grouping. In fact, you should generally always group calls to the orWhere method in parentheses in order to avoid unexpected query behavior. To accomplish this, you may pass a closure to the where method:

const users = await Query.from('users')
        .where('name', '=', 'John')
        .where((query) => {
            query.where('votes', '>', 100)
                .orWhere('title', '=', 'Admin');
            })
        .get();

As you can see, passing a closure into the where method instructs the query builder to begin a constraint group. The closure will receive a query builder instance which you can use to set the constraints that should be contained within the parenthesis group. The example above will produce the following SQL:

select * from users where name = 'John' and (votes > 100 or title = 'Admin')

ℹ️ You should always group orWhere calls in order to avoid unexpected behavior when global scopes are applied.

Advanced Where Clauses

Where Exists Clauses

The whereExists / orWhereExists methods allows you to write "where exists" SQL clauses. The whereExists / orWhereExists methods accepts a closure which will receive a query builder instance, allowing you to define the query that should be placed inside of the "exists" clause:

const users = await Query.from('users')
        .whereExists((query) => {
            query.select(Query.raw(1))
                .from('orders')
                .whereColumn('orders.user_id', 'user.id');
            })
        .get();

Alternatively, you may provide a query object to the whereExists method instead of a closure:

const orders = Query.from('orders')
        .select(Query.raw(1))
        .whereColumn('orders.user_id', 'users.id');

const users = await Query.from('users')
        .whereExists(orders)
        .get();

Both of the examples above will produce the following SQL:

select * from users
where exists (
    select 1
    from orders
    where orders.user_id = users.id
)

Ordering, Grouping, Limit and Offset

Ordering

The orderBy Method

The orderBy method allows you to sort the results of the query by a given column. The first argument accepted by the orderBy method should be the column you wish to sort by, while the second argument determines the direction of the sort and may be either asc or desc:

const users = await Query.from('users')
        .orderBy('name', 'desc')
        .get();

To sort by multiple columns, you may simply invoke orderBy as many times as necessary:

const users = await Query.from('users')
        .orderBy('name', 'DESC')
        .orderBy('email', 'ASC')
        .get();

The sort direction is optional, and is ascending by default. If you want to sort in descending order, you can specify the second parameter for the orderBy method, or just use orderByDesc:

const users = await Query.from('users')
        .orderByDesc('verified_at')
        .get();

Grouping

The groupBy and having Methods

As you might expect, 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:

const users = await Query.from('users')
        .groupBy('account_id')
        .having('account_id', '>', 100)
        .get();

You can use the havingBetween method to filter the results within a given range:

const report = await Query.from('orders')
        .selectRaw('count(id) as number_of_orders, customer_id')
        .groupBy('customer_id')
        .havingBetween('number_of_orders', [5, 15])
        .get();

You may pass multiple arguments to the groupBy method to group by multiple columns:

const users = await Query.from('users')
        .groupBy('first_name', 'status')
        .having('account_id', '>', 100)
        .get();

To build more advanced having statements, see the havingRaw method.

Limit and Offset

You may use the limit and offset methods to limit the number of results returned from the query or to skip a given number of results in the query:

const users = await Query.from('users')
        .offset(10)
        .limit(5)
        .get();

Conditional Clauses

Sometimes you may want certain query clauses to apply to a query based on another condition. For instance, you may only want to apply a where statement if a given input value is present. You may accomplish this using the when method:

const role = 'sales' // from a request: could be an empty string ''

const users = await Query.from('users')
        .when(role, (query) => {
            query.where('role_id', role);
        })
        .get();

The when method only executes the given closure when the first argument is true. If the first argument is false, the closure will not be executed. So, in the example above, the closure given to the when method will only be invoked if the role field is present on the incoming request and evaluates to true.

You may pass another closure as the third argument to the when method. This closure will only execute if the first argument evaluates as false. To illustrate how this feature may be used, we will use it to configure the default ordering of a query:

// types are added for examples and clarity

const sortByVotes: boolean = false // from a request: could be true

const users = await Query.from('users')
        .when(sortByVotes, (Query: query, boolean: sortByVotes) => {
            query.orderBy('votes');
        }, (Query: query, boolean: sortByVotes) => {
            query.orderBy('name')
        })
        .get();

Clone this wiki locally