ORM Query Examples

Advanced examples of using Miko's Query Builder for complex database queries.

---

Basic Queries

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

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

// Where clause
$activeUsers = User::where('IsActive', true)->get();

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

---

Complex Where Clauses

Grouped Conditions

$staff = User::where('IsActive', true)
    ->where(function ($q) {
        $q->where('Role', 'admin')
          ->orWhere('Role', 'moderator');
    })
    ->get();

Multiple OR Groups

// (Status = 'active' AND Type = 'premium') OR (Status = 'trial' AND CreatedDate > '2024-01-01')
$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();

Dynamic Conditions

$query = User::query();

if ($request['status'] ?? null) {
    $query->where('Status', $request['status']);
}

if ($request['role'] ?? null) {
    $query->where('Role', $request['role']);
}

if ($request['search'] ?? null) {
    $query->whereLike('Name', $request['search'])
          ->orWhereLike('Email', $request['search']);
}

$users = $query->orderBy('Name')->get();

---

Joins

Inner Join

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

Left Join

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

Multiple Joins

$orderDetails = Order::query()
    ->join('users', 'orders.UserId', '=', 'users.Id')
    ->join('order_items', 'orders.Id', '=', 'order_items.OrderId')
    ->join('products', 'order_items.ProductId', '=', 'products.Id')
    ->select(
        'orders.OrderNumber',
        'users.Name as CustomerName',
        'products.Name as ProductName',
        'order_items.Quantity',
        'order_items.UnitPrice'
    )
    ->where('orders.Id', $orderId)
    ->get();

---

Aggregates & Grouping

Basic Aggregates

$stats = [
    'total_users' => User::count(),
    'active_users' => User::where('IsActive', true)->count(),
    'avg_age' => User::avg('Age'),
    'total_revenue' => Order::where('Status', 'completed')->sum('TotalAmount')
];

Group By

// Users per role
$roleStats = User::query()
    ->select('Role')
    ->selectRaw('COUNT(*) as Count')
    ->groupBy('Role')
    ->get();

// Output: [['Role' => 'admin', 'Count' => 5], ['Role' => 'user', 'Count' => 100], ...]

Group By with Having

// Roles with more than 10 users
$popularRoles = User::query()
    ->select('Role')
    ->selectRaw('COUNT(*) as Count')
    ->groupBy('Role')
    ->having('Count', '>', 10)
    ->orderBy('Count', 'desc')
    ->get();

Complex Aggregates

// Monthly sales report
$monthlySales = Order::query()
    ->selectRaw('YEAR(CreatedDate) as Year')
    ->selectRaw('MONTH(CreatedDate) as Month')
    ->selectRaw('COUNT(*) as OrderCount')
    ->selectRaw('SUM(TotalAmount) as TotalSales')
    ->selectRaw('AVG(TotalAmount) as AvgOrderValue')
    ->where('Status', 'completed')
    ->groupBy('Year', 'Month')
    ->orderBy('Year', 'desc')
    ->orderBy('Month', 'desc')
    ->get();

---

Subqueries

Where In Subquery

// Users who have placed orders over $1000
$bigSpenders = User::whereIn('Id', function($query) {
    $query->select('UserId')
          ->from('orders')
          ->where('TotalAmount', '>', 1000)
          ->groupBy('UserId');
})->get();

Where Exists

// Users with at least one order
$customersWithOrders = User::whereExists(function($query) {
    $query->select('Id')
          ->from('orders')
          ->whereColumn('orders.UserId', 'users.Id');
})->get();

---

Raw Expressions

Select Raw

// Calculate age from birth date
$users = User::query()
    ->select('Name', 'Email')
    ->selectRaw('TIMESTAMPDIFF(YEAR, BirthDate, CURDATE()) as Age')
    ->get();

Where Raw

// Full-text search (MySQL)
$results = Article::whereRaw(
    "MATCH(Title, Content) AGAINST(? IN BOOLEAN MODE)",
    ['+php +mysql']
)->get();

// Date functions
$todayOrders = Order::whereRaw('DATE(CreatedDate) = CURDATE()')->get();

Order Raw

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

// Random order
$randomUsers = User::orderByRaw('RAND()')->take(5)->get();

---

Pagination Examples

Basic Pagination

$page = (int)($_GET['page'] ?? 1);
$perPage = 20;

$result = User::where('IsActive', true)
    ->orderBy('Name')
    ->paginate($perPage, $page);

// Build pagination links
$links = [];
for ($i = 1; $i <= $result['last_page']; $i++) {
    $links[] = [
        'page' => $i,
        'url' => "/users?page=$i",
        'active' => $i === $result['current_page']
    ];
}

Cursor Pagination (for large datasets)

$lastId = (int)($_GET['after'] ?? 0);
$limit = 20;

$users = User::where('Id', '>', $lastId)
    ->orderBy('Id')
    ->take($limit)
    ->get();

$nextCursor = !empty($users) ? end($users)->Id : null;

---

Search Implementation

function searchUsers(string $query, array $filters = []): array
{
    $builder = User::query();
    
    // Text search
    if ($query) {
        $builder->whereLike('Name', $query)
              ->orWhereLike('Email', $query)
              ->orWhereLike('Phone', $query);
    }
    
    // Filters
    if ($filters['role'] ?? null) {
        $builder->where('Role', $filters['role']);
    }
    
    if ($filters['status'] ?? null) {
        $builder->where('IsActive', $filters['status'] === 'active');
    }
    
    if ($filters['created_from'] ?? null) {
        $builder->where('CreatedDate', '>=', $filters['created_from']);
    }
    
    if ($filters['created_to'] ?? null) {
        $builder->where('CreatedDate', '<=', $filters['created_to']);
    }
    
    // Sorting
    $sortField = $filters['sort'] ?? 'CreatedDate';
    $sortDir = $filters['order'] ?? 'desc';
    $builder->orderBy($sortField, $sortDir);
    
    // Pagination
    $page = (int)($filters['page'] ?? 1);
    $perPage = (int)($filters['per_page'] ?? 20);
    
    return $builder->paginate($perPage, $page);
}

---

Report Queries

Sales Dashboard

function getSalesDashboard(): array
{
    $today = date('Y-m-d');
    $thisMonth = date('Y-m-01');
    $thisYear = date('Y-01-01');
    
    return [
        'today' => [
            'orders' => Order::whereDate('CreatedDate', $today)->count(),
            'revenue' => Order::whereDate('CreatedDate', $today)
                ->where('Status', 'completed')
                ->sum('TotalAmount')
        ],
        'this_month' => [
            'orders' => Order::where('CreatedDate', '>=', $thisMonth)->count(),
            'revenue' => Order::where('CreatedDate', '>=', $thisMonth)
                ->where('Status', 'completed')
                ->sum('TotalAmount')
        ],
        'this_year' => [
            'orders' => Order::where('CreatedDate', '>=', $thisYear)->count(),
            'revenue' => Order::where('CreatedDate', '>=', $thisYear)
                ->where('Status', 'completed')
                ->sum('TotalAmount')
        ],
        'top_products' => OrderItem::query()
            ->join('products', 'order_items.ProductId', '=', 'products.Id')
            ->select('products.Name')
            ->selectRaw('SUM(order_items.Quantity) as TotalSold')
            ->selectRaw('SUM(order_items.Quantity * order_items.UnitPrice) as Revenue')
            ->groupBy('products.Id', 'products.Name')
            ->orderBy('TotalSold', 'desc')
            ->take(10)
            ->get()
    ];
}