Query Builder API Documentation

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();