• Getting Started
• Model Properties
• Query Using ORM
• Insertion and Update
• Aggregation
• Transform Eloquent Collection
• Pagination
• Database Transactions
• Eloquent Join
• DB Query

Eloquent: Getting Started

Introduction

Doppar features its own powerful data interaction tool, Eloquent, an object-relational mapper (ORM), which simplifies and enhances the way you work with your database. With ORM, every database table is linked to a dedicated "Data Model" that serves as your gateway to managing table data seamlessly. Beyond just fetching records, Doppar Data Mapper empowers you to effortlessly insert, update, and delete records, making database interactions intuitive and efficient. Whether you're building complex queries or handling simple data operations, ORM ensures a smooth and enjoyable experience, tailored to streamline your development workflow.

Creating Model Classes

To get started, let's create an Eloquent model. Models typically live in the app\Models directory and extend the Phaseolies\Database\Eloquent\Model class. You may use the make:model Pool command to generate a new model:

php pool make:model Post

This command will generate a new model inside App\Models directory. Models generated by the make:model command will be placed in the app/Models directory. Let's see a basic model class.

<?php

namespace App\Models;

use Phaseolies\Database\Eloquent\Model;

class Post extends Model
{
    //
}

Model Properties

Before diving into Doppar's data management capabilities, it’s important to familiarize yourself with some key model properties that shape how your data is handled. Doppar offers the flexibility to customize these properties to suit your specific needs. Key properties include $pageSize, which controls the number of records displayed per page; $primaryKey, which defines the unique identifier for your table; $table, which specifies the database table associated with the model; $creatable, which determines whether new records can be added; and $unexposable and $timeStamps, which allows you to hide sensitive or irrelevant data from being exposed and handle datetime columns. With Doppar, you have full control to tweak these properties, ensuring your data interactions are both efficient and secure. Let's see the User model as for example.

$primaryKey

Specifies the column name that serves as the unique identifier for the table. By default, this is set to 'id', but it can be customized if your table uses a different primary key.

<?php

namespace App\Models;

use Phaseolies\Database\Eloquent\Model;

class Post extends Model
{
    /**
     * The primary key associated with the table.
     *
     * @var int
     */
    protected $primaryKey = 'id';
}

$table

Specifies the database table associated with this model

<?php

namespace App\Models;

use Phaseolies\Database\Eloquent\Model;

class Post extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'posts';
}

$creatable

Specifies which attributes can be mass-assigned when creating or updating records.This helps prevent mass assignment vulnerabilities by explicitly defining safe fields. Only the attributes listed here can be set in bulk operations.

<?php

namespace App\Models;

use Phaseolies\Database\Eloquent\Model;

class Post extends Model
{
    /**
     * Creatable Attributes
     *
     * @var array
     */
    protected $creatable = ['title', 'status', 'description'];
}

$unexposable

Specifies which attributes should be hidden when the model is converted to an array or JSON or Collection. This is particularly useful for hiding sensitive information, such as passwords, from being exposed in API responses or other outputs.

<?php

namespace App\Models;

use Phaseolies\Database\Eloquent\Model;

class User extends Model
{
    /**
     * Unexposable Attributes
     *
     * @var array
     */
    protected $unexposable = ['password'];
}

$timeStamps

Indicates whether the model should maintain timest(created_at and updated_at fields.).

<?php

namespace App\Models;

use Phaseolies\Database\Eloquent\Model;

class Post extends Model
{
    /**
     * Indicates if the model should be timestamped.
     *
     * @var bool
     */
    protected $timeStamps = true;
}

---

Query Using ORM

Retrieving Models

Once you have created a model and its associated database table, you are ready to start retrieving data from your database. You can think of each Eloquent model as a powerful query builder allowing you to fluently query the database table associated with the model. The model's all method will retrieve all of the records from the model's associated database table:

<?php

use App\Models\Post;

$posts = Post::all();

// Or you can use direct get() method
$posts = Post::get();

foreach (Post::all() as $post) {
    echo $post->title;
}

Expending Queries

The all method in Eloquent fetches every record from a model’s corresponding table. However, because Eloquent models double as query builders, you can refine queries using conditions and retrieve only the matching results by calling the get method.

$posts = Post::query()
    ->where('status', '=', true)
    ->orderBy('title')
    ->limit(10)
    ->get();

You can write query with multiple where condition by chaining multiple where with the queries

Post::query()
    ->where('title', '=', 'Sincere Littel')
    ->where('status', '=', 1)
    ->first();

Collections

As observed, Eloquent methods such as all and get are designed to fetch multiple records from the database. Rather than returning a standard PHP array, these methods yield an instance of Phaseolies\Support\Collection, providing a rich set of tools to work with the retrieved models efficiently.

If you want to convert collection to array, you can use toArray() method.

$posts = Post::query()
    ->where('status', '=', true)
    ->orderBy('title')
    ->limit(10)
    ->get()
    ->toArray();

Fetch the First Records

To fetch the first records you can use first() function as like below

Post::query()->first();

Actually this above query returns the Post model object so you can directly fetch its attributes like

Post::query()->first()?->title ?? 'defult';

GroupBy and OrderBy

You can also use groupBy() and orderBy() to handle your records like

User::query()->orderBy('id', 'desc')->groupBy('name')->get();

toSql()

The toSql() method in Eloquent is a useful tool when you want to inspect the raw SQL query that would be executed for a given Eloquent query. Instead of retrieving the results from the database, toSql() returns the SQL statement as a string, allowing you to debug or log the query before it's run. This is especially helpful during development when you need to understand how your query builder chain is being translated into SQL.

Here's an example:

User::query()
    ->where('status', '=', 'active')
    ->toSql();

This will output:

select * from `users` where `status` = ?

Using toSql() helps you optimize and troubleshoot queries by giving insight into what Doppar is sending to the database under the hood.

find()

The find() method in Eloquent is used to retrieve a single record from the database by its primary key. It offers a quick and efficient way to look up a specific model instance without having to write a full where clause. If a matching record is found, an instance of the model is returned; otherwise, null is returned.

Here’s a basic example:

$user = User::find(1);

In this case, Eloquent returns a collection of User instances corresponding to the given IDs. find() is ideal when you know the exact primary key(s) you're looking for and want a concise way to retrieve the corresponding record(s).

This is the sort version of this query

$user = User::query()->where('id', '=', 1)->first();

count()

To see how many records your table has, you can use count() function as like

User::count();
User::query()->orderBy('id', 'desc')->groupBy('name')->count();
User::query()->where('active', '=', true)->count();

Newest and Oldest Records

Doppar makes it convenient to retrieve the most recent or earliest records from a database using the newest() and oldest() query methods. These methods sort the results based on a specified column, with id being the default field if none is provided.

To fetch the latest records (in descending order):

User::query()->newest()->get();

This retrieves the most recently added users based on the id column by default. If you want to sort by a different column, you can pass it as an argument:

User::query()->newest('name')->get();

Similarly, to retrieve the oldest records (in ascending order):

User::query()->oldest()->get();

Again, you can specify a custom column to determine what "oldest" means in your context:

User::query()->oldest('id')->get();

These methods offer a clean and readable way to sort query results by time or any relevant field, enhancing code clarity while maintaining flexibility.

Selecting Specific Columns

In many cases, you may not need to retrieve every column from a table—especially when working with large datasets. Eloquent's select() method allows you to specify exactly which columns you want to fetch, helping optimize performance and reduce memory usage.

Here are a few examples:

// Selecting specific columns using an array
User::query()->select(['name', 'email'])->get();

// Selecting specific columns using multiple arguments
User::query()->select('name', 'email')->get();

Both of these will return only the name and email fields for each user, excluding all other columns.

You can also use select() in more advanced queries, such as when grouping results:

Post::query()
    ->select('title', 'COUNT(*) as count')
    ->groupBy('category_id')
    ->get();

Using select() helps tailor your queries to fetch only the data you actually need, improving both efficiency and clarity in your code.

selectRaw()

The selectRaw() method in Doppar's query builder provides a powerful way to include raw SQL expressions in your queries. It's especially useful when you need to perform calculations, use SQL functions, or include complex expressions that go beyond Eloquent's default capabilities.

Basic Usage

You can use selectRaw() to write a custom SQL expression directly in the SELECT clause. For example, to count the total number of orders:

use App\Models\Order;

$total = Order::query()
    ->selectRaw('COUNT(*) as order_count')
    ->first();

echo $total->order_count;

This will execute a query similar to:

SELECT COUNT(*) as order_count FROM orders;

Calculated Columns

You can also perform calculations using existing fields in the database. For instance, to calculate the total value of each order:

$orders = Order::query()
    ->selectRaw('price * quantity as total_value')
    ->get();

foreach ($orders as $order) {
    echo $order->total_value;
}

This calculates a total_value field on the fly for each record using the price and quantity columns.

Chaining Multiple selectRaw() Calls

You can chain multiple selectRaw() calls to build a SELECT clause that includes several raw expressions. Each call adds to the final SQL:

$orders = Order::query()
    ->selectRaw('SUM(price) as total_sales')
    ->selectRaw('AVG(quantity) as average_quantity')
    ->selectRaw('MAX(price) as highest_price')
    ->first();

This returns an object with aggregated values such as total sales, average quantity, and the highest price.

⚠️ Security Tip: When using raw expressions, especially with user input, always bind parameters or sanitize values to protect against SQL injection.

Using Bindings with selectRaw()

Scenario: You're running an e-commerce platform. You store orders in an orders table with these columns:

• price: base price of an item
• category_id: the category the product belongs to
• status: whether the order is completed (true) or not
• created_at: timestamp of the order

You want to:

• Get the maximum order value after tax (e.g. 8%) and after VAT (e.g. 20%)
• Only consider completed orders
• Group results by category

Now assume "For each product category, what's the highest order value including tax and VAT, from completed orders?"

$orders = Order::query()
    ->selectRaw('MAX(price * ?) AS total_with_tax', [1.08]) // Apply 8% tax
    ->selectRaw('MAX(price * ?) AS total_with_vat', [1.20]) // Apply 20% VAT
    ->where('status', '=', true)
    ->groupByRaw('category_id')
    ->get();

Example output

category_id	total_with_tax	total_with_vat
1	108.00	120.00
2	216.00	240.00
3	162.00	180.00

Why Use selectRaw Here?

• You need to run math operations (price * taxRate) directly in SQL.
• You want to bind the tax rate dynamically (?) to avoid hardcoding values.
• You’re leveraging SQL’s aggregate function (MAX()) for reporting.

Bindings are passed as an array to selectRaw() and must match the number of ? placeholders in the SQL expression.

exists()

To check whether a specific row exists in your database, you can use the exists() function. This method returns a boolean value (true or false) based on whether the specified condition matches any records. Here's an example:

// Returns `true` if a matching row exists, otherwise `false`.
User::query()->where('id', '=', 1)->exists();

whereIn()

The whereIn() method filters records where a column's value matches any value in the given array.

User::query()->whereIn('id', [1, 2, 4])->get();

This retrieves all users with id values of 1, 2, or 4.

orWhereIn()

The orWhereIn() method filters records where a column's value optionally matches any value in the given array.

User::query()->orWhereIn('id', [1, 2, 4])->get();

whereBetween()

The whereBetween() method in Doppar lets you filter records where a column’s value falls within a specified range. It’s particularly useful for working with numeric values or date/time columns, such as filtering records created within a certain time frame.

Example: Filter Users by Date Range

User::query()
    ->whereBetween('created_at', ['2025-02-29', '2025-04-29'])
    ->get();

This query will retrieve all users whose created_at timestamp falls between February 29, 2025 and April 29, 2025, inclusive.

You can use whereBetween() with any comparable column — such as prices, IDs, scores, or dates — to make your queries more expressive and efficient.

User::query()->whereBetween('id', [1, 10])->get();

This query retrieves all user records where the id is between 1 and 10, inclusive. That means it will return users with id values of 1, 2, 3, ..., 10.

whereNotBetween()

The whereNotBetween() method allows you to filter records where a given column's value do not falls within a specified range. This is commonly used for date or numerical ranges.

User::query()
    ->whereNotBetween('created_at', ['2025-02-29', '2025-04-29'])
    ->get();

orWhereBetween()

Doppar allows you to construct expressive, flexible queries using methods like orWhereBetween(). This method is particularly useful when you want to retrieve records that match either a specific condition or fall within a certain range.

Example: Combine exact match with a range

Post::query()
    ->where('status', ,'=', 'published')
    ->orWhereBetween('views', [100, 500])
    ->get();

This will return all posts that are either published or have between 100 and 500 views, inclusive.

orWhereNotBetween()

Similarly, orWhereNotBetween() is used when you want to retrieve records that match a condition or fall outside a given range. This adds more control to your filtering logic.

Post::query()
    ->where('status', ,'=', 'published')
    ->orWhereNotBetween('views', [100, 500])
    ->get();

This query retrieves posts that are either published or have a view count less than 100 or greater than 500.

whereNull()

In Doppar, the whereNull() method is used to filter records where a specific column has no value—i.e., it contains NULL. This is useful when you're identifying incomplete or pending data.

Example: Find Posts Without a Created Date

Post::query()
    ->whereNull('created_at')
    ->get();

This query retrieves all posts where the created_at field is NULL, which might indicate drafts or records that haven't been finalized yet.

whereNotNull()

Conversely, whereNotNull() filters records where a given column does contain a value. It's ideal for fetching entries that are already completed or published.

Example: Fetch Published Posts

Post::query()
    ->whereNotNull('published_at')
    ->get();

This retrieves all posts that have a value in the published_at field, meaning they’ve been published.

orWhereNull()

The orWhereNull() method in Eloquent is used to add an OR condition to the query, checking if a column is NULL. In your example

Post::query()
    ->where('status', '=', 'draft')
    ->orWhereNull('reviewed_at')
    ->get();

orWhereNotNull()

The orWhereNotNull() method in Eloquent is used to add an OR condition to the query, checking if a column is not NULL. In your example:

Post::query()
    ->where('status', '=', 'draft')
    ->orWhereNotNull('reviewed_at')
    ->get();

pluck()

The pluck() method in Doppar is a convenient way to extract the values of a single column from your query results. Instead of returning full model instances, pluck() pulls out just the values from the specified field—perfect for generating simple lists.

Example: Get All Post Titles

Post::query()->pluck('title');

This will return a collection of all titles from the posts table, such as:

[
    "How to Use Doppar",
    "Getting Started with PHP",
    "Understanding Eloquent Queries"
]

useRaw()

The useRaw() method allows you to run raw SQL queries with parameter bindings, which helps prevent SQL injection by safely binding parameters to the query.

User::query()->useRaw(
    'SELECT * FROM user WHERE created_at > ? AND status = ?',
    ['2023-01-01', 'active']
);

useRaw runs a custom SQL query that retrieves users created after January 1, 2023, and with an active status. The values 2023-01-01 and active are securely bound to the query to prevent SQL injection.

Query Filter Using match()

The match() method in Doppar offers a clean and expressive way to apply dynamic, reusable filters to your queries. It supports both simple key-value pairs and more complex conditions using callbacks, making it ideal for building powerful, customizable queries without cluttering your controller or model logic.

Basic Filtering

Post::match([
    'id' => 1,
    'user_id' => 1
])->get();

Applies standard where clauses for each key-value pair.

Advanced Filtering with Callback

Post::match(function ($query) {
    $query->where('views', '>', 100)
          ->whereBetween('created_at', ['2023-01-01', '2023-12-31']);
})->get();

Allows complex conditions inside a closure for more flexible logic.

Filter Using Request Data

Post::match($request->only(['title', 'user_id']))->paginate();

Automatically filters based on available request fields—great for search forms or filters.

Combining Simple and Complex Filters

Post::match([
    'user_id' => [1, 2, 3],
    'created_at' => null,
    'active' => function ($query) {
        $query->where('active', 1)
              ->orWhere('legacy', 1);
    }
])
->orderBy('created_at', 'desc')
->get();

The match() method enhances query readability and reduces repetitive condition-building, especially in service layers or controllers.

if() for Conditional Query Execution

In Doppar, the if() method provides a powerful and elegant way to conditionally add query constraints based on a given condition. This allows you to build more flexible and dynamic queries, improving code readability and reducing unnecessary complexity.

Doppar ORM's if() method allows you to conditionally add query constraints based on a given condition. If the condition evaluates to true, the corresponding query modification is applied; otherwise, it is skipped.

Basic Usage of if()

Post::query()
    ->if($request->has('date_range'), function($query) use ($request) {
        $query->whereBetween('created_at', [
            $request->input('date_range.start'),
            $request->input('date_range.end')
        ]);
    })
    ->get();

In this example, the if() method checks if the date_range is present in the request. If it is, the query will filter the posts based on the provided start and end dates. If not, the whereBetween() condition is skipped.

Conditional Query Modifications with a Default Case

You can also provide a default case to be applied when the condition is false or not provided:

Post::query()
    ->if($request->input('search'),
        fn($q) => $q->where('title', 'LIKE', "%{$request->search}%"), // If search is provided, filter by title
        fn($q) => $q->where('is_featured', '=', true) // If no search is provided, filter by featured status
    )
    ->get();

In this example:

• If a search parameter is provided, the query will search posts by title.
• If no search parameter is found, it defaults to filtering posts where is_featured is true.

How if() Works with Different Conditions

Will execute when the condition is truthy:

Post::query()
    ->if(true, fn($q) => $q->where('active', '=', true)) // Executes because true
    ->if('text', fn($q) => $q->where('title', '=', 'text')) // Executes because 'text' is truthy
    ->if(1, fn($q) => $q->where('views', '=', 1)) // Executes because 1 is truthy
    ->if([1], fn($q) => $q->whereIn('id', '=', [1])) // Executes because [1] is truthy
    ->get();

Will NOT execute when the condition is falsy:

Post::query()
    ->if(false, fn($q) => $q->where('active', '=', false)) // Does not execute because false
    ->if(0, fn($q) => $q->where('views', '=', 0)) // Does not execute because 0 is falsy
    ->if('', fn($q) => $q->where('title', '=', '')) // Does not execute because empty string is falsy
    ->if(null, fn($q) => $q->where('deleted_at', '=', null)) // Does not execute because null is falsy
    ->if([], fn($q) => $q->whereIn('id', '=',  [])) // Does not execute because empty array is falsy
    ->get();

This makes the if() method powerful for dynamically building queries based on various conditions. It allows for more concise and flexible query building without having to manually check each condition before applying the relevant query changes.

Insertion and Update

Inserts

Of course, when using Doppar, working with data isn't limited to just retrieving records — inserting new entries is just as important. Fortunately, Doppar makes this process straightforward. To add a new record to the database, you simply create a new instance of the model, assign values to its attributes, and then call the save() method:

use App\Models\User;

$user = new User();
$user->name = 'Mahedi Hasan';
$user->email = 'mahedi@doppar.com';
$user->save();

This inserts a new row into the users table with the specified name and email. The save() method takes care of generating and executing the appropriate SQL insert query behind the scenes, making data insertion clean and intuitive.

Alternatively, you may use the create method to "save" a new model using a single PHP statement. The inserted model instance will be returned to you by the create method:

use App\Models\User;

User::create([
    'name' => 'Doppar'
]);

Updates

The save() method in Doppar isn't just for inserting new records—it can also be used to update existing ones. To update a record, first retrieve the model instance, modify the attributes you want to change, and then call save(). Doppar will handle generating the appropriate UPDATE statement. Additionally, the updated_at timestamp will be refreshed automatically, so you don't need to set it manually:

use App\Models\Flight;

$flight = Flight::find(1);
$flight->name = 'Dhaka to Mumbai';
$flight->save();

Occasionally, you may need to update an existing model. The updateOrCreate() method, the updateOrCreate() method persists the model, so there's no need to manually call the save method.

The updateOrCreate() method is used to either update an existing record or create a new one if no matching record is found. It simplifies handling scenarios where you need to ensure a record exists with specific attributes while updating other fields.

$user = User::updateOrCreate(
    ['email' => 'howdy@doppar.com'], // attributes to match
    ['name' => 'Doppar'] // values to update/create
);

Mass Updates

Updates can also be performed against models that match a given query. In this example, that are active will be marked as delayed:

User::query()
    ->where('country', '=', 'bd')
    ->update([
        'code' =>  '+880'
    ]);

Updating Data with fill()

In Doppar, you can streamline the process of updating multiple attributes by using the fill() method. This method allows you to pass an array of key-value pairs representing the fields you want to update. After filling the model with new data, you simply call save() to persist the changes:

$user = User::find(1);
$user->fill([
    'name' => 'John',
    'email' => 'updated@doppar.com',
]);
$user->save();

Bulk Insert Using saveMany()

When you need to insert multiple records at once, Doppar provides the saveMany() method, which simplifies batch inserts. Instead of saving each record individually, you can pass an array of data, and Doppar will insert them all in a single operation.

Here’s an example of how you can use saveMany() to insert multiple users:

User::saveMany([
    ['name' => 'John', 'email' => 'john@example.com', 'password' => bcrypt('password')],
    ['name' => 'Jane', 'email' => 'jane@example.com', 'password' => bcrypt('password')],
    ['name' => 'Bob', 'email' => 'bob@example.com', 'password' => bcrypt('password')]
]);

This inserts all three users into the users table in one batch, making the process more efficient than inserting them one by one.

Chunk Size for Large Datasets

If you are working with a large dataset, inserting all records at once might lead to performance issues or memory limitations. In such cases, you can specify a chunk size as the second parameter to saveMany() to batch the inserts into smaller chunks. For example:

User::saveMany([
    ['name' => 'John', 'email' => 'john@example.com', 'password' => bcrypt('password')],
    ['name' => 'Jane', 'email' => 'jane@example.com', 'password' => bcrypt('password')],
    ['name' => 'Bob', 'email' => 'bob@example.com', 'password' => bcrypt('password')]
], 1000);

This will insert the records in chunks of 1000, helping prevent memory overflow and improving performance when dealing with a large volume of data.

Using saveMany() is a great way to handle bulk inserts in Doppar, ensuring your application remains efficient even with large datasets.

Mass Assignment

You may use the create method to "save" a new model using a single PHP statement. The inserted model instance will be returned to you by the method:

use App\Models\User;

$user = User::create([
    'name' => 'Nure',
]);

However, before using the create method, you will need to specify a $creatable property on your model class. These properties are required because all Eloquent models are protected against mass assignment vulnerabilities by default.

<?php

namespace App\Models;

use Phaseolies\Database\Eloquent\Model;

class User extends Model
{
    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $creatable = ['name'];
}

So, to get started, you should define which model attributes you want to make mass assignable. You may do this using the $creatable property on the model. For example, let's make the name attribute of our User model mass assignable:

Deleting Models

To delete a model, you may call the delete method on the model instance:

User::find(1)->delete();

Deleting an Existing Model by its Primary Key

In Doppar, if you already know the primary key(s) of the records you want to delete, there's no need to retrieve the models first. Instead, you can call the purge method directly. This method efficiently deletes records by their primary key and supports various input formats, including single IDs, multiple IDs, and arrays

User::purge(1);
User::purge(1, 2, 3);
User::purge([1, 2, 3]);

Deleting Models Using Queries

Of course, Doppar also allows you to perform bulk deletions by building a query that targets specific records. For instance, you can delete all users that are marked as inactive in a single operation. Just like with mass updates, these mass deletions are executed directly in the database.

User::query()
    ->where('active', '=', false)
    ->delete();

Aggregation

The Doppar ORM provides a rich set of aggregation functions that allow you to perform statistical and summary operations directly through your model queries. These methods help extract meaningful insights from your data without writing raw SQL, making your code clean, expressive, and efficient.

Total Sum of a Column

To calculate the sum of all values in a specific column (e.g., total views across all posts):

Post::query()->sum('views');

Average Value of a Column

To compute the average value of a column (e.g., average number of views):

Post::query()->avg('views');

Maximum Value in a Column

To get the highest value in a column (e.g., the most expensive product):

Product::query()->max('price');

Minimum Value in a Column

To get the lowest value in a column (e.g., cheapest product):

Product::query()->min('price');

Standard Deviation Calculation

To compute the standard deviation of values in a column (useful for measuring variability):

Product::query()->stdDev('price');

Variance Calculation

To calculate the variance of values (a squared measure of data spread):

Product::query()->variance('price');

Multiple Aggregations in One Query

To retrieve count, average, min, and max in a single grouped query:

Product::query()
    ->select([
        'COUNT(*) as count',
        'AVG(price) as avg_price',
        'MIN(price) as min_price',
        'MAX(price) as max_price'
    ])
    ->groupBy('variants')
    ->first();

This groups data by variants and returns aggregated stats for each group.

Fetching Distinct Rows

To get unique values from a column (e.g., all unique user IDs who posted):

Post::query()->distinct('user_id');

Calculating Conditional Sum

To sum values under a specific condition (e.g., sales where title is "maiores"):

Product::query()
    ->where('title', '=', 'maiores')
    ->sum('price');

Total Sales by Category

To aggregate sales per category by multiplying price and quantity:

return Product::query()
    ->select(['category_id', 'SUM(price * quantity) as total_sales'])
    ->groupBy('category_id')
    ->get();

Column Modification: increment() & decrement()

Incrementing a Column

To increase the value of a numeric column (e.g., views):

$post = Post::find(1);
$post->increment('views'); // Increments by 1 by default
$post->increment('views', 10); // Increments by 10

You can also update additional fields during the increment:

$post->increment('views', 1, [
    'updated_at' => date('Y-m-d H:i:s'),
    'modified_by' => Auth::id()
]);

Decrementing a Column

To decrease the value of a numeric column:

$post = Post::find(1);
$post->decrement('views'); // Decrements by 1 by default
$post->decrement('views', 10); // Decrements by 10

You can also attach extra updates when decrementing:

$post->decrement('views', 1, [
    'updated_at' => date('Y-m-d H:i:s'),
    'modified_by' => Auth::id()
]);

Doppar ORM empowers you to run comprehensive statistical queries, manage data updates, and build analytics-driven features with minimal effort. Whether you're building dashboards, reports, or tracking metrics, these aggregation tools are essential for maintaining efficient and elegant code.

Transform Eloquent Collection

The Doppar ORM offers expressive methods like map(), filter(), and each() to transform, filter, and iterate over Eloquent collections with ease. These methods provide fine control over the shape and content of your data after fetching it from the database.

map() – Transforming Collection Items

Use the map() method to transform each item in a collection. This is especially useful when you want to reformat or limit the fields returned in your response.

Example: Return Only the name of Each User

return User::all()
    ->map(function ($item) {
        return [
            'name' => $item->name
        ];
    });

Use case: Customize API responses or prepare data for front-end consumption by trimming down unnecessary fields.

filter() – Conditional Filtering

After transforming a collection, you can use filter() to return only items that match a given condition.

Example: Return Posts Where status = 1, With Only title and status

return Post::all()
    ->map(function ($item) {
        return [
            'title' => $item->title,
            'status' => $item->status
        ];
    })
    ->filter(function ($item) {
        return $item['status'] === 1;
    });

each() – Iterating Through Items Without Changing Structure

The each() method is used to perform actions on each item in the collection without modifying the collection itself. It's great for side effects like logging, notifications, or conditional updates.

Example: Run Logic on Each Latest Post

return Post::query()->newest()->get()
    ->each(function ($post) {
        // Perform some operation on each post
    });

Summary of Collection Utilities

Method	Purpose	Mutates Collection?
map()	Transforms each item	✅ Yes
filter()	Filters items by a condition	✅ Yes
each()	Iterates for side-effects (no return)	❌ No

These methods make Doppar’s Eloquent collection manipulation concise, readable, and highly adaptable for API responses, data formatting, and conditional logic.

Pagination

Pagination is an essential feature for working with large datasets, enabling you to fetch and display data in manageable chunks. This improves both performance and user experience, especially in applications that deal with lists like users, posts, products, or logs.

Doppar makes pagination seamless through the built-in paginate() method, which handles all the behind-the-scenes logic for splitting your results into pages.

Basic Usage

To paginate a dataset, simply call the paginate() method like as follows:

User::query()->paginate(1);

This will give you the response like this

{
    "data": [
        {
            "id": 4,
            "name": "Caden Jerde",
            "email": "stokes.ludwig@hotmail.com",
            "created_at": "2025-05-06 09:59:57",
            "updated_at": "2025-05-06 09:59:57"
        }
    ],
    "first_page_url": "http://example.com/users?page=1",
    "last_page_url": "http://example.com/users?page=21",
    "next_page_url": "http://example.com/users?page=2",
    "previous_page_url": null,
    "path": "http://example.com/users",
    "from": 1,
    "to": 1,
    "total": 21,
    "per_page": 1,
    "current_page": 1,
    "last_page": 21
}

Display Pagination in Views

When working with paginated data in your views, Doppar provides two convenient methods to render pagination links. These methods allow you to display navigation controls for moving between pages, ensuring a smooth user experience.

Available Methods:

• linkWithJumps() method generates pagination links with additional "jump" options, such as dropdown with paging. It is ideal for datasets with a large number of pages, as it allows users to quickly navigate to the beginning or end of the paginated results.
• links() This method generates standard pagination links, including "Previous" and "Next" buttons, along with page numbers. It is suitable for most use cases and provides a clean and simple navigation interface.

Now call the pagination for views

@foreach ($data['data'] as $user)
    <tr>
        <td>{{ $user->id }}</td>
        <td>{{ $user->name }}</td>
        <td>{{ $user->username }}</td>
        <td>{{ $user->email }}</td>
    </tr>
@endforeach

<!-- "Previous" and "Next" buttons, along with page numbers. -->
{!! paginator($data)->links() !!}

<!-- "Previous" and "Next" buttons, along with page jump options. -->
{!! paginator($data)->linkWithJumps() !!} //

Customize Default Pagination

Doppar provides a Bootstrap 5 pagination view by default. However, you can also customize this view to suit your needs. To customize the pagination view, Doppar offers the publish:pagination pool command. Running this command will create two files, jump.blade.php and number.blade.php, inside the resources/views/vendor/pagination folder. These files allow you to tailor the pagination design to match your application's style.

php pool publish:pagination

Once you modify the jump.blade.php and number.blade.php files, the changes will immediately reflect in your pagination view. This allows you to fully customize the appearance and behavior of the pagination links to align with your application's design and requirements. Feel free to update these files as needed to create a seamless and visually consistent user experience.

Database Transactions

A database transaction is a sequence of database operations that are executed as a single unit. Transactions ensure data integrity by following the ACID properties (Atomicity, Consistency, Isolation, Durability). If any operation within the transaction fails, the entire transaction is rolled back, preventing partial updates that could leave the database in an inconsistent state.

Doppar provides built-in support for handling database transactions using the DB::transaction() method, DB::beginTransaction(), DB::commit(), and DB::rollBack().

Using DB::transaction() for Simplicity

The DB::transaction() method automatically handles committing the transaction if no exception occurs and rolls it back if an exception is thrown.

DB::transaction(function () {
    $user = User::create($request->all());
    $post = Post::create([
        'user_id' => $user->id, 
        'title' => 'War is started'
    ]);
});

Manually Handling Transactions

In cases where more control is needed, transactions can be manually started using DB::beginTransaction(). The operations must then be explicitly committed or rolled back.

DB::beginTransaction();
try {
    $user = User::create([
        'name' => 'Mahedi',
        'email' => fake()->email,
        'password' => bcrypt('password'),
    ]);

    Post::create([
        'title' => 'My very first post',
        'user_id' => $user->id
    ]);

    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
    throw $e;
}

Handling Deadlocks with Transaction Retries

Deadlocks can occur when multiple transactions compete for the same database resources. Doppar allows setting a retry limit for transactions using a second parameter in DB::transaction().

DB::transaction(function () {
    // Operations that might deadlock
}, 3); // Will attempt up to 3 times before throwing an exception

This approach helps mitigate issues caused by deadlocks by retrying the transaction a set number of times before ultimately failing.

Using transactions properly ensures database consistency and prevents data corruption due to incomplete operations. Doppar provides flexible methods for handling transactions, allowing both automatic and manual control based on the use case.

Eloquent Join

In Doppar, Eloquent manual joins allow you to retrieve data from multiple tables based on a related column. The join method in Eloquent's Query Builder provides an easy way to combine records from different tables. This document explains how to perform various types of joins manually using Eloquent's Eloquent ORM and Query Builder.

Basic Join Example

A simple join operation can be performed using the join method to combine records from two tables based on a common key. Below is an example of joining users and posts tables:

$users = User::query()
    ->select('posts.*', 'users.name as user_name')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->get();

This will return a dataset containing user data which has at least one post.

Specifying Join Type

By default, Doppar performs an INNER JOIN. You can specify the type of join you want by passing the join type as an argument:

$users = User::query()
    ->join('posts', 'users.id', '=', 'posts.user_id', 'left')
    ->get();

Here, a LEFT JOIN is used to include all users, even if they do not have associated posts.

Applying Conditions in Joins

You can apply additional conditions to filter the joined data. The example below joins the posts table and fetches only the users who have published posts:

$users = User::query()
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->where('posts.published', '=', true)
    ->orderBy('users.name', 'ASC')
    ->get();

Performing Multiple Joins

You can join multiple tables in a single query. The example below joins the users, posts, and comments tables:

$users = User::query()
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->join('comments', 'posts.id', '=', 'comments.post_id')
    ->get();

This will return data containing users, their posts, and associated comments.

Selecting Specific Columns

To optimize queries and improve performance, you can select specific columns instead of retrieving all fields:

$users = User::query()
    ->select('users.name', 'posts.title')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->get();

This query retrieves only the users.name and posts.title fields, reducing the amount of data transferred.

DB Query

Doppar’s DB Facade, available via the Phaseolies\Support\Facades\DB namespace, provides a clean, expressive, and powerful interface to interact directly with your database—going beyond the ORM layer when you need low-level control or raw SQL flexibility.

Whether you're running raw queries, managing transactions, executing stored procedures, or querying database views, Doppar gives you a full toolkit under the DB facade.

Basic Database Utilities

Operation	Example	Description
Get all tables	DB::getTables()	Returns an array of all table names in the database
Check table existence	DB::tableExists('user')	Returns true if the specified table exists
Get table columns	DB::getTableColumns('user')	Returns a list of column names from a table
Get table from model	DB::getTable(new User())	Fetches the associated table name of a model
Get raw PDO connection	DB::getConnection()	Returns the underlying PDO object

Querying the Database with query()

The query() method provided by Doppar enables you to execute raw SQL statements with parameter binding to ensure safe and secure queries. This method returns a \Phaseolies\Support\Collection giving you full control over result handling.

The following example demonstrates how to execute a parameterized SQL query and retrieve the first matching row as an associative array:

<?php

use Phaseolies\Support\Facades\DB

$user = DB::query("SELECT * FROM users WHERE name = ?", ['Caden Jerde']);
$user->name;

This will return the first row where the name column matches "Kasper Snider", or null if no such row exists.

Get All Rows

In this example, all rows from the users table are retrieved and stored in the $users collection. If there are no matching rows, an empty array will be returned.

$users = DB::query("SELECT * FROM users");

The query() method returns a Collection, allowing you to fluently chain collection methods like map(), filter(), and others. This makes it easy to transform or manipulate the result set directly after querying the database.

If you expect multiple rows, you can use the map() method to transform each row into a custom structure or format.

<?php

use Phaseolies\Support\Facades\DB;

return DB::query("SELECT * FROM users")
    ->map(function ($data) {
        return [
            'name' => $data['name'] ?? 'default',
        ];
    });

Modifying the Database using execute()

Doppar provides execute() function and using it, you can modify database. See the basic example. Inserts a new user record. Returns the number of affected rows (1 if successful).

$inserted = DB::execute(
    "INSERT INTO users (name, email, password) VALUES (?, ?, ?)",
    ['John Doe', 'john@example.com', bcrypt('secret')]
);

You can use transaction here as well

DB::transaction(function () {
    $moved = DB::execute(
        "INSERT INTO archived_posts SELECT * FROM posts WHERE created_at < ?",
        [date('Y-m-d', strtotime('-1 year'))]
    );

    $deleted = DB::execute(
        "DELETE FROM posts WHERE created_at < ?",
        [date('Y-m-d', strtotime('-1 year'))]
    );

    echo "Archived {$moved} posts and deleted {$deleted} originals";
});

Executes multiple statements in a single transaction. Ensures either all queries succeed or none are committed.

Executing Stored Procedures

Doppar provides support for executing stored procedures through the procedure() method. This allows you to call database-stored routines directly, making it ideal for complex logic encapsulated within the database.

By default, procedure() returns a \Phaseolies\Support\Collection results.

$nestedResults = DB::procedure('sp_GetAllUsers')->all();

This will return the all users collection data.

Get first row

When you only need the first row from the first result set of a stored procedure, you can use the first() method after calling procedure(). This is useful for cases where the procedure returns a list, but you only care about the top result.

$firstUser = DB::procedure('sp_GetAllUsers')->first();

// with passing parameter
$firstUser = DB::executeProcedure('sp_GetAllUsers', [1])->first();

Get last row

When you only need the last row from the first result set of a stored procedure, you can use the last() method after calling procedure(). This is useful for cases where the procedure returns a list, but you only care about the last result.

$firstUser = DB::procedure('sp_GetAllUsers')->last();

Get the second result set (index 1) by passing 123 parameter

$stats = DB::procedure('sp_GetUserWithStats', [123])->resultSet(1);

With Multiple Params

Doppar's procedure() method supports stored procedures that return multiple result sets. The returned result object offers convenient methods to navigate and extract specific parts of the output.

$result = Database::procedure('get_multiple_results');
$result->resultSet(0); // First set
$result->resultSet(1); // Second set

// Get last row of first result set
$lastRow = $result->last();

// Get last row of second result set
$lastRowSet2 = $result->lastSet(1);

Executing View

Doppar makes it easy to query database views using the view() method. A view behaves like a virtual table and can be queried just like a regular table, often used to simplify complex joins or aggregations.

$stats = DB::view('vw_user_statistics');

View with WHERE Conditions

You can pass where condition in your custom view like

// View with single WHERE condition
$nyUsers = DB::view(
    'vw_user_locations', 
    ['state' => 'New York']
);

// Equivalent to: SELECT * FROM vw_user_locations WHERE state = 'New York'

// View with multiple WHERE conditions
$premiumNyUsers = DB::view(
    'vw_user_locations',
    [
        'state' => 'New York',
        'account_type' => 'premium'
    ]
);

// Equivalent to: 
// SELECT * FROM vw_user_locations 
// WHERE state = 'New York' AND account_type = 'premium'

View with Parameter Binding

You can also pass params with where condition as follows

// 4. Using parameter binding for security
$recentOrders = DB::view(
    'vw_recent_orders',
    ['status' => 'completed'],
    [':min_amount' => 100] // Additional parameters
);

// Equivalent to:
// SELECT * FROM vw_recent_orders 
// WHERE status = 'completed' AND amount > :min_amount

Summery

Feature	Method	Purpose
Raw Queries	query()	Run SELECT statements with parameter binding
Data Modification	execute()	Run INSERT, UPDATE, DELETE
Transactions	transaction()	Execute multiple queries safely
Stored Procedures	procedure()	Call stored procs with multiple results
Views	view()	Query DB views with filters and bindings
Utilities	getTables(), tableExists()	Explore schema

Doppar's DB Facade provides all the raw SQL power you need while preserving the developer-friendly syntax you love.