README.md

SQL queries generated by Eloquent ORM reference

Get

Where

Create

Update

Delete

One to One

One to Many

Many to Many

Aggregate functions

Miscellaneous  

Get

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

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

create

User::create(['name' => 'John', 'email' => 'john@smith.com'])

INSERT INTO users (name, email, updated_at, created_at)
    VALUES ('John', 'john@smith.com', '2023-10-04 15:30:34', '2023-10-04 15:30:34')

 

firstOrCreate

User::firstOrCreate(['email' => 'john@smith.com'], ['name' => 'John'])

SELECT * FROM users WHERE (email = 'john@smith.com') LIMIT 1
INSERT INTO users (email, name, updated_at, created_at)
    VALUES ('john@smith.com', 'John', '2023-10-04 16:17:59', '2023-10-04 16:17:59')

 

Update

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' => 'john@smith.com'])

UPDATE users SET name = 'John', email = 'john@smith.com', users.updated_at = '2023-10-04 16:12:30'
    WHERE id = 1

 

updateOrCreate

User::updateOrCreate(['email' => 'john@smith.com'], ['name' => 'James']);

SELECT * FROM users WHERE (email = 'john@smith.com') LIMIT 1
UPDATE users SET name = 'James', users.updated_at = '2023-10-04 16:14:18'
    WHERE id = 1

 

Delete

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

 

One to One

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->passport

SELECT * 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)

 

One to Many

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)

 

Many to Many

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

 

Aggregate functions

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

 

Miscellaneous

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