Query Builder

Miko's Query Builder provides a fluent interface for building SQL queries without writing raw SQL.

Query Methods Summary

Basic Queries

Select

// Select all columns
$users = User::query()->get();

// Select specific columns
$users = User::select('Id', 'Name', 'Email')->get();

// Select with alias
$users = User::select('Name as UserName', 'Email')->get();

// Distinct
$roles = User::select('Role')->distinct()->get();

Where Conditions

// Basic where
$users = User::where('IsActive', true)->get();

// With operator
$users = User::where('Age', '>=', 18)->get();

// Multiple conditions (AND)
$users = User::where('IsActive', true)
    ->where('Role', 'admin')
    ->get();

// OR condition
$users = User::where('Role', 'admin')
    ->orWhere('Role', 'moderator')
    ->get();

// Grouped AND ( ... )
$users = User::where('IsActive', true)
    ->where(function ($q) {
        $q->where('Role', 'admin')
          ->orWhere('Role', 'moderator');
    })
    ->get();
// SQL: WHERE IsActive = ? AND (Role = ? OR Role = ?)

// Grouped OR ( ... ) OR ( ... )
$users = User::where(function ($q) {
        $q->where('Status', 'active')
          ->where('Type', 'premium');
    })
    ->orWhere(function ($q) {
        $q->where('Status', 'trial')
          ->where('CreatedDate', '>', '2024-01-01');
    })
    ->get();
// SQL: WHERE (Status = ? AND Type = ?) OR (Status = ? AND CreatedDate > ?)

where(function ($q) { ... }) and orWhere(function ($q) { ... }) work on Model::where, MikoSet::where, the ORM query builder, and the database / fluent query builders.

Where Variations

// WHERE IN
$users = User::whereIn('Id', [1, 2, 3, 4, 5])->get();

// WHERE NOT IN
$users = User::whereNotIn('Role', ['banned', 'suspended'])->get();

// WHERE BETWEEN
$users = User::whereBetween('Age', 18, 65)->get();

// WHERE NULL
$users = User::whereNull('DeletedAt')->get();

// WHERE NOT NULL
$users = User::whereNotNull('EmailVerifiedAt')->get();

// WHERE LIKE
$users = User::whereLike('Name', 'john')->get(); // SQL: Name LIKE '%john%'

// WHERE DATE
$users = User::whereDate('CreatedDate', '2024-01-15')->get();

Ordering & Limiting

// Order by single column
$users = User::orderBy('Name')->get();
$users = User::orderBy('CreatedDate', 'desc')->get();

// Order by multiple columns
$users = User::orderBy('Role')
    ->orderBy('Name')
    ->get();

// Latest / Oldest (by CreatedDate)
$latestUser = User::latest()->first();
$oldestUser = User::oldest()->first();

// Limit
$users = User::take(10)->get();

// Offset
$users = User::skip(20)->take(10)->get();

Aggregates

// Count
$total = User::count();
$activeCount = User::where('IsActive', true)->count();

// Sum
$totalSales = Order::sum('TotalAmount');

// Average
$avgAge = User::avg('Age');

// Min / Max
$minPrice = Product::min('Price');
$maxPrice = Product::max('Price');

// Exists
$hasUsers = User::exists();
$hasAdmin = User::where('Role', 'admin')->exists();

Pagination

// Paginate results
$result = User::where('IsActive', true)
    ->orderBy('Name')
    ->paginate(20, 1); // per page, page number

echo $result['current_page'];
echo $result['last_page'];
echo $result['total'];

foreach ($result['data'] as $user) {
    echo $user->Name;
}

Chunking

Process large datasets efficiently:

// Process in chunks of 100
User::chunk(100, function($users) {
    foreach ($users as $user) {
        $user->sendNewsletter();
    }
});

// Chunk by ID (more efficient)
User::chunkById(100, function($users) {
    foreach ($users as $user) {
        $user->updateStats();
    }
});

Joins

// Inner join
$orders = Order::query()
    ->join('users', 'orders.UserId', '=', 'users.Id')
    ->select('orders.*', 'users.Name as UserName')
    ->get();

// Left join
$users = User::query()
    ->leftJoin('profiles', 'users.Id', '=', 'profiles.UserId')
    ->select('users.*', 'profiles.Bio')
    ->get();

Group By & Having

// Group by with count
$roleStats = User::query()
    ->select('Role')
    ->selectRaw('COUNT(*) as Count')
    ->groupBy('Role')
    ->get();

// Having clause
$popularRoles = User::query()
    ->select('Role')
    ->selectRaw('COUNT(*) as Count')
    ->groupBy('Role')
    ->having('Count', '>', 10)
    ->get();

Raw Expressions

// Select raw
$users = User::selectRaw('YEAR(CreatedDate) as Year, COUNT(*) as Count')
    ->groupBy('Year')
    ->get();

// Where raw
$users = User::whereRaw('Age > ? AND Role = ?', [18, 'admin'])->get();

// Order raw
$users = User::orderByRaw('FIELD(Role, "admin", "moderator", "user")')->get();