Database: Query Builder

The Query Builder provides a fluent, convenient interface for creating and running database queries. It can be used to perform most database operations in your application and works seamlessly with the Model class.

Introduction

The Ecommerce\Core\Database\Query\QueryBuilder class is accessed through Model classes, providing an elegant, chainable API for building SQL queries. Instead of writing raw SQL, you can use intuitive methods that are automatically compiled into optimized SQL statements.

All examples in this documentation use Model-based syntax (e.g., Product::where()) which is the recommended approach for working with the Query Builder.

Basic Queries

Select Columns

Specify which columns to retrieve from the database.

Description:

The select() method allows you to specify which columns should be returned from the database query. You can pass column names as an array or as variadic arguments. If no columns are specified, all columns (*) are selected by default.

Method Signature:

select(array|string $columns = ['*']): QueryBuilder

Examples:

// Select specific columns
$products = Product::select(['id', 'name', 'price'])->get();

// Variadic syntax
$products = Product::select('id', 'name', 'price')->get();

// Select all columns (default)
$products = Product::select()->get();

SQL Output:

SELECT `id`, `name`, `price` FROM `products`
SELECT `id`, `name`, `price` FROM `products`
SELECT * FROM `products`

Select Raw Expression

Add a raw SELECT clause to the query for complex expressions.

Description:

The select_raw() method allows you to add raw SQL expressions to your SELECT clause. This is useful for database functions, calculations, or any SQL that cannot be expressed using the fluent query builder methods.

Method Signature:

select_raw(string $expression, array $bindings = []): QueryBuilder

Examples:

// Calculate total with tax
$products = Product::select_raw('price * 1.15 as price_with_tax')->get();

// Use database functions
$products = Product::select_raw('UPPER(name) as uppercase_name')->get();

// With bindings for security
$products = Product::select_raw('price * ? as discounted_price', [0.9])->get();

SQL Output:

SELECT price * 1.15 as price_with_tax FROM `products`
SELECT UPPER(name) as uppercase_name FROM `products`
SELECT price * 0.9 as discounted_price FROM `products`

WHERE Clauses

Basic WHERE Clause

Add a WHERE condition to filter query results.

Description:

The where() method adds a WHERE clause to your query. It accepts a column name, an operator, and a value. The operator can be any valid SQL comparison operator like =, >, <, >=, <=, !=, <>, LIKE, etc.

Method Signature:

where(string $column, string $operator, mixed $value): QueryBuilder

Examples:

// Equality check
$products = Product::where('status', '=', 'active')->get();

// Greater than comparison
$products = Product::where('price', '>', 100)->get();

// Chain multiple WHERE clauses (AND logic)
$products = Product::where('status', '=', 'active')
    ->where('price', '>', 100)
    ->get();

SQL Output:

SELECT * FROM `products` WHERE `status` = 'active'
SELECT * FROM `products` WHERE `price` > 100
SELECT * FROM `products` WHERE `status` = 'active' AND `price` > 100

OR WHERE Clause

Add an OR WHERE condition to the query.

Description:

The or_where() method adds an OR WHERE clause to your query, allowing you to retrieve records that match either condition.

Method Signature:

or_where(string $column, string $operator, mixed $value): QueryBuilder

Examples:

// Get products that are either active or pending
$products = Product::where('status', '=', 'active')
    ->or_where('status', '=', 'pending')
    ->get();

// Combine with other conditions
$products = Product::where('price', '>', 100)
    ->or_where('featured', '=', 1)
    ->get();

SQL Output:

SELECT * FROM `products` WHERE `status` = 'active' OR `status` = 'pending'
SELECT * FROM `products` WHERE `price` > 100 OR `featured` = 1

WHERE IN Clause

Filter results where a column's value is in a given array.

Description:

The where_in() method adds a WHERE IN clause, which checks if a column's value matches any value in the provided array. This is more efficient than multiple OR conditions.

Method Signature:

where_in(string $column, array $values): QueryBuilder

Examples:

// Get products with specific IDs
$products = Product::where_in('id', [1, 2, 3, 4, 5])->get();

// Get products in specific categories
$products = Product::where_in('category_id', [10, 20, 30])->get();

SQL Output:

SELECT * FROM `products` WHERE `id` IN (1, 2, 3, 4, 5)
SELECT * FROM `products` WHERE `category_id` IN (10, 20, 30)

WHERE NOT IN Clause

Filter results where a column's value is not in a given array.

Description:

The where_not_in() method adds a WHERE NOT IN clause, which checks if a column's value does not match any value in the provided array.

Method Signature:

where_not_in(string $column, array $values): QueryBuilder

Examples:

// Exclude specific statuses
$products = Product::where_not_in('status', ['archived', 'deleted'])->get();

// Exclude specific product IDs
$products = Product::where_not_in('id', [5, 10, 15])->get();

SQL Output:

SELECT * FROM `products` WHERE `status` NOT IN ('archived', 'deleted')
SELECT * FROM `products` WHERE `id` NOT IN (5, 10, 15)

WHERE NULL Clause

Filter results where a column is NULL.

Description:

The where_null() method adds a WHERE NULL clause to check if a column's value is NULL.

Method Signature:

where_null(string $column): QueryBuilder

Examples:

// Get products without a deleted timestamp
$products = Product::where_null('deleted_at')->get();

// Get products without a description
$products = Product::where_null('description')->get();

SQL Output:

SELECT * FROM `products` WHERE `deleted_at` IS NULL
SELECT * FROM `products` WHERE `description` IS NULL

WHERE NOT NULL Clause

Filter results where a column is not NULL.

Description:

The where_not_null() method adds a WHERE NOT NULL clause to check if a column has a value.

Method Signature:

where_not_null(string $column): QueryBuilder

Examples:

// Get products with an email address
$products = Product::where_not_null('email')->get();

// Get products with a featured image
$products = Product::where_not_null('featured_image')->get();

SQL Output:

SELECT * FROM `products` WHERE `email` IS NOT NULL
SELECT * FROM `products` WHERE `featured_image` IS NOT NULL

WHERE BETWEEN Clause

Filter results where a column's value is between two values.

Description:

The where_between() method adds a WHERE BETWEEN clause to check if a column's value falls within a specified range (inclusive).

Method Signature:

where_between(string $column, array $values): QueryBuilder

Examples:

// Get products in a price range
$products = Product::where_between('price', [100, 500])->get();

// Get products created in a date range
$products = Product::where_between('created_at', ['2024-01-01', '2024-12-31'])->get();

SQL Output:

SELECT * FROM `products` WHERE `price` BETWEEN 100 AND 500
SELECT * FROM `products` WHERE `created_at` BETWEEN '2024-01-01' AND '2024-12-31'

WHERE LIKE Clause

Filter results using pattern matching.

Description:

The where_like() method adds a WHERE LIKE clause for pattern matching. Use % as a wildcard to match any sequence of characters.

Method Signature:

where_like(string $column, string $value, bool $case_sensitive = false, string $boolean = 'and', bool $not = false): QueryBuilder

Examples:

// Search for products containing "phone"
$products = Product::where_like('name', '%phone%')->get();

// Search for products starting with "Smart"
$products = Product::where_like('name', 'Smart%')->get();

// Search for products ending with "Pro"
$products = Product::where_like('name', '%Pro')->get();

SQL Output:

SELECT * FROM `products` WHERE `name` LIKE '%phone%'
SELECT * FROM `products` WHERE `name` LIKE 'Smart%'
SELECT * FROM `products` WHERE `name` LIKE '%Pro'

WHERE NOT LIKE Clause

Filter results that don't match a pattern.

Description:

The where_not_like() method adds a WHERE NOT LIKE clause to exclude records matching a pattern.

Method Signature:

where_not_like(string $column, string $value, bool $case_sensitive = false, string $boolean = 'and'): QueryBuilder

Examples:

// Exclude discontinued products
$products = Product::where_not_like('name', '%discontinued%')->get();

// Exclude draft products
$products = Product::where_not_like('status', 'draft%')->get();

SQL Output:

SELECT * FROM `products` WHERE `name` NOT LIKE '%discontinued%'
SELECT * FROM `products` WHERE `status` NOT LIKE 'draft%'

Joins

Inner Join

Join tables using an INNER JOIN.

Description:

The join() method adds an INNER JOIN clause to your query, combining rows from two tables based on a related column. Only rows with matching values in both tables are returned.

Method Signature:

join(string $table, string $first, string $operator, string $second, string $type = 'inner'): QueryBuilder

Examples:

// Join products with categories
$products = Product::join('categories', 'products.category_id', '=', 'categories.id')
    ->select(['products.*', 'categories.name as category_name'])
    ->get();

// Multiple joins
$products = Product::join('categories', 'products.category_id', '=', 'categories.id')
    ->join('brands', 'products.brand_id', '=', 'brands.id')
    ->get();

SQL Output:

SELECT `products`.*, `categories`.`name` AS `category_name`
FROM `products`
INNER JOIN `categories` ON `products`.`category_id` = `categories`.`id`

SELECT * FROM `products`
INNER JOIN `categories` ON `products`.`category_id` = `categories`.`id`
INNER JOIN `brands` ON `products`.`brand_id` = `brands`.`id`

Left Join

Join tables using a LEFT JOIN.

Description:

The left_join() method adds a LEFT JOIN clause, returning all rows from the left table and matching rows from the right table. If there's no match, NULL values are returned for right table columns.

Method Signature:

left_join(string $table, string $first, string $operator, string $second): QueryBuilder

Examples:

// Get all products with their brands (including products without brands)
$products = Product::left_join('brands', 'products.brand_id', '=', 'brands.id')
    ->select(['products.*', 'brands.name as brand_name'])
    ->get();

SQL Output:

SELECT `products`.*, `brands`.`name` AS `brand_name`
FROM `products`
LEFT JOIN `brands` ON `products`.`brand_id` = `brands`.`id`

Right Join

Join tables using a RIGHT JOIN.

Description:

The right_join() method adds a RIGHT JOIN clause, returning all rows from the right table and matching rows from the left table. If there's no match, NULL values are returned for left table columns.

Method Signature:

right_join(string $table, string $first, string $operator, string $second): QueryBuilder

Examples:

// Get all suppliers with their products (including suppliers without products)
$products = Product::right_join('suppliers', 'products.supplier_id', '=', 'suppliers.id')
    ->get();

SQL Output:

SELECT * FROM `products`
RIGHT JOIN `suppliers` ON `products`.`supplier_id` = `suppliers`.`id`

Ordering & Grouping

Order By

Sort query results by one or more columns.

Description:

The order_by() method adds an ORDER BY clause to sort your query results. You can specify ascending (asc) or descending (desc) order.

Method Signature:

order_by(string $column, string $direction = 'asc'): QueryBuilder

Examples:

// Sort by created date, newest first
$products = Product::order_by('created_at', 'desc')->get();

// Sort by name alphabetically
$products = Product::order_by('name', 'asc')->get();

// Multiple sorting criteria
$products = Product::order_by('category_id', 'asc')
    ->order_by('price', 'desc')
    ->get();

SQL Output:

SELECT * FROM `products` ORDER BY `created_at` DESC
SELECT * FROM `products` ORDER BY `name` ASC
SELECT * FROM `products` ORDER BY `category_id` ASC, `price` DESC

Group By

Group query results by one or more columns.

Description:

The group_by() method adds a GROUP BY clause to group rows that have the same values in specified columns. This is typically used with aggregate functions.

Method Signature:

group_by(string|array $columns): QueryBuilder

Examples:

// Group by category
$products = Product::group_by('category_id')->get();

// Group by multiple columns
$products = Product::group_by(['category_id', 'brand_id'])->get();

SQL Output:

SELECT * FROM `products` GROUP BY `category_id`
SELECT * FROM `products` GROUP BY `category_id`, `brand_id`

Having

Add a HAVING clause to filter grouped results.

Description:

The having() method adds a HAVING clause to filter the results of a GROUP BY. Unlike WHERE, HAVING can use aggregate functions.

Method Signature:

having(string $column, string $operator, mixed $value): QueryBuilder

Examples:

// Get categories with more than 5 products
$categories = Product::select_raw('category_id, COUNT(*) as product_count')
    ->group_by('category_id')
    ->having('COUNT(*)', '>', 5)
    ->get();

// Get brands with average price over 100
$brands = Product::select_raw('brand_id, AVG(price) as avg_price')
    ->group_by('brand_id')
    ->having('AVG(price)', '>', 100)
    ->get();

SQL Output:

SELECT category_id, COUNT(*) as product_count
FROM `products`
GROUP BY `category_id`
HAVING COUNT(*) > 5

SELECT brand_id, AVG(price) as avg_price
FROM `products`
GROUP BY `brand_id`
HAVING AVG(price) > 100

Limiting Results

Limit

Limit the number of results returned.

Description:

The limit() method sets the maximum number of records to return from the query.

Method Signature:

limit(int $limit): QueryBuilder

Examples:

// Get only 10 products
$products = Product::limit(10)->get();

// Get top 5 most expensive products
$products = Product::order_by('price', 'desc')->limit(5)->get();

SQL Output:

SELECT * FROM `products` LIMIT 10
SELECT * FROM `products` ORDER BY `price` DESC LIMIT 5

Offset

Skip a specified number of results.

Description:

The offset() method skips a specified number of records before returning results. This is commonly used with limit() for pagination.

Method Signature:

offset(int $offset): QueryBuilder

Examples:

// Skip first 20 products
$products = Product::offset(20)->get();

// Get products 11-20
$products = Product::offset(10)->limit(10)->get();

SQL Output:

SELECT * FROM `products` OFFSET 20
SELECT * FROM `products` LIMIT 10 OFFSET 10

Skip

Alias for offset - skip a specified number of results.

Description:

The skip() method is an alias for offset(), providing a more readable alternative.

Method Signature:

skip(int $offset): QueryBuilder

Examples:

// Skip first 20 products
$products = Product::skip(20)->get();

SQL Output:

SELECT * FROM `products` OFFSET 20

Take

Alias for limit - take a specified number of results.

Description:

The take() method is an alias for limit(), providing a more readable alternative.

Method Signature:

take(int $limit): QueryBuilder

Examples:

// Take only 10 products
$products = Product::take(10)->get();

SQL Output:

SELECT * FROM `products` LIMIT 10

Aggregates

Count

Get the count of results.

Description:

The count() method returns the number of records matching the query. You can optionally specify a column to count non-NULL values.

Method Signature:

count(string $column = '*'): int

Examples:

// Count all products
$count = Product::count();

// Count active products
$count = Product::where('status', '=', 'active')->count();

// Count non-NULL email addresses
$count = Product::count('email');

SQL Output:

SELECT COUNT(*) FROM `products`
SELECT COUNT(*) FROM `products` WHERE `status` = 'active'
SELECT COUNT(`email`) FROM `products`

Sum

Get the sum of a column's values.

Description:

The sum() method returns the sum of all values in a specified numeric column.

Method Signature:

sum(string $column): mixed

Examples:

// Get total value of all products
$total = Product::sum('price');

// Get total quantity in stock
$total_stock = Product::where('status', '=', 'active')->sum('stock_quantity');

SQL Output:

SELECT SUM(`price`) FROM `products`
SELECT SUM(`stock_quantity`) FROM `products` WHERE `status` = 'active'

Average

Get the average of a column's values.

Description:

The avg() method returns the average value of a specified numeric column.

Method Signature:

avg(string $column): mixed

Examples:

// Get average product price
$average = Product::avg('price');

// Get average rating for active products
$average_rating = Product::where('status', '=', 'active')->avg('rating');

SQL Output:

SELECT AVG(`price`) FROM `products`
SELECT AVG(`rating`) FROM `products` WHERE `status` = 'active'

Minimum

Get the minimum value of a column.

Description:

The min() method returns the smallest value in a specified column.

Method Signature:

min(string $column): mixed

Examples:

// Get lowest product price
$min_price = Product::min('price');

// Get earliest creation date
$earliest = Product::min('created_at');

SQL Output:

SELECT MIN(`price`) FROM `products`
SELECT MIN(`created_at`) FROM `products`

Maximum

Get the maximum value of a column.

Description:

The max() method returns the largest value in a specified column.

Method Signature:

max(string $column): mixed

Examples:

// Get highest product price
$max_price = Product::max('price');

// Get most recent update
$latest = Product::max('updated_at');

SQL Output:

SELECT MAX(`price`) FROM `products`
SELECT MAX(`updated_at`) FROM `products`

Executing Queries

Get All Results

Execute the query and retrieve all matching records.

Description:

The get() method executes the query and returns a Collection of all matching records. You can optionally specify which columns to retrieve.

Method Signature:

get(array $columns = ['*']): Collection

Examples:

// Get all products
$products = Product::get();

// Get specific columns
$products = Product::get(['id', 'name', 'price']);

// Get filtered results
$products = Product::where('status', '=', 'active')->get();

SQL Output:

SELECT * FROM `products`
SELECT `id`, `name`, `price` FROM `products`
SELECT * FROM `products` WHERE `status` = 'active'

Get First Result

Execute the query and retrieve the first matching record.

Description:

The first() method executes the query and returns only the first matching record, or NULL if no records are found.

Method Signature:

first(): Model|null

Examples:

// Get first product
$product = Product::first();

// Get first active product
$product = Product::where('status', '=', 'active')->first();

// Get first product ordered by price
$cheapest = Product::order_by('price', 'asc')->first();

SQL Output:

SELECT * FROM `products` LIMIT 1
SELECT * FROM `products` WHERE `status` = 'active' LIMIT 1
SELECT * FROM `products` ORDER BY `price` ASC LIMIT 1

Find by ID

Find a record by its primary key.

Description:

The find() method retrieves a single record by its primary key value. You can optionally specify a different column to search by.

Method Signature:

find(mixed $id, string $column = 'id'): Model|null

Examples:

// Find product by ID
$product = Product::find(1);

// Find product by slug
$product = Product::find('awesome-product', 'slug');

// Find with additional conditions
$product = Product::where('status', '=', 'active')->find(1);

SQL Output:

SELECT * FROM `products` WHERE `id` = 1 LIMIT 1
SELECT * FROM `products` WHERE `slug` = 'awesome-product' LIMIT 1
SELECT * FROM `products` WHERE `status` = 'active' AND `id` = 1 LIMIT 1

First or Fail

Get the first result or throw an exception.

Description:

The first_or_fail() method retrieves the first matching record or throws an exception if no records are found. This is useful when you expect a record to exist.

Method Signature:

first_or_fail(): Model

Examples:

// Get product or fail
$product = Product::where('slug', '=', 'product-name')->first_or_fail();

// Will throw exception if not found
$product = Product::where('id', '=', 999)->first_or_fail();

SQL Output:

SELECT * FROM `products` WHERE `slug` = 'product-name' LIMIT 1
SELECT * FROM `products` WHERE `id` = 999 LIMIT 1

Check Existence

Check if any records exist matching the query.

Description:

The exists() method returns a boolean indicating whether any records match the query conditions.

Method Signature:

exists(): bool

Examples:

// Check if any products exist
if (Product::exists()) {
    // Products exist
}

// Check if email is already taken
if (Product::where('email', '=', 'user@example.com')->exists()) {
    // Email exists
}

SQL Output:

SELECT EXISTS(SELECT * FROM `products`) AS `exists`
SELECT EXISTS(SELECT * FROM `products` WHERE `email` = 'user@example.com') AS `exists`

Check Non-Existence

Check if no records exist matching the query.

Description:

The does_not_exists() method returns a boolean indicating whether no records match the query conditions. It's the inverse of exists().

Method Signature:

does_not_exists(): bool

Examples:

// Check if email is available
if (Product::where('email', '=', 'user@example.com')->does_not_exists()) {
    // Email is available
}

SQL Output:

SELECT EXISTS(SELECT * FROM `products` WHERE `email` = 'user@example.com') AS `exists`

Get Single Column Value

Get a single column value from the first result.

Description:

The value() method retrieves a single column value from the first matching record. This is more efficient than retrieving the entire record when you only need one field.

Method Signature:

value(string $column): mixed

Examples:

// Get product name
$name = Product::where('id', '=', 1)->value('name');

// Get highest price
$max_price = Product::order_by('price', 'desc')->value('price');

SQL Output:

SELECT `name` FROM `products` WHERE `id` = 1 LIMIT 1
SELECT `price` FROM `products` ORDER BY `price` DESC LIMIT 1

Data Manipulation

Insert Record

Insert a new record into the database.

Description:

The insert() method inserts a new record with the specified column values. It returns a boolean indicating success or failure.

Method Signature:

insert(array $values): bool

Examples:

// Insert a new product
Product::insert([
    'name' => 'New Product',
    'price' => 99.99,
    'status' => 'active',
]);

// Insert with timestamps
Product::insert([
    'name' => 'Another Product',
    'price' => 149.99,
    'created_at' => date('Y-m-d H:i:s'),
]);

SQL Output:

INSERT INTO `products` (`name`, `price`, `status`)
VALUES ('New Product', 99.99, 'active')

INSERT INTO `products` (`name`, `price`, `created_at`)
VALUES ('Another Product', 149.99, '2024-01-22 12:00:00')

Insert and Get ID

Insert a new record and retrieve its ID.

Description:

The insert_get_id() method inserts a new record and returns the auto-generated primary key value.

Method Signature:

insert_get_id(array $values): int

Examples:

// Insert and get the new product ID
$id = Product::insert_get_id([
    'name' => 'New Product',
    'price' => 99.99,
    'status' => 'active',
]);

echo "New product ID: " . $id;

SQL Output:

INSERT INTO `products` (`name`, `price`, `status`)
VALUES ('New Product', 99.99, 'active')
-- Returns the last inserted ID

Update Records

Update existing records matching the query.

Description:

The update() method updates all records matching the query conditions with the specified values. It returns a boolean indicating success or failure.

Method Signature:

update(array $values): bool

Examples:

// Update a specific product
Product::where('id', '=', 1)->update([
    'price' => 149.99,
]);

// Update multiple products
Product::where('status', '=', 'draft')->update([
    'status' => 'active',
]);

// Update with timestamp
Product::where('id', '=', 1)->update([
    'price' => 149.99,
    'updated_at' => date('Y-m-d H:i:s'),
]);

SQL Output:

UPDATE `products` SET `price` = 149.99 WHERE `id` = 1
UPDATE `products` SET `status` = 'active' WHERE `status` = 'draft'
UPDATE `products` SET `price` = 149.99, `updated_at` = '2024-01-22 12:00:00' WHERE `id` = 1

Delete Records

Delete records matching the query.

Description:

The delete() method removes all records matching the query conditions. It returns a boolean indicating success or failure.

Method Signature:

delete(): bool

Examples:

// Delete a specific product
Product::where('id', '=', 1)->delete();

// Delete all archived products
Product::where('status', '=', 'archived')->delete();

// Delete old products
Product::where('created_at', '<', '2020-01-01')->delete();

SQL Output:

DELETE FROM `products` WHERE `id` = 1
DELETE FROM `products` WHERE `status` = 'archived'
DELETE FROM `products` WHERE `created_at` < '2020-01-01'

Pagination

Paginate Results

Paginate query results with metadata.

Description:

The paginate() method divides query results into pages and returns both the data and pagination metadata (total count, current page, per page, etc.). This is useful for building paginated interfaces.

Method Signature:

paginate(int $per_page = 15, array $columns = ['*'], string $page_name = 'page', int $page = null): array

Examples:

// Paginate with default settings (15 per page)
$result = Product::where('status', '=', 'active')->paginate();

// Custom page size
$result = Product::paginate(10);

// Paginate with specific columns
$result = Product::paginate(20, ['id', 'name', 'price']);

// Access pagination data
echo "Total: " . $result['total'];
echo "Current Page: " . $result['current_page'];
echo "Per Page: " . $result['per_page'];

SQL Output:

-- Count query
SELECT COUNT(*) FROM `products` WHERE `status` = 'active'

-- Data query
SELECT * FROM `products` WHERE `status` = 'active' LIMIT 15 OFFSET 0

Utility Methods

Distinct

Select distinct/unique rows.

Description:

The distinct() method ensures that only unique rows are returned, eliminating duplicates based on the selected columns.

Method Signature:

distinct(array|bool $columns = []): QueryBuilder

Examples:

// Get distinct category IDs
$categories = Product::distinct()->select('category_id')->get();

// Get distinct combinations
$combinations = Product::distinct()->select(['category_id', 'brand_id'])->get();

SQL Output:

SELECT DISTINCT `category_id` FROM `products`
SELECT DISTINCT `category_id`, `brand_id` FROM `products`

Complex Query Example

Here's a comprehensive example combining multiple Query Builder methods:

$products = Product::select(['products.id', 'products.name', 'products.price', 'categories.name as category_name'])
    ->join('categories', 'products.category_id', '=', 'categories.id')
    ->where('products.status', '=', 'active')
    ->where('products.price', '>', 100)
    ->where_in('products.brand_id', [1, 2, 3])
    ->where_not_null('products.featured_image')
    ->order_by('products.created_at', 'desc')
    ->limit(10)
    ->get();

SQL Output:

SELECT `products`.`id`, `products`.`name`, `products`.`price`, `categories`.`name` AS `category_name`
FROM `products`
INNER JOIN `categories` ON `products`.`category_id` = `categories`.`id`
WHERE `products`.`status` = 'active'
  AND `products`.`price` > 100
  AND `products`.`brand_id` IN (1, 2, 3)
  AND `products`.`featured_image` IS NOT NULL
ORDER BY `products`.`created_at` DESC
LIMIT 10

Defining Relationships

Before you can query relationships, you need to define them in your Model classes. The Query Builder provides four methods to define different types of relationships between models.

Define One-to-One Relationship

Define a one-to-one relationship where the parent model has one related model.

Description:

The has_one() method defines a one-to-one relationship where the current model has exactly one instance of another model. The related model's table contains a foreign key that references the parent model's primary key. This is useful for relationships like "User has one Profile" or "Order has one Invoice".

Method Signature:

protected function has_one(string $related, string $foreign_key = null, string $local_key = null): HasOne

Parameters:

$related - The fully qualified class name of the related model
$foreign_key - (Optional) The foreign key column on the related model's table. Defaults to {parent_model}_id
$local_key - (Optional) The local key column on the parent model. Defaults to the primary key

Examples:

// In Order model - Order has one CouponUsage
public function coupon_usage()
{
    return $this->has_one(CouponUsage::class, 'order_id');
}

// Accessing the relationship
$order = Order::find(1);
$couponUsage = $order->coupon_usage;

// With custom foreign key
public function invoice()
{
    return $this->has_one(Invoice::class, 'order_id');
}

// With custom local key
public function latest_payment()
{
    return $this->has_one(Payment::class, 'order_uuid', 'uuid');
}

// Eager loading
$orders = Order::with('coupon_usage')->get();
foreach ($orders as $order) {
    if ($order->coupon_usage) {
        echo $order->coupon_usage->coupon_code;
    }
}

SQL Output:

-- Lazy loading: $order->coupon_usage
SELECT * FROM `droip_ecommerce_coupon_usage`
WHERE `order_id` = 1

-- Eager loading: Order::with('coupon_usage')->get()
-- Main query
SELECT * FROM `droip_ecommerce_orders`

-- Relationship query
SELECT * FROM `droip_ecommerce_coupon_usage`
WHERE `order_id` IN (1, 2, 3, ...)

Define One-to-Many Relationship

Define a one-to-many relationship where the parent model has many related models.

Description:

The has_many() method defines a one-to-many relationship where the current model can have multiple instances of another model. The related model's table contains a foreign key that references the parent model's primary key. This is useful for relationships like "Product has many Variants" or "Order has many Items".

Method Signature:

protected function has_many(string $related, string $foreign_key = null, string $local_key = null): HasMany

Parameters:

$related - The fully qualified class name of the related model
$foreign_key - (Optional) The foreign key column on the related model's table. Defaults to {parent_model}_id
$local_key - (Optional) The local key column on the parent model. Defaults to the primary key

Examples:

// In Product model - Product has many Variants
public function variants()
{
    return $this->has_many(Variant::class, 'product_id', 'id');
}

// In Order model - Order has many OrderItems
public function items()
{
    return $this->has_many(OrderItem::class, 'order_id');
}

// With custom foreign key
public function reviews()
{
    return $this->has_many(Review::class, 'product_id');
}

// With custom local key
public function orders_by_uuid()
{
    return $this->has_many(Order::class, 'customer_uuid', 'uuid');
}

// Accessing the relationship
$product = Product::find(1);
$variants = $product->variants; // Returns a Collection

foreach ($variants as $variant) {
    echo $variant->sku;
}

// Eager loading
$products = Product::with('variants')->get();

// Eager loading with constraints
$products = Product::with(['variants' => function ($query) {
    $query->where('stock', '>', 0)->order_by('price', 'asc');
}])->get();

SQL Output:

-- Lazy loading: $product->variants
SELECT * FROM `droip_ecommerce_variants`
WHERE `product_id` = 1

-- Eager loading: Product::with('variants')->get()
-- Main query
SELECT * FROM `droip_ecommerce_products`

-- Relationship query
SELECT * FROM `droip_ecommerce_variants`
WHERE `product_id` IN (1, 2, 3, ...)

Define Inverse One-to-One or Many Relationship

Define the inverse of a one-to-one or one-to-many relationship.

Description:

The belongs_to() method defines the inverse side of a one-to-one or one-to-many relationship. The current model's table contains a foreign key that references another model's primary key. This is useful for relationships like "Product belongs to Brand" or "Order belongs to Customer".

Method Signature:

protected function belongs_to(string $related, string $foreign_key = null, string $owner_key = null): BelongsTo

Parameters:

$related - The fully qualified class name of the related (owner) model
$foreign_key - (Optional) The foreign key column on the current model's table. Defaults to {related_model}_id
$owner_key - (Optional) The key column on the related model. Defaults to the primary key

Examples:

// In Product model - Product belongs to Brand
public function brand()
{
    return $this->belongs_to(Brand::class);
}

// In Product model - Product belongs to Currency
public function currency()
{
    return $this->belongs_to(Currency::class);
}

// In Order model - Order belongs to Customer
public function customer()
{
    return $this->belongs_to(Customer::class, 'customer_id');
}

// With custom foreign key and owner key
public function parent_category()
{
    return $this->belongs_to(Category::class, 'parent_id', 'id');
}

// Accessing the relationship
$product = Product::find(1);
$brand = $product->brand;
echo $brand->name;

// Eager loading
$products = Product::with('brand')->get();

// Eager loading multiple relationships
$products = Product::with(['brand', 'currency'])->get();
foreach ($products as $product) {
    echo $product->brand->name . ' - ' . $product->currency->code;
}

SQL Output:

-- Lazy loading: $product->brand
SELECT * FROM `droip_ecommerce_brands`
WHERE `id` = 5

-- Eager loading: Product::with('brand')->get()
-- Main query
SELECT * FROM `droip_ecommerce_products`

-- Relationship query
SELECT * FROM `droip_ecommerce_brands`
WHERE `id` IN (1, 2, 3, 5, ...)

Define Many-to-Many Relationship

Define a many-to-many relationship using a pivot (junction) table.

Description:

The belongs_to_many() method defines a many-to-many relationship where both models can have multiple instances of each other. This requires a pivot table that contains foreign keys for both models. This is useful for relationships like "Product belongs to many Categories" or "User belongs to many Roles".

Method Signature:

protected function belongs_to_many(
    string $related,
    string $pivot_table = null,
    string $foreign_pivot_key = null,
    string $related_pivot_key = null,
    string $parent_key = null,
    string $related_key = null
): BelongsToMany

Parameters:

$related - The fully qualified class name of the related model
$pivot_table - (Optional) The pivot table name. Defaults to alphabetically sorted table names joined with underscore
$foreign_pivot_key - (Optional) The foreign key for the current model in the pivot table. Defaults to {current_model}_id
$related_pivot_key - (Optional) The foreign key for the related model in the pivot table. Defaults to {related_model}_id
$parent_key - (Optional) The key on the current model. Defaults to the primary key
$related_key - (Optional) The key on the related model. Defaults to the primary key

Examples:

// In Product model - Product belongs to many Categories
public function categories()
{
    return $this->belongs_to_many(
        Category::class,
        'droip_ecommerce_category_product',
        'product_id',
        'category_id'
    );
}

// In Product model - Product belongs to many Tags
public function tags()
{
    return $this->belongs_to_many(
        Tag::class,
        'droip_ecommerce_product_tags',
        'product_id',
        'tag_id'
    );
}

// Accessing the relationship
$product = Product::find(1);
$categories = $product->categories; // Returns a Collection

foreach ($categories as $category) {
    echo $category->name;
}

// Eager loading
$products = Product::with('categories')->get();

// With additional pivot columns
public function collections()
{
    return $this->belongs_to_many(
        Collection::class,
        'droip_ecommerce_collection_product',
        'product_id',
        'collection_id'
    )->with_pivot('position', 'featured');
}

// Accessing pivot data
$product = Product::with('collections')->first();
foreach ($product->collections as $collection) {
    echo $collection->name;
    echo $collection->pivot['position'];
    echo $collection->pivot['featured'];
}

// Ordering by pivot column
public function media()
{
    return $this->belongs_to_many(
        Post::class,
        'droip_ecommerce_media_product',
        'product_id',
        'media_id'
    )->order_by_pivot('ordering', 'asc');
}

// Filtering by pivot conditions
$products = Product::with(['categories' => function ($query) {
    $query->where_pivot('featured', '=', true);
}])->get();

SQL Output:

-- Lazy loading: $product->categories
SELECT `droip_ecommerce_categories`.*,
       `droip_ecommerce_category_product`.`product_id` AS `pivot_product_id`,
       `droip_ecommerce_category_product`.`category_id` AS `pivot_category_id`
FROM `droip_ecommerce_categories`
INNER JOIN `droip_ecommerce_category_product`
  ON `droip_ecommerce_categories`.`id` = `droip_ecommerce_category_product`.`category_id`
WHERE `droip_ecommerce_category_product`.`product_id` = 1

-- Eager loading: Product::with('categories')->get()
-- Main query
SELECT * FROM `droip_ecommerce_products`

-- Relationship query
SELECT `droip_ecommerce_categories`.*,
       `droip_ecommerce_category_product`.`product_id` AS `pivot_product_id`,
       `droip_ecommerce_category_product`.`category_id` AS `pivot_category_id`
FROM `droip_ecommerce_categories`
INNER JOIN `droip_ecommerce_category_product`
  ON `droip_ecommerce_categories`.`id` = `droip_ecommerce_category_product`.`category_id`
WHERE `droip_ecommerce_category_product`.`product_id` IN (1, 2, 3, ...)

Relationship Helper Methods

After defining relationships, you can use various helper methods to create, associate, and manage related records.

Create new related models through a has_many relationship.

Description:

The create() method allows you to create a new related model instance and automatically sets the foreign key to link it to the parent model. This is a convenient way to create child records without manually setting the foreign key.

Method Signature:

public function create(array $attributes): Model

Parameters:

$attributes - Array of attributes for the new related model

Examples:

// Create a single variant for a product
$product = Product::find(1);
$variant = $product->variants()->create([
    'sku' => 'PROD-001-RED',
    'price' => 2999,
    'stock' => 100,
    'color' => 'Red'
]);

// The foreign key is automatically set
echo $variant->product_id; // 1

// Create an order item
$order = Order::find(1);
$item = $order->items()->create([
    'product_id' => 5,
    'quantity' => 2,
    'price' => 1999,
    'total' => 3998
]);

SQL Output:

INSERT INTO `droip_ecommerce_variants`
(`product_id`, `sku`, `price`, `stock`, `color`, `created_at`, `updated_at`)
VALUES (1, 'PROD-001-RED', 2999, 100, 'Red', '2026-01-23 12:00:00', '2026-01-23 12:00:00')

Create multiple related models at once through a has_many relationship.

Description:

The create_many() method allows you to create multiple related model instances in a single call. Each record will have its foreign key automatically set to link it to the parent model.

Method Signature:

public function create_many(array $records): array

Parameters:

$records - Array of attribute arrays for the new related models

Examples:

// Create multiple variants for a product
$product = Product::find(1);
$variants = $product->variants()->create_many([
    ['sku' => 'PROD-001-BLUE', 'price' => 2999, 'stock' => 50, 'color' => 'Blue'],
    ['sku' => 'PROD-001-GREEN', 'price' => 2999, 'stock' => 75, 'color' => 'Green'],
    ['sku' => 'PROD-001-BLACK', 'price' => 3499, 'stock' => 25, 'color' => 'Black']
]);

// Returns an array of created models
echo count($variants); // 3

// Create multiple order items
$order = Order::find(1);
$items = $order->items()->create_many([
    ['product_id' => 5, 'quantity' => 2, 'price' => 1999],
    ['product_id' => 8, 'quantity' => 1, 'price' => 4999],
    ['product_id' => 12, 'quantity' => 3, 'price' => 999]
]);

SQL Output:

-- Each record is inserted separately
INSERT INTO `droip_ecommerce_variants`
(`product_id`, `sku`, `price`, `stock`, `color`, `created_at`, `updated_at`)
VALUES (1, 'PROD-001-BLUE', 2999, 50, 'Blue', '2026-01-23 12:00:00', '2026-01-23 12:00:00')

INSERT INTO `droip_ecommerce_variants`
(`product_id`, `sku`, `price`, `stock`, `color`, `created_at`, `updated_at`)
VALUES (1, 'PROD-001-GREEN', 2999, 75, 'Green', '2026-01-23 12:00:00', '2026-01-23 12:00:00')

INSERT INTO `droip_ecommerce_variants`
(`product_id`, `sku`, `price`, `stock`, `color`, `created_at`, `updated_at`)
VALUES (1, 'PROD-001-BLACK', 3499, 25, 'Black', '2026-01-23 12:00:00', '2026-01-23 12:00:00')

Associate a parent model with a related model in a belongs_to relationship.

Description:

The associate() method sets the foreign key on the parent model to link it to the related model. This is useful when you want to change which related model a parent belongs to. Note: You must call save() on the parent model after associating to persist the change to the database.

Method Signature:

public function associate(Model $model): Model

Parameters:

$model - The related model instance to associate with

Examples:

// Associate a product with a brand
$product = Product::find(1);
$brand = Brand::find(5);

$product->brand()->associate($brand);
$product->save(); // Must save to persist

// The foreign key is now set
echo $product->brand_id; // 5

// Associate an order with a customer
$order = Order::find(10);
$customer = Customer::find(25);

$order->customer()->associate($customer);
$order->save();

// You can also chain it
Product::find(1)
    ->brand()->associate(Brand::find(5))
    ->save();

SQL Output:

UPDATE `droip_ecommerce_products`
SET `brand_id` = 5, `updated_at` = '2026-01-23 12:00:00'
WHERE `id` = 1

Remove the association between a parent model and its related model in a belongs_to relationship.

Description:

The dissociate() method sets the foreign key on the parent model to NULL, effectively removing the relationship. Note: You must call save() on the parent model after dissociating to persist the change to the database.

Method Signature:

public function dissociate(): Model

Examples:

// Dissociate a product from its brand
$product = Product::find(1);
$product->brand()->dissociate();
$product->save(); // Must save to persist

// The foreign key is now NULL
echo $product->brand_id; // NULL

// Dissociate an order from its customer
$order = Order::find(10);
$order->customer()->dissociate();
$order->save();

// You can also chain it
Product::find(1)
    ->brand()->dissociate()
    ->save();

SQL Output:

UPDATE `droip_ecommerce_products`
SET `brand_id` = NULL, `updated_at` = '2026-01-23 12:00:00'
WHERE `id` = 1

Attach related models in a belongs_to_many relationship by creating pivot table records.

Description:

The attach() method creates records in the pivot table to establish many-to-many relationships. You can attach single or multiple related models, and optionally include additional pivot table data.

Method Signature:

public function attach(mixed $id, array $attributes = []): void

Parameters:

$id - The related model ID, array of IDs, or array mapping IDs to pivot attributes
$attributes - (Optional) Additional pivot table attributes to store

Examples:

$product = Product::find(1);

// Attach a single category
$product->categories()->attach(5);

// Attach with additional pivot data
$product->categories()->attach(5, ['featured' => true, 'position' => 1]);

// Attach multiple categories
$product->categories()->attach([3, 5, 7]);

// Attach multiple with different pivot data for each
$product->categories()->attach([
    3 => ['position' => 1, 'featured' => false],
    5 => ['position' => 2, 'featured' => true],
    7 => ['position' => 3, 'featured' => false]
]);

// Attach tags to a product
$product->tags()->attach([10, 15, 20]);

SQL Output:

-- Single attach
INSERT INTO `droip_ecommerce_category_product`
(`product_id`, `category_id`, `created_at`, `updated_at`)
VALUES (1, 5, '2026-01-23 12:00:00', '2026-01-23 12:00:00')

-- Attach with pivot data
INSERT INTO `droip_ecommerce_category_product`
(`product_id`, `category_id`, `featured`, `position`, `created_at`, `updated_at`)
VALUES (1, 5, 1, 1, '2026-01-23 12:00:00', '2026-01-23 12:00:00')

-- Multiple attach
INSERT INTO `droip_ecommerce_category_product`
(`product_id`, `category_id`, `created_at`, `updated_at`)
VALUES (1, 3, '2026-01-23 12:00:00', '2026-01-23 12:00:00')

INSERT INTO `droip_ecommerce_category_product`
(`product_id`, `category_id`, `created_at`, `updated_at`)
VALUES (1, 5, '2026-01-23 12:00:00', '2026-01-23 12:00:00')

INSERT INTO `droip_ecommerce_category_product`
(`product_id`, `category_id`, `created_at`, `updated_at`)
VALUES (1, 7, '2026-01-23 12:00:00', '2026-01-23 12:00:00')

Remove relationships in a belongs_to_many relationship by deleting pivot table records.

Description:

The detach() method removes records from the pivot table to break many-to-many relationships. You can detach specific related models, multiple models, or all related models.

Method Signature:

public function detach(mixed $ids = null): int

Parameters:

$ids - (Optional) The related model ID, array of IDs, or null to detach all

Returns:

Number of pivot records deleted

Examples:

$product = Product::find(1);

// Detach a specific category
$product->categories()->detach(5);

// Detach multiple categories
$product->categories()->detach([3, 5, 7]);

// Detach all categories
$product->categories()->detach();

// Get the count of detached records
$count = $product->tags()->detach([10, 15]);
echo "Detached {$count} tags";

SQL Output:

-- Detach specific
DELETE FROM `droip_ecommerce_category_product`
WHERE `product_id` = 1 AND `category_id` = 5

-- Detach multiple
DELETE FROM `droip_ecommerce_category_product`
WHERE `product_id` = 1 AND `category_id` IN (3, 5, 7)

-- Detach all
DELETE FROM `droip_ecommerce_category_product`
WHERE `product_id` = 1

Synchronize the pivot table to match a given set of IDs in a belongs_to_many relationship.

Description:

The sync() method synchronizes the pivot table with the given array of IDs. It will attach any IDs that don't exist, update existing relationships with new pivot data, and optionally detach any IDs not in the array. This is useful when you want the relationship to match exactly a specific set of related models.

Method Signature:

public function sync(mixed $ids, bool $detaching = true): array

Parameters:

$ids - Array of IDs or array mapping IDs to pivot attributes
$detaching - (Optional) Whether to detach IDs not in the array. Defaults to true

Returns:

Array with keys attached, detached, and updated containing the affected IDs

Examples:

$product = Product::find(1);

// Sync to exactly these categories (detaches others)
$changes = $product->categories()->sync([1, 2, 3]);
// Result: ['attached' => [2, 3], 'detached' => [5, 7], 'updated' => []]

// Sync with pivot data
$product->categories()->sync([
    1 => ['position' => 1, 'featured' => true],
    2 => ['position' => 2, 'featured' => false],
    3 => ['position' => 3, 'featured' => false]
]);

// Sync without detaching (only add new relationships)
$product->categories()->sync([4, 5, 6], false);
// Same as syncWithoutDetaching([4, 5, 6])

// Check what changed
$changes = $product->tags()->sync([10, 15, 20]);
echo "Attached: " . count($changes['attached']);
echo "Detached: " . count($changes['detached']);
echo "Updated: " . count($changes['updated']);

SQL Output:

-- First, detach categories not in the sync list
DELETE FROM `droip_ecommerce_category_product`
WHERE `product_id` = 1 AND `category_id` NOT IN (1, 2, 3)

-- Then, attach new categories
INSERT INTO `droip_ecommerce_category_product`
(`product_id`, `category_id`, `created_at`, `updated_at`)
VALUES (1, 2, '2026-01-23 12:00:00', '2026-01-23 12:00:00')

INSERT INTO `droip_ecommerce_category_product`
(`product_id`, `category_id`, `created_at`, `updated_at`)
VALUES (1, 3, '2026-01-23 12:00:00', '2026-01-23 12:00:00')

-- Update existing with new pivot data (if provided)
UPDATE `droip_ecommerce_category_product`
SET `position` = 1, `featured` = 1, `updated_at` = '2026-01-23 12:00:00'
WHERE `product_id` = 1 AND `category_id` = 1

Toggle the attachment state of related models in a belongs_to_many relationship.

Description:

The toggle() method attaches related models that are not currently attached and detaches those that are. This is useful for implementing features like favorites, bookmarks, or selections where you want to flip the relationship state.

Method Signature:

public function toggle(mixed $ids): array

Parameters:

$ids - The related model ID, array of IDs, or variadic list of IDs

Returns:

Array with keys attached and detached containing the affected IDs

Examples:

$product = Product::find(1);

// Toggle a single category
$changes = $product->categories()->toggle(5);
// If category 5 was attached, it's now detached (or vice versa)

// Toggle multiple categories
$changes = $product->categories()->toggle([3, 5, 7]);

// Check what happened
echo "Attached: " . implode(', ', $changes['attached']);
echo "Detached: " . implode(', ', $changes['detached']);

// Practical example: Toggle product in a collection
$collection = Collection::find(1);
$collection->products()->toggle($productId);
// If product was in collection, remove it; if not, add it

SQL Output:

-- First, check current state
SELECT `category_id` FROM `droip_ecommerce_category_product`
WHERE `product_id` = 1 AND `category_id` IN (3, 5, 7)

-- For IDs that exist, detach them
DELETE FROM `droip_ecommerce_category_product`
WHERE `product_id` = 1 AND `category_id` IN (5, 7)

-- For IDs that don't exist, attach them
INSERT INTO `droip_ecommerce_category_product`
(`product_id`, `category_id`, `created_at`, `updated_at`)
VALUES (1, 3, '2026-01-23 12:00:00', '2026-01-23 12:00:00')

Relationship Aggregates

Add a count of related models to your query results.

Description:

The with_count() method adds a count column for a specified relationship. The count is added as a {relationship}_count attribute on each model instance. This is more efficient than loading all related models just to count them.

Method Signature:

with_count(string|array $relations): QueryBuilder

Examples:

// Get products with review count
$products = Product::with_count('reviews')->get();
// Each product will have a 'reviews_count' attribute
echo $products[0]->reviews_count;

// Count multiple relationships
$products = Product::with_count(['reviews', 'orders'])->get();
echo $products[0]->reviews_count;
echo $products[0]->orders_count;

// With conditions
$products = Product::where('status', '=', 'active')
    ->with_count('reviews')
    ->get();

SQL Output:

SELECT `products`.*,
       (SELECT COUNT(*) FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) AS `reviews_count`
FROM `products`

SELECT `products`.*,
       (SELECT COUNT(*) FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) AS `reviews_count`,
       (SELECT COUNT(*) FROM `orders` WHERE `products`.`id` = `orders`.`product_id`) AS `orders_count`
FROM `products`

Get the maximum value of a column from related models.

Description:

The with_max() method adds the maximum value of a specified column from a relationship. The result is added as a {relationship}_max_{column} attribute on each model instance.

Method Signature:

with_max(string $relation, string $column): QueryBuilder

Examples:

// Get products with maximum order quantity
$products = Product::with_max('orders', 'quantity')->get();
// Each product will have an 'orders_max_quantity' attribute
echo $products[0]->orders_max_quantity;

// Get products with highest review rating
$products = Product::with_max('reviews', 'rating')->get();
echo $products[0]->reviews_max_rating;

SQL Output:

SELECT `products`.*,
       (SELECT MAX(`quantity`) FROM `orders` WHERE `products`.`id` = `orders`.`product_id`) AS `orders_max_quantity`
FROM `products`

SELECT `products`.*,
       (SELECT MAX(`rating`) FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) AS `reviews_max_rating`
FROM `products`

Get the minimum value of a column from related models.

Description:

The with_min() method adds the minimum value of a specified column from a relationship. The result is added as a {relationship}_min_{column} attribute on each model instance.

Method Signature:

with_min(string $relation, string $column): QueryBuilder

Examples:

// Get products with minimum order quantity
$products = Product::with_min('orders', 'quantity')->get();
echo $products[0]->orders_min_quantity;

// Get products with lowest review rating
$products = Product::with_min('reviews', 'rating')->get();
echo $products[0]->reviews_min_rating;

SQL Output:

SELECT `products`.*,
       (SELECT MIN(`quantity`) FROM `orders` WHERE `products`.`id` = `orders`.`product_id`) AS `orders_min_quantity`
FROM `products`

Get the average value of a column from related models.

Description:

The with_avg() method adds the average value of a specified column from a relationship. The result is added as a {relationship}_avg_{column} attribute on each model instance.

Method Signature:

with_avg(string $relation, string $column): QueryBuilder

Examples:

// Get products with average review rating
$products = Product::with_avg('reviews', 'rating')->get();
echo $products[0]->reviews_avg_rating;

// Get products with average order quantity
$products = Product::with_avg('orders', 'quantity')->get();
echo $products[0]->orders_avg_quantity;

// Combine with filters
$products = Product::where('status', '=', 'active')
    ->with_avg('reviews', 'rating')
    ->order_by('reviews_avg_rating', 'desc')
    ->get();

SQL Output:

SELECT `products`.*,
       (SELECT AVG(`rating`) FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) AS `reviews_avg_rating`
FROM `products`

Get the sum of a column's values from related models.

Description:

The with_sum() method adds the sum of a specified column from a relationship. The result is added as a {relationship}_sum_{column} attribute on each model instance.

Method Signature:

with_sum(string $relation, string $column): QueryBuilder

Examples:

// Get products with total order quantity
$products = Product::with_sum('orders', 'quantity')->get();
echo $products[0]->orders_sum_quantity;

// Get products with total revenue
$products = Product::with_sum('orders', 'total_amount')->get();
echo $products[0]->orders_sum_total_amount;

SQL Output:

SELECT `products`.*,
       (SELECT SUM(`quantity`) FROM `orders` WHERE `products`.`id` = `orders`.`product_id`) AS `orders_sum_quantity`
FROM `products`

Check if Relationship Exists

Add a boolean indicating if a relationship exists.

Description:

The with_exists() method adds a boolean attribute indicating whether at least one related record exists. The result is added as a {relationship}_exists attribute on each model instance.

Method Signature:

with_exists(string $relation): QueryBuilder

Examples:

// Check if products have reviews
$products = Product::with_exists('reviews')->get();
if ($products[0]->reviews_exists) {
    echo "This product has reviews";
}

// Check if products have orders
$products = Product::with_exists('orders')->get();

SQL Output:

SELECT `products`.*,
       EXISTS(SELECT * FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) AS `reviews_exists`
FROM `products`

Relationship Existence Queries

Filter by Relationship Existence

Filter results based on the existence of a relationship.

Description:

The where_has() method filters results to only include records that have at least one related record. You can optionally provide a callback to add additional constraints on the related records, and specify a minimum count.

Method Signature:

where_has(string $relation, Closure $callback = null, string $operator = '>=', int $count = 1): QueryBuilder

Examples:

// Get products that have at least one review
$products = Product::where_has('reviews')->get();

// Get products that have at least 5 reviews
$products = Product::where_has('reviews', null, '>=', 5)->get();

// Get products with reviews rating > 4
$products = Product::where_has('reviews', function ($query) {
    $query->where('rating', '>', 4);
})->get();

// Get products with at least 10 high-rated reviews
$products = Product::where_has('reviews', function ($query) {
    $query->where('rating', '>=', 4);
}, '>=', 10)->get();

SQL Output:

SELECT * FROM `products`
WHERE EXISTS (
    SELECT * FROM `reviews`
    WHERE `products`.`id` = `reviews`.`product_id`
)

SELECT * FROM `products`
WHERE (SELECT COUNT(*) FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) >= 5

SELECT * FROM `products`
WHERE EXISTS (
    SELECT * FROM `reviews`
    WHERE `products`.`id` = `reviews`.`product_id`
    AND `rating` > 4
)

Combine Eager Loading with Existence Filter

Combine where_has and eager loading in one call.

Description:

The with_where_has() method is a convenience method that combines where_has() with eager loading of the relationship. This filters the results and loads the related data in a single call.

Method Signature:

with_where_has(string $relation, Closure $callback = null, string $operator = '>=', int $count = 1): QueryBuilder

Examples:

// Get products with high-rated reviews and load those reviews
$products = Product::with_where_has('reviews', function ($query) {
    $query->where('rating', '>', 4);
})->get();

// Access the filtered relationship
foreach ($products as $product) {
    foreach ($product->reviews as $review) {
        echo $review->rating; // All will be > 4
    }
}

SQL Output:

-- Main query
SELECT * FROM `products`
WHERE EXISTS (
    SELECT * FROM `reviews`
    WHERE `products`.`id` = `reviews`.`product_id`
    AND `rating` > 4
)

-- Eager load query
SELECT * FROM `reviews`
WHERE `product_id` IN (1, 2, 3, ...)
AND `rating` > 4

OR Condition for Relationship Existence

Add an OR condition for relationship existence.

Description:

The or_where_has() method adds an OR WHERE clause for relationship existence, allowing you to retrieve records that match either condition.

Method Signature:

or_where_has(string $relation, Closure $callback = null, string $operator = '>=', int $count = 1): QueryBuilder

Examples:

// Get products that have reviews OR ratings
$products = Product::where_has('reviews')
    ->or_where_has('ratings')
    ->get();

// Get products with many reviews OR high-rated reviews
$products = Product::where_has('reviews', null, '>=', 10)
    ->or_where_has('reviews', function ($query) {
        $query->where('rating', '>=', 4.5);
    })
    ->get();

SQL Output:

SELECT * FROM `products`
WHERE EXISTS (
    SELECT * FROM `reviews`
    WHERE `products`.`id` = `reviews`.`product_id`
)
OR EXISTS (
    SELECT * FROM `ratings`
    WHERE `products`.`id` = `ratings`.`product_id`
)

Filter Where Relationship Doesn't Exist

Filter results to only include records without a specific relationship.

Description:

The where_not_has() method filters results to only include records that don't have any related records. You can optionally provide a callback to add constraints.

Method Signature:

where_not_has(string $relation, Closure $callback = null): QueryBuilder

Examples:

// Get products without reviews
$products = Product::where_not_has('reviews')->get();

// Get products without negative reviews
$products = Product::where_not_has('reviews', function ($query) {
    $query->where('rating', '<', 3);
})->get();

// Get products without orders in the last month
$products = Product::where_not_has('orders', function ($query) {
    $query->where('created_at', '>', date('Y-m-d', strtotime('-1 month')));
})->get();

SQL Output:

SELECT * FROM `products`
WHERE NOT EXISTS (
    SELECT * FROM `reviews`
    WHERE `products`.`id` = `reviews`.`product_id`
)

SELECT * FROM `products`
WHERE NOT EXISTS (
    SELECT * FROM `reviews`
    WHERE `products`.`id` = `reviews`.`product_id`
    AND `rating` < 3
)

OR Condition for Relationship Non-Existence

Add an OR condition for relationship non-existence.

Description:

The or_where_not_has() method adds an OR WHERE clause for relationship non-existence.

Method Signature:

or_where_not_has(string $relation, Closure $callback = null): QueryBuilder

Examples:

// Get products without reviews OR without ratings
$products = Product::where_not_has('reviews')
    ->or_where_not_has('ratings')
    ->get();

SQL Output:

SELECT * FROM `products`
WHERE NOT EXISTS (
    SELECT * FROM `reviews`
    WHERE `products`.`id` = `reviews`.`product_id`
)
OR NOT EXISTS (
    SELECT * FROM `ratings`
    WHERE `products`.`id` = `ratings`.`product_id`
)

Relationship Column Queries

Filter results based on a related model's column value.

Description:

The where_relation() method allows you to filter results based on a column value in a related table without explicitly writing JOIN statements. This is a convenient alternative to where_has() when you need to check a specific column value.

Method Signature:

where_relation(string $relation, string $column, string $operator = null, mixed $value = null): QueryBuilder

Examples:

// Get products where category name is 'Electronics'
$products = Product::where_relation('category', 'name', '=', 'Electronics')->get();

// Get products where brand is active
$products = Product::where_relation('brand', 'status', '=', 'active')->get();

// Get products where supplier country is 'USA'
$products = Product::where_relation('supplier', 'country', '=', 'USA')->get();

SQL Output:

SELECT * FROM `products`
WHERE EXISTS (
    SELECT * FROM `categories`
    WHERE `products`.`category_id` = `categories`.`id`
    AND `categories`.`name` = 'Electronics'
)

SELECT * FROM `products`
WHERE EXISTS (
    SELECT * FROM `brands`
    WHERE `products`.`brand_id` = `brands`.`id`
    AND `brands`.`status` = 'active'
)

Combine Eager Loading with Relation Column Filter

Combine where_relation with eager loading.

Description:

The with_where_relation() method combines where_relation() with eager loading of the relationship, filtering results and loading related data in one call.

Method Signature:

with_where_relation(string $relation, string $column, string $operator = null, mixed $value = null): QueryBuilder

Examples:

// Get products in Electronics category and load the category
$products = Product::with_where_relation('category', 'name', '=', 'Electronics')->get();

foreach ($products as $product) {
    echo $product->category->name; // Will be 'Electronics'
}

SQL Output:

-- Main query
SELECT * FROM `products`
WHERE EXISTS (
    SELECT * FROM `categories`
    WHERE `products`.`category_id` = `categories`.`id`
    AND `categories`.`name` = 'Electronics'
)

-- Eager load query
SELECT * FROM `categories`
WHERE `id` IN (1, 2, 3, ...)
AND `name` = 'Electronics'

OR Condition for Relationship Column

Add an OR condition for a relationship column query.

Description:

The or_where_relation() method adds an OR WHERE clause for a relationship column query.

Method Signature:

or_where_relation(string $relation, string $column, string $operator = null, mixed $value = null): QueryBuilder

Examples:

// Get products in Electronics OR Computers category
$products = Product::where_relation('category', 'name', '=', 'Electronics')
    ->or_where_relation('category', 'name', '=', 'Computers')
    ->get();

// Get products from active brands OR featured brands
$products = Product::where_relation('brand', 'status', '=', 'active')
    ->or_where_relation('brand', 'featured', '=', 1)
    ->get();

SQL Output:

SELECT * FROM `products`
WHERE EXISTS (
    SELECT * FROM `categories`
    WHERE `products`.`category_id` = `categories`.`id`
    AND `categories`.`name` = 'Electronics'
)
OR EXISTS (
    SELECT * FROM `categories`
    WHERE `products`.`category_id` = `categories`.`id`
    AND `categories`.`name` = 'Computers'
)

Negative Relationship Column Query

Filter where a relationship doesn't match a column condition.

Description:

The where_not_has_relation() method filters results to exclude records where the related model matches a specific column condition.

Method Signature:

where_not_has_relation(string $relation, string $column, string $operator = null, mixed $value = null): QueryBuilder

Examples:

// Get products not in 'Archived' category
$products = Product::where_not_has_relation('category', 'name', '=', 'Archived')->get();

// Get products where brand is not inactive
$products = Product::where_not_has_relation('brand', 'status', '=', 'inactive')->get();

SQL Output:

SELECT * FROM `products`
WHERE NOT EXISTS (
    SELECT * FROM `categories`
    WHERE `products`.`category_id` = `categories`.`id`
    AND `categories`.`name` = 'Archived'
)

OR Negative Relationship Column Query

Add an OR condition for a negative relationship column query.

Description:

The or_where_not_has_relation() method adds an OR WHERE clause for a negative relationship column query.

Method Signature:

or_where_not_has_relation(string $relation, string $column, string $operator = null, mixed $value = null): QueryBuilder

Examples:

// Get products where category is not inactive OR brand is not inactive
$products = Product::where_not_has_relation('category', 'status', '=', 'inactive')
    ->or_where_not_has_relation('brand', 'status', '=', 'inactive')
    ->get();

SQL Output:

SELECT * FROM `products`
WHERE NOT EXISTS (
    SELECT * FROM `categories`
    WHERE `products`.`category_id` = `categories`.`id`
    AND `categories`.`status` = 'inactive'
)
OR NOT EXISTS (
    SELECT * FROM `brands`
    WHERE `products`.`brand_id` = `brands`.`id`
    AND `brands`.`status` = 'inactive'
)

Complex Relationship Query Example

Here's a comprehensive example combining multiple relationship query methods:

$products = Product::where('status', '=', 'active')
    // Only products with at least 10 reviews
    ->where_has('reviews', null, '>=', 10)
    // In Electronics or Computers category
    ->where_relation('category', 'name', '=', 'Electronics')
    ->or_where_relation('category', 'name', '=', 'Computers')
    // From active brands only
    ->where_relation('brand', 'status', '=', 'active')
    // Include review count and average rating
    ->with_count('reviews')
    ->with_avg('reviews', 'rating')
    // Order by average rating
    ->order_by('reviews_avg_rating', 'desc')
    ->limit(20)
    ->get();

// Access the aggregated data
foreach ($products as $product) {
    echo "Product: " . $product->name;
    echo "Reviews: " . $product->reviews_count;
    echo "Average Rating: " . $product->reviews_avg_rating;
}

SQL Output:

SELECT `products`.*,
       (SELECT COUNT(*) FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) AS `reviews_count`,
       (SELECT AVG(`rating`) FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) AS `reviews_avg_rating`
FROM `products`
WHERE `status` = 'active'
  AND (SELECT COUNT(*) FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) >= 10
  AND (
    EXISTS (
      SELECT * FROM `categories`
      WHERE `products`.`category_id` = `categories`.`id`
      AND `categories`.`name` = 'Electronics'
    )
    OR EXISTS (
      SELECT * FROM `categories`
      WHERE `products`.`category_id` = `categories`.`id`
      AND `categories`.`name` = 'Computers'
    )
  )
  AND EXISTS (
    SELECT * FROM `brands`
    WHERE `products`.`brand_id` = `brands`.`id`
    AND `brands`.`status` = 'active'
  )
ORDER BY `reviews_avg_rating` DESC
LIMIT 20

Chunking & Iteration

Chunk Results

Process query results in chunks to reduce memory usage.

Description:

The chunk() method retrieves and processes query results in smaller batches (chunks), which is essential when working with large datasets. Instead of loading all records into memory at once, it processes them in manageable groups. The callback function is called for each chunk, and if it returns false, processing stops.

Method Signature:

chunk(int $count, Closure $callback): bool

Examples:

// Process 100 products at a time
Product::where('status', '=', 'active')->chunk(100, function ($products) {
    foreach ($products as $product) {
        // Process each product
        echo $product->name . "\n";
    }
});

// Stop processing if a condition is met
Product::chunk(100, function ($products) {
    foreach ($products as $product) {
        if ($product->price > 1000) {
            return false; // Stop chunking
        }
        // Process product
    }
});

// Update products in chunks
Product::where('status', '=', 'draft')->chunk(50, function ($products) {
    foreach ($products as $product) {
        $product->update(['status' => 'active']);
    }
});

SQL Output:

-- Processes in batches
SELECT * FROM `products` WHERE `status` = 'active' ORDER BY `id` ASC LIMIT 100 OFFSET 0
SELECT * FROM `products` WHERE `status` = 'active' ORDER BY `id` ASC LIMIT 100 OFFSET 100
SELECT * FROM `products` WHERE `status` = 'active' ORDER BY `id` ASC LIMIT 100 OFFSET 200
-- ... continues until all records are processed

Chunk and Map Results

Chunk results and transform them using a callback.

Description:

The chunk_map() method combines chunking with mapping, allowing you to transform each record and collect the results into a single Collection. This is memory-efficient for large datasets while still returning all transformed results.

Method Signature:

chunk_map(Closure $callback, int $count = 1000): Collection

Examples:

// Extract product names from large dataset
$names = Product::chunk_map(function ($product) {
    return $product->name;
}, 100);

// Transform products
$transformed = Product::chunk_map(function ($product) {
    return [
        'id' => $product->id,
        'title' => strtoupper($product->name),
        'price_with_tax' => $product->price * 1.15,
    ];
}, 100);

SQL Output:

-- Same chunking behavior as chunk()
SELECT * FROM `products` ORDER BY `id` ASC LIMIT 100 OFFSET 0
SELECT * FROM `products` ORDER BY `id` ASC LIMIT 100 OFFSET 100
-- ... continues until all records are processed

Iterate Over Results

Iterate over results and execute a callback for each item.

Description:

The each() method iterates over all query results, executing a callback for each individual record. It uses chunking internally for memory efficiency. If the callback returns false, iteration stops.

Method Signature:

each(Closure $callback, int $count = 1000): bool

Examples:

// Process each product individually
Product::where('status', '=', 'active')->each(function ($product) {
    echo $product->name . "\n";
});

// Stop iteration conditionally
Product::each(function ($product) {
    if ($product->price > 1000) {
        return false; // Stop iteration
    }
    // Process product
}, 100);

SQL Output:

-- Uses chunking internally
SELECT * FROM `products` WHERE `status` = 'active' ORDER BY `id` ASC LIMIT 1000 OFFSET 0
SELECT * FROM `products` WHERE `status` = 'active' ORDER BY `id` ASC LIMIT 1000 OFFSET 1000
-- ... continues until all records are processed

Chunk by ID Ascending

Chunk results by ID in ascending order for better performance.

Description:

The chunk_by_id() method chunks results using the ID column for ordering, which is more efficient than offset-based chunking for large datasets. It automatically handles the WHERE clause to fetch the next chunk based on the last ID from the previous chunk.

Method Signature:

chunk_by_id(int $count, Closure $callback, string $column = null, string $alias = null): bool

Examples:

// Chunk by ID (more efficient for large tables)
Product::chunk_by_id(100, function ($products) {
    foreach ($products as $product) {
        // Process product
    }
});

// Chunk by custom column
Product::chunk_by_id(100, function ($products) {
    // Process products
}, 'custom_id');

SQL Output:

-- First chunk
SELECT * FROM `products` ORDER BY `id` ASC LIMIT 100

-- Subsequent chunks use WHERE to skip processed records
SELECT * FROM `products` WHERE `id` > 100 ORDER BY `id` ASC LIMIT 100
SELECT * FROM `products` WHERE `id` > 200 ORDER BY `id` ASC LIMIT 100
-- ... continues until all records are processed

Chunk by ID Descending

Chunk results by ID in descending order.

Description:

The chunk_by_id_desc() method is similar to chunk_by_id() but processes records in descending order, starting from the highest ID.

Method Signature:

chunk_by_id_desc(int $count, Closure $callback, string $column = null, string $alias = null): bool

Examples:

// Process newest products first
Product::chunk_by_id_desc(100, function ($products) {
    foreach ($products as $product) {
        // Process product (newest first)
    }
});

SQL Output:

-- First chunk
SELECT * FROM `products` ORDER BY `id` DESC LIMIT 100

-- Subsequent chunks
SELECT * FROM `products` WHERE `id` < 900 ORDER BY `id` DESC LIMIT 100
SELECT * FROM `products` WHERE `id` < 800 ORDER BY `id` DESC LIMIT 100
-- ... continues until all records are processed

Lazy Loading

Lazy Iteration

Lazily iterate over query results using a PHP generator.

Description:

The lazy() method returns a PHP Generator that yields records one at a time, using chunking internally. This is the most memory-efficient way to process large datasets as only one chunk is in memory at a time, and records are yielded individually.

Method Signature:

lazy(int $chunk_size = 1000): Generator

Examples:

// Lazily iterate over all products
foreach (Product::where('status', '=', 'active')->lazy() as $product) {
    // Process one product at a time
    echo $product->name . "\n";
}

// Custom chunk size
foreach (Product::lazy(100) as $product) {
    // Memory-efficient iteration
}

// Can break out of loop
foreach (Product::lazy() as $product) {
    if ($product->price > 1000) {
        break;
    }
}

SQL Output:

-- Fetches in chunks internally
SELECT * FROM `products` WHERE `status` = 'active' ORDER BY `id` ASC LIMIT 1000 OFFSET 0
-- Next chunk is only fetched when needed
SELECT * FROM `products` WHERE `status` = 'active' ORDER BY `id` ASC LIMIT 1000 OFFSET 1000

Lazy Iteration by ID Ascending

Lazily iterate by ID in ascending order.

Description:

The lazy_by_id() method combines lazy loading with ID-based chunking for optimal performance on large datasets.

Method Signature:

lazy_by_id(int $chunk_size = 1000, string $column = null, string $alias = null): Generator

Examples:

// Memory-efficient iteration using ID-based chunking
foreach (Product::lazy_by_id(100) as $product) {
    // Process product
}

// With custom ID column
foreach (Product::lazy_by_id(100, 'custom_id') as $product) {
    // Process product
}

SQL Output:

-- First chunk
SELECT * FROM `products` ORDER BY `id` ASC LIMIT 100

-- Subsequent chunks
SELECT * FROM `products` WHERE `id` > 100 ORDER BY `id` ASC LIMIT 100

Lazy Iteration by ID Descending

Lazily iterate by ID in descending order.

Description:

The lazy_by_id_desc() method provides lazy iteration in reverse ID order, useful for processing newest records first.

Method Signature:

lazy_by_id_desc(int $chunk_size = 1000, string $column = null, string $alias = null): Generator

Examples:

// Process newest products first with lazy loading
foreach (Product::lazy_by_id_desc(100) as $product) {
    // Process product (newest first)
}

SQL Output:

-- First chunk
SELECT * FROM `products` ORDER BY `id` DESC LIMIT 100

-- Subsequent chunks
SELECT * FROM `products` WHERE `id` < 900 ORDER BY `id` DESC LIMIT 100

Raw Queries

Raw WHERE Clause

Add a raw SQL WHERE clause to the query.

Description:

The where_raw() method allows you to add raw SQL expressions to your WHERE clause when the fluent query builder doesn't support your specific needs. Use bindings to prevent SQL injection.

Method Signature:

where_raw(string $sql, array $bindings = [], string $boolean = 'and'): QueryBuilder

Examples:

// Complex condition using SQL functions
$products = Product::where_raw('price > IF(sale_price IS NULL, 100, sale_price)')->get();

// With parameter bindings (recommended for security)
$products = Product::where_raw('price > ? AND stock > ?', [100, 10])->get();

// Date calculations
$products = Product::where_raw('DATEDIFF(NOW(), created_at) < ?', [30])->get();

// Combine with other query builder methods
$products = Product::where('status', '=', 'active')
    ->where_raw('(price * quantity) > ?', [1000])
    ->get();

SQL Output:

SELECT * FROM `products` WHERE price > IF(sale_price IS NULL, 100, sale_price)
SELECT * FROM `products` WHERE price > 100 AND stock > 10
SELECT * FROM `products` WHERE DATEDIFF(NOW(), created_at) < 30
SELECT * FROM `products` WHERE `status` = 'active' AND (price * quantity) > 1000

OR Raw WHERE Clause

Add a raw SQL WHERE clause with OR logic.

Description:

The or_where_raw() method adds a raw SQL expression with OR logic to your WHERE clause.

Method Signature:

or_where_raw(string $sql, array $bindings = []): QueryBuilder

Examples:

// Multiple raw conditions with OR
$products = Product::where('status', '=', 'active')
    ->or_where_raw('featured = 1 AND price < ?', [100])
    ->get();

// Complex OR conditions
$products = Product::where_raw('stock > ?', [100])
    ->or_where_raw('(backorder_allowed = 1 AND supplier_stock > ?)', [50])
    ->get();

SQL Output:

SELECT * FROM `products`
WHERE `status` = 'active'
   OR (featured = 1 AND price < 100)

SELECT * FROM `products`
WHERE stock > 100
   OR (backorder_allowed = 1 AND supplier_stock > 50)

Utility Methods

Tap Into Query

Pass the query to a callback and return it.

Description:

The tap() method allows you to "tap into" the query chain to perform side effects (like logging or debugging) without breaking the chain. The callback receives the query builder instance, and the method returns the query builder for continued chaining.

Method Signature:

tap(Closure $callback): QueryBuilder

Examples:

// Log the SQL query for debugging
$products = Product::where('status', '=', 'active')
    ->tap(function ($query) {
        error_log('Query: ' . $query->to_sql());
    })
    ->where('price', '>', 100)
    ->get();

// Conditionally modify query
$products = Product::where('status', '=', 'active')
    ->tap(function ($query) use ($user) {
        if ($user->is_admin) {
            // Admins see all products
        } else {
            $query->where('published', '=', 1);
        }
    })
    ->get();

// Debug query bindings
$products = Product::where('price', '>', 100)
    ->tap(function ($query) {
        var_dump($query->get_bindings());
    })
    ->get();

Distinct Rows

Select only distinct/unique rows.

Description:

The distinct() method ensures that only unique rows are returned, eliminating duplicates based on the selected columns.

Method Signature:

distinct(array|bool $columns = []): QueryBuilder

Examples:

// Get distinct category IDs
$categories = Product::distinct()->select('category_id')->get();

// Get distinct combinations of columns
$combinations = Product::distinct()
    ->select(['category_id', 'brand_id'])
    ->get();

// Combine with other clauses
$active_categories = Product::distinct()
    ->select('category_id')
    ->where('status', '=', 'active')
    ->get();

SQL Output:

SELECT DISTINCT `category_id` FROM `products`
SELECT DISTINCT `category_id`, `brand_id` FROM `products`
SELECT DISTINCT `category_id` FROM `products` WHERE `status` = 'active'

Performance Optimization Examples

Efficient Large Dataset Processing

// BAD: Loads all records into memory at once
$products = Product::all();
foreach ($products as $product) {
    // Process product
}

// GOOD: Uses chunking to process in batches
Product::chunk(100, function ($products) {
    foreach ($products as $product) {
        // Process product
    }
});

// BETTER: Uses lazy loading for maximum memory efficiency
foreach (Product::lazy(100) as $product) {
    // Process product one at a time
}

// BEST: Uses ID-based lazy loading for large tables
foreach (Product::lazy_by_id(100) as $product) {
    // Most efficient for very large datasets
}

Complex Query with Raw SQL

// Combine query builder methods with raw SQL for complex scenarios
$products = Product::select(['products.*'])
    ->select_raw('(price * ?) as price_with_tax', [1.15])
    ->select_raw('DATEDIFF(NOW(), created_at) as days_old')
    ->join('categories', 'products.category_id', '=', 'categories.id')
    ->where('products.status', '=', 'active')
    ->where_raw('(price * stock_quantity) > ?', [1000])
    ->where_relation('category', 'name', '=', 'Electronics')
    ->with_count('reviews')
    ->with_avg('reviews', 'rating')
    ->tap(function ($query) {
        // Log for debugging
        error_log('Complex query: ' . $query->to_sql());
    })
    ->order_by('days_old', 'asc')
    ->limit(50)
    ->get();

SQL Output:

SELECT `products`.*,
       (price * 1.15) as price_with_tax,
       DATEDIFF(NOW(), created_at) as days_old,
       (SELECT COUNT(*) FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) AS `reviews_count`,
       (SELECT AVG(`rating`) FROM `reviews` WHERE `products`.`id` = `reviews`.`product_id`) AS `reviews_avg_rating`
FROM `products`
INNER JOIN `categories` ON `products`.`category_id` = `categories`.`id`
WHERE `products`.`status` = 'active'
  AND (price * stock_quantity) > 1000
  AND EXISTS (
    SELECT * FROM `categories`
    WHERE `products`.`category_id` = `categories`.`id`
    AND `categories`.`name` = 'Electronics'
  )
ORDER BY days_old ASC
LIMIT 50

Best Practices

When to Use Chunking vs Lazy Loading

Use chunk() when you need to process records in batches and perform batch operations
Use lazy() when you need to process records one at a time with minimal memory usage
Use chunk_by_id() or lazy_by_id() for very large tables (millions of rows) as they're more efficient than offset-based pagination
Avoid all() or get() without limits on large tables as they load everything into memory

When to Use Raw Queries

Use raw SQL only when the query builder doesn't support your specific need
Always use parameter bindings to prevent SQL injection
Consider whether a raw query could be refactored into query builder methods
Document why raw SQL is necessary for future maintainability

Memory Management

// For small datasets (< 1000 records)
$products = Product::where('status', '=', 'active')->get();

// For medium datasets (1000-100,000 records)
Product::where('status', '=', 'active')->chunk(100, function ($products) {
    // Process chunk
});

// For large datasets (> 100,000 records)
foreach (Product::where('status', '=', 'active')->lazy_by_id(100) as $product) {
    // Process individual record
}

Introduction​

Basic Queries​

Select Columns​

Select Raw Expression​

WHERE Clauses​

Basic WHERE Clause​

OR WHERE Clause​

WHERE IN Clause​

WHERE NOT IN Clause​

WHERE NULL Clause​

WHERE NOT NULL Clause​

WHERE BETWEEN Clause​

WHERE LIKE Clause​

WHERE NOT LIKE Clause​

Joins​

Inner Join​

Left Join​

Right Join​

Ordering & Grouping​

Order By​

Group By​

Having​

Limiting Results​

Limit​

Offset​

Skip​

Take​

Aggregates​

Count​

Sum​

Average​

Minimum​

Maximum​

Executing Queries​

Get All Results​

Get First Result​

Find by ID​

First or Fail​

Check Existence​

Check Non-Existence​

Get Single Column Value​

Data Manipulation​

Insert Record​

Insert and Get ID​

Update Records​

Delete Records​

Pagination​

Paginate Results​

Utility Methods​

Distinct​

Complex Query Example​

Defining Relationships​

Define One-to-One Relationship​

Define One-to-Many Relationship​

Define Inverse One-to-One or Many Relationship​

Define Many-to-Many Relationship​

Relationship Helper Methods​

Create Related Models​

Create Multiple Related Models​

Associate Related Model​

Dissociate Related Model​

Attach Related Models​

Detach Related Models​

Sync Related Models​

Toggle Related Models​

Relationship Aggregates​

Count Related Models​

Maximum Value from Related Models​

Minimum Value from Related Models​

Average Value from Related Models​

Sum of Values from Related Models​

Check if Relationship Exists​

Relationship Existence Queries​

Filter by Relationship Existence​

Combine Eager Loading with Existence Filter​

OR Condition for Relationship Existence​

Filter Where Relationship Doesn't Exist​

OR Condition for Relationship Non-Existence​

Relationship Column Queries​

Query Based on Related Column​

Introduction

Basic Queries

Select Columns

Select Raw Expression

WHERE Clauses

Basic WHERE Clause

OR WHERE Clause

WHERE IN Clause

WHERE NOT IN Clause

WHERE NULL Clause

WHERE NOT NULL Clause

WHERE BETWEEN Clause

WHERE LIKE Clause

WHERE NOT LIKE Clause

Joins

Inner Join

Left Join

Right Join

Ordering & Grouping

Order By

Group By

Having

Limiting Results

Limit

Offset

Skip

Take

Aggregates

Count

Sum

Average

Minimum

Maximum

Executing Queries

Get All Results

Get First Result

Find by ID

First or Fail

Check Existence

Check Non-Existence

Get Single Column Value

Data Manipulation

Insert Record

Insert and Get ID

Update Records

Delete Records

Pagination

Paginate Results

Utility Methods

Distinct

Complex Query Example

Defining Relationships

Define One-to-One Relationship

Define One-to-Many Relationship

Define Inverse One-to-One or Many Relationship

Define Many-to-Many Relationship

Relationship Helper Methods

Create Related Models

Create Multiple Related Models

Associate Related Model

Dissociate Related Model

Attach Related Models

Detach Related Models

Sync Related Models

Toggle Related Models

Relationship Aggregates

Count Related Models

Maximum Value from Related Models

Minimum Value from Related Models

Average Value from Related Models

Sum of Values from Related Models

Check if Relationship Exists

Relationship Existence Queries

Filter by Relationship Existence

Combine Eager Loading with Existence Filter

OR Condition for Relationship Existence

Filter Where Relationship Doesn't Exist

OR Condition for Relationship Non-Existence

Relationship Column Queries

Query Based on Related Column