-
Notifications
You must be signed in to change notification settings - Fork 1
Query Builder API Documentation
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))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);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');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();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.
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.
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();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();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();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();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();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();ℹ️ 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();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();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();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
orWherecalls in order to avoid unexpected behavior when global scopes are applied.
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%'
)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();The whereBetween method verifies that a column's value is between two values:
const users = await Query.from('users')
.whereBetween('votes', [1, 100])
.get();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();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();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();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
orWherecalls in order to avoid unexpected behavior when global scopes are applied.
The whereExists method allows you to write "where exists" SQL clauses. The whereExists method 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
)