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