Where debate

convenient-by-default.php

<?php

public function searchBooksChained($filters) 
{
    $query = query(Book::class)->select();

    // Search term
    if (!empty($filters['search'])) {
        $query = $query->where(function($q) use ($filters) {
            $q->whereLike('title', "%{$filters['search']}%")
              ->orWhereLike('description', "%{$filters['search']}%");
        });
    }

    // Author filter
    if (!empty($filters['author'])) {
        $query = $query->whereLike('author_name', "%{$filters['author']}%");
    }

    // Genre filter
    if (!empty($filters['genre'])) {
        $genres = is_array($filters['genre']) ? $filters['genre'] : [$filters['genre']];
        $query = $query->whereIn('genre', $genres);
    }

    // Price range
    if (!empty($filters['min_price'])) {
        $query = $query->where('price', '>=', $filters['min_price']);
    }

    if (!empty($filters['max_price'])) {
        $query = $query->where('price', '<=', $filters['max_price']);
    }

    // Publication date range
    if (!empty($filters['published_after'])) {
        $query = $query->whereDate('published_date', '>=', $filters['published_after']);
    }

    // In stock filter
    if (isset($filters['in_stock']) && $filters['in_stock']) {
        $query = $query->where('stock_quantity', '>', 0);
    }

    // Rating filter - this is where method chaining shows limitations
    if (!empty($filters['min_rating'])) {
        $query = $query->whereHas('reviews', function($q) use ($filters) {
            $q->havingRaw('AVG(rating) >= ?', [$filters['min_rating']]);
        });
        // OR if your ORM doesn't support whereHas:
        // $query = $query->whereRaw('(SELECT AVG(rating) FROM reviews WHERE book_id = books.id) >= ?', [$filters['min_rating']]);
    }

    // Sorting
    if (!empty($filters['sort'])) {
        $sortDirection = $filters['sort_dir'] ?? 'asc';
        switch($filters['sort']) {
            case 'price':
                $query = $query->orderBy('price', $sortDirection);
                break;
            case 'title':
                $query = $query->orderBy('title', $sortDirection);
                break;
            case 'rating':
                $query = $query->orderByRaw("(SELECT AVG(rating) FROM reviews WHERE book_id = books.id) $sortDirection");
                break;
        }
    }

    return $query->get();
}

raw-by-default.php

<?php

public function searchBooksRaw($filters) 
{
    $query = query(Book::class)->select();
    $params = [];
    $conditions = [];

    // Search term
    if (!empty($filters['search'])) {
        $conditions[] = "(title LIKE ? OR description LIKE ?)";
        $params[] = "%{$filters['search']}%";
        $params[] = "%{$filters['search']}%";
    }

    // Author filter
    if (!empty($filters['author'])) {
        $conditions[] = "author_name LIKE ?";
        $params[] = "%{$filters['author']}%";
    }

    // Genre filter (multiple values possible)
    if (!empty($filters['genre'])) {
        $genres = is_array($filters['genre']) ? $filters['genre'] : [$filters['genre']];
        $genrePlaceholders = str_repeat('?,', count($genres) - 1) . '?';
        $conditions[] = "genre IN ($genrePlaceholders)";
        $params = array_merge($params, $genres);
    }

    // Price range
    if (!empty($filters['min_price'])) {
        $conditions[] = "price >= ?";
        $params[] = $filters['min_price'];
    }

    if (!empty($filters['max_price'])) {
        $conditions[] = "price <= ?";
        $params[] = $filters['max_price'];
    }

    // Publication date range
    if (!empty($filters['published_after'])) {
        $conditions[] = "published_date >= ?";
        $params[] = $filters['published_after'];
    }

    // In stock filter
    if (isset($filters['in_stock']) && $filters['in_stock']) {
        $conditions[] = "stock_quantity > 0";
    }

    // Rating filter
    if (!empty($filters['min_rating'])) {
        $conditions[] = "(SELECT AVG(rating) FROM reviews WHERE book_id = books.id) >= ?";
        $params[] = $filters['min_rating'];
    }

    // Build the final condition
    if (!empty($conditions)) {
        $finalCondition = implode(' AND ', $conditions);
        $query = $query->where($finalCondition, ...$params);
    }

    // Sorting
    if (!empty($filters['sort'])) {
        $sortDirection = $filters['sort_dir'] ?? 'asc';
        switch($filters['sort']) {
            case 'price':
                $query = $query->orderBy("price $sortDirection");
                break;
            case 'title':
                $query = $query->orderBy("title $sortDirection");
                break;
            case 'rating':
                $query = $query->orderBy("(SELECT AVG(rating) FROM reviews WHERE book_id = books.id) $sortDirection");
                break;
        }
    }

    return $query->get();
}