Загрузка...

SQL-запросы Eloquent ORM

laravel cover
Laravel 13 минут 41 0

Eloquent ORM предоставляет удобный и интуитивно понятный способ работы с базами данных в Laravel, позволяя разработчикам создавать SQL-запросы с помощью простых и лаконичных методов. Это значительно упрощает процесс взаимодействия с данными, делая код более читаемым и поддерживаемым.

Основные возможности Eloquent ORM

  • Создание запросов: Eloquent позволяет легко создавать запросы к базе данных с помощью методов, таких как get(), first(), find(), и других. Это позволяет разработчикам быстро извлекать данные без необходимости писать сложные SQL-запросы вручную.

  • Работа с отношениями: Eloquent поддерживает отношения между моделями, такие как hasMany, belongsTo, и belongsToMany. Это позволяет легко загружать связанные данные, используя методы, такие как with() и whereHas().

  • Агрегация данных: С помощью Eloquent можно выполнять агрегатные функции, такие как count(), max(), min(), и sum(), что упрощает анализ данных и получение статистики.

  • Фильтрация и сортировка: Eloquent предоставляет мощные инструменты для фильтрации и сортировки данных с помощью методов where(), orWhere(), orderBy(), и других, что позволяет легко находить нужные записи.

  • Создание и обновление записей: С помощью методов create(), update(), и delete() можно легко управлять записями в базе данных, что делает процесс работы с данными более эффективным.

SQL-запросы, созданные с помощью Eloquent ORM:

 

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

https://github.com/alexeymezenin/eloquent-sql-reference/blob/main/README.md

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *