SQL queries generated by Eloquent ORM reference
get (all)
User::get()SELECT * FROM users
get (all) with soft deletes on
User::get()SELECT * FROM users WHERE users.deleted_at IS NULL
get with soft deleted rows
User::withTrashed()->get()SELECT * FROM users
first
User::first()SELECT * FROM users LIMIT 1
find
User::find(2)SELECT * FROM users WHERE users.id = 2 LIMIT 1
findMany
User::findMany([1, 2, 3])SELECT * FROM users WHERE users.id IN (1, 2, 3)
value
User::where('name', 'John Smith')->value('email')SELECT email FROM users WHERE name = 'John Smith' LIMIT 1
paginate
User::paginate()SELECT COUNT(*) AS aggregate FROM users
SELECT * FROM users LIMIT 15 OFFSET 0
where
User::where('age', '>', 32)->get()SELECT * FROM users WHERE `age` > 32
orWhere
User::where('age', '>', 40)->orWhere('age', '<', 20)->get()SELECT * FROM users WHERE age > 40 OR age < 20
whereIn
User::whereIn('age', [20, 30, 40])->get()SELECT * FROM users WHERE age IN (20, 30, 40)
whereBetween
User::whereBetween('age', [20,40])->get()SELECT * FROM users WHERE age BETWEEN 20 AND 40
whereDate
User::whereDate('created_at', now())->get()SELECT * FROM `users` WHERE DATE(created_at) = '2023-10-04'
create
User::create(['name' => 'John', 'email' => '[email protected]'])INSERT INTO users (name, email, updated_at, created_at)
VALUES ('John', '[email protected]', '2023-10-04 15:30:34', '2023-10-04 15:30:34')
firstOrCreate
User::firstOrCreate(['email' => '[email protected]'], ['name' => 'John'])SELECT * FROM users WHERE (email = '[email protected]') LIMIT 1
INSERT INTO users (email, name, updated_at, created_at)
VALUES ('[email protected]', 'John', '2023-10-04 16:17:59', '2023-10-04 16:17:59')
UPDATE
User::where('status', 'some_status')->update(['type' => 'some_type'])UPDATE users SET type = 'some_type', users.updated_at = '2023-10-04 15:36:44'
WHERE status = 'some_status'
Update single row
$user->update(['name' => 'John', 'email' => '[email protected]'])UPDATE users SET name = 'John', email = '[email protected]', users.updated_at = '2023-10-04 16:12:30'
WHERE id = 1
updateOrCreate
User::updateOrCreate(['email' => '[email protected]'], ['name' => 'James']);SELECT * FROM users WHERE (email = '[email protected]') LIMIT 1
UPDATE users SET name = 'James', users.updated_at = '2023-10-04 16:14:18'
WHERE id = 1
delete
User::where('status', 'some_status')->delete()DELETE FROM users WHERE status = 'some_status'
Delete with soft deletes on
User::where('status', 'some_status')->delete()UPDATE users SET deleted_at = '2023-10-25 12:58:26', users.updated_at = '2023-10-25 12:58:26'
WHERE status = 'some_status' AND users.deleted_at IS NULL
Delete single row
$user->delete()DELETE FROM users WHERE id = 1
Delete single row with soft deletes on
$user->delete()UPDATE users SET deleted_at = '2023-10-25 12:59:52', users.updated_at = '2023-10-25 12:59:52'
WHERE id = 1
destroy
User::destroy(1, 2, 3)SELECT * FROM users WHERE id IN (1, 2, 3)
DELETE FROM users WHERE id = 1
DELETE FROM users WHERE id = 2
DELETE FROM users WHERE id = 3
Destroy with soft deletes on
User::destroy(1, 2, 3)SELECT * FROM users WHERE id IN (1, 2, 3) AND users.deleted_at IS NULL
UPDATE users SET deleted_at = '2023-10-25 12:54:53', users.updated_at = '2023-10-25 12:54:53' WHERE id = 1
UPDATE users SET deleted_at = '2023-10-25 12:54:53', users.updated_at = '2023-10-25 12:54:53' WHERE id = 2
UPDATE users SET deleted_at = '2023-10-25 12:54:53', users.updated_at = '2023-10-25 12:54:53' WHERE id = 3
Restore soft deleted row
$user->restore()UPDATE users SET deleted_at = '', users.updated_at = '2023-10-25 13:07:24' WHERE id = 1
profiles table has id, user_id, city
User hasOne profile, profile belongsTo user
For nested relationships examples another one to one relationship is used. Profile hasOne passport, passport belongsTo profile
Get user's profile
$user->profile or $user->profile()->first()SELECT * FROM profiles WHERE profiles.user_id = 2 AND profiles.user_id IS NOT NULL LIMIT 1
Get user with profile
User::with('profile')->find(2)SELECT * FROM users WHERE users.id = 2 LIMIT 1
SELECT * FROM profiles WHERE profiles.user_id IN (2)
Get user's passport (nested one to one relationships)
$user->profile->passportSELECT * FROM users WHERE users.id = 2 LIMIT 1
SELECT * FROM profiles WHERE profiles.user_id = 2 AND profiles.user_id IS NOT NULL LIMIT 1
SELECT * FROM passports WHERE passports.profile_id = 4 AND passports.profile_id IS NOT NULL LIMIT 1
Get user with profile AND passport (nested one to one relationships)
User::with('profile.passport')->find(2)SELECT * FROM users WHERE users.id = 2 LIMIT 1
SELECT * FROM profiles WHERE profiles.user_id IN (2)
SELECT * FROM passports WHERE passports.profile_id IN (4)
Get users who have profiles
User::has('profile')->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM profiles WHERE users.id = profiles.user_id)
Get users who have passport (nested one to one relationships)
User::has('profile.passport')->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM profiles WHERE users.id = profiles.user_id AND EXISTS
(SELECT * FROM passports WHERE profiles.id = passports.profile_id)
)
Get users from Adelaide
User::whereHas('profile', function ($q) {
$q->where('city', 'Adelaide');
})->get();SELECT * FROM users WHERE EXISTS
(SELECT * FROM profiles WHERE users.id = profiles.user_id AND city = 'Adelaide')
Get users who have profiles and load their profiles
User::has('profile')->with('profile')->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM profiles WHERE users.id = profiles.user_id)
SELECT * FROM profiles WHERE profiles.user_id IN (1, 2, 3)
orders table has id, user_id, comment
User has many orders. Order belongsTo user
For nested relationships examples another one to many relationship is used. Order hasMany support tickets, support ticket belongsTo order.
Get user's orders
$user->orders or $user->orders()->get()SELECT * FROM orders WHERE orders.user_id = 1 AND orders.user_id IS NOT NULL
Get users with orders
User::with('orders')->get()SELECT * FROM users
SELECT * FROM orders WHERE orders.user_id IN (1, 2, 3)
Get users with orders and support tickets (nested one to many relationships)
User::with('orders.supportTickets')->get()SELECT * FROM users
SELECT * FROM orders WHERE orders.user_id IN (1, 2, 3)
SELECT * FROM support_tickets WHERE support_tickets.order_id IN (7, 8, 9)
Load companies for user
$user->load('orders')SELECT * FROM orders WHERE orders.user_id IN (1)
Get users who have orders
User::has('orders')->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM orders WHERE users.id = orders.user_id)
Get users who have support tickets (nested one to many relationships)
User::has('orders.supportTickets')->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM orders WHERE users.id = orders.user_id AND EXISTS
(SELECT * FROM support_tickets WHERE orders.id = support_tickets.order_id))
Get users who has orders with empty comment
User::whereHas('orders', function ($q) {
$q->whereNull('comment');
})->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM orders WHERE users.id = orders.user_id AND comment IS NULL)
Get users who have orders and load their orders
User::has('orders')->with('orders')->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM orders WHERE users.id = orders.user_id)
SELECT * FROM orders WHERE orders.user_id IN (1, 2, 3)
companies table has id, name
company_user is a pivot table, it has company_id, user_id columns
User belongsToMany companies, company belongsToMany users
For nested relationships examples another many to many relationship is used. Company belongsToMany employees, employee belongsToMany companies
Get user's companies
$user->companies or user->companies()->get()SELECT companies.*, company_user.user_id AS pivot_user_id, company_user.company_id AS pivot_company_id
FROM companies
INNER JOIN company_user ON companies.id = company_user.company_id
WHERE company_user.user_id = 1
Get users with companies
User::with('companies')->get()SELECT * FROM users
SELECT companies.*, company_user.user_id AS pivot_user_id, company_user.company_id AS pivot_company_id
FROM companies
INNER JOIN company_user ON companies.id = company_user.company_id
WHERE company_user.user_id IN (1, 2, 3)
Get users with employees (nested many to many relationships)
User::with('companies.employees')->get()SELECT * FROM usersSELECT companies.*, company_user.user_id AS pivot_user_id, company_user.company_id AS pivot_company_id
FROM companies
INNER JOIN company_user ON companies.id = company_user.company_id
WHERE company_user.user_id IN (1, 2, 3)
SELECT employees.*, company_employee.company_id AS pivot_company_id, company_employee.employee_id AS pivot_employee_id
FROM employees
INNER JOIN company_employee ON employees.id = company_employee.employee_id
WHERE company_employee.company_id IN (7, 8, 9)
Load companies for user
$user->load('companies')SELECT companies.*, company_user.user_id AS pivot_user_id, company_user.company_id AS pivot_company_id
FROM companies
INNER JOIN company_user ON companies.id = company_user.company_id
WHERE company_user.user_id IN (1)
Load companies and employees for user (nested many to many relationships)
$user->load('companies.employees')SELECT companies.*, company_user.user_id AS pivot_user_id, company_user.company_id AS pivot_company_id
FROM companies
INNER JOIN company_user ON companies.id = company_user.company_id
WHERE company_user.user_id IN (2)
SELECT employees.*, company_employee.company_id AS pivot_company_id, company_employee.employee_id AS pivot_employee_id
FROM employees
INNER JOIN company_employee ON employees.id = company_employee.employee_id
WHERE company_employee.company_id IN (7, 8, 9)
Get users who have companies
User::has('companies')->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM companies
INNER JOIN company_user ON companies.id = company_user.company_id
WHERE users.id = company_user.user_id)
Get users who have companies with employees (nested many to many relationships)
User::has('companies.employees')->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM companies
INNER JOIN company_user ON companies.id = company_user.company_id
WHERE users.id = company_user.user_id AND EXISTS
(SELECT * FROM employees
INNER JOIN company_employee ON employees.id = company_employee.employee_id
WHERE companies.id = company_employee.company_id))
Get users who have companies with empty description
User::whereHas('companies', function ($q) {
$q->whereNull('description');
})->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM companies
INNER JOIN company_user ON companies.id = company_user.company_id
WHERE users.id = company_user.user_id AND description IS NULL)
Get users who have companies and load their companies
User::has('orders')->with('orders')->get()SELECT * FROM users WHERE EXISTS
(SELECT * FROM orders WHERE users.id = orders.user_id)
SELECT * FROM orders WHERE orders.user_id IN (1, 2, 3)
count
User::count()SELECT COUNT(*) AS aggregate FROM users
max (min, avg, sum)
User::max('age')SELECT MAX(age) AS aggregate FROM users
withSum (withAvg, withMin, withMax, withCount)
User::withSum('orders', 'total')->get();SELECT users.*, (SELECT SUM(orders.total) FROM orders WHERE users.id = orders.user_id) AS orders_sum_total FROM users
latest (`oldest``)
User::latest()->get()SELECT * FROM users ORDER BY created_at DESC
withExists
User::withExists('orders')->get()SELECT users.*, EXISTS(SELECT * FROM orders WHERE users.id = orders.user_id) AS orders_exists FROM users
increment (decrement)
User::where('id', 2)->increment('bonus', 12)UPDATE users SET bonus = bonus + 12, users.updated_at = '2023-10-25 14:58:10' WHERE id = 2
