Query Builder vs. Eloquent Model in Laravel Query optimization code example from several project

 

1. Query Builder

Query Builder provides a direct way to interact with the database using SQL-like queries. It does not require defining models and relationships.

When to Use Query Builder?

✅ When performance is critical (especially for large datasets).
✅ When executing complex queries (e.g., JOIN, GROUP BY, HAVING).
✅ When fetching raw data without needing relationships or models.
✅ When dealing with reports, analytics, or bulk operations.

Example of Query Builder:

$users = DB::table('users')->where('status', 'active')->get();

✅ Query Builder is faster than Eloquent because it does not load extra objects, relationships, or unnecessary data.

---

2. Eloquent ORM

Eloquent is Laravel’s Object-Relational Mapping (ORM) that allows you to work with database tables as if they were PHP objects.

When to Use Eloquent?

✅ When working with models and relationships (like hasOne, belongsTo).
✅ When you need an expressive and readable syntax for database operations.
✅ When you want to leverage Laravel’s built-in features like accessors, mutators, scopes, and event handling.
✅ When developing standard CRUD applications.

Example of Eloquent:

$users = User::where('status', 'active')->get();

✅ Eloquent makes the code more readable and maintainable.
❌ But Eloquent can be slower than Query Builder because it loads additional model data.

---

Performance Comparison

Feature	Query Builder	Eloquent Model
Performance	✅ Faster (No overhead)	❌ Slower (Loads models & relationships)
Readability	❌ Complex Queries	✅ More readable & expressive
Relationships	❌ Manual Joins Required	✅ Built-in relationship methods
Use Case	Complex queries, Reports	Standard CRUD, Object-Oriented Approach

---

Which One Should You Use?

• For standard CRUD operations: 🏆 Use Eloquent Model (more readable, easy to maintain).

• For complex queries, bulk updates, or reports: 🏆 Use Query Builder (better performance).

• For raw SQL or stored procedures: 🏆 Use DB::select() for direct queries.

---

Hybrid Approach (Using Both)

You can combine Eloquent with Query Builder for efficiency. Example:

$users = User::select('id', 'name', 'email')
    ->where('status', 'active')
    ->get();

This optimizes performance while still using Eloquent.

---

Final Verdict:

✔ If you prioritize speed, use Query Builder.
✔ If you prioritize readability & maintainability, use Eloquent.
✔ For best results, mix both based on the situation.

👉 Pro Tip: If your application is getting slow, optimize Eloquent queries using select(), chunk(), cache(), or even raw queries when necessary.

==========================================================

Here are advanced query optimization techniques in Laravel with both Query Builder and Eloquent Model approaches. These techniques will help you handle complex queries efficiently.

Scenario:

Imagine you have an e-commerce system where you need to:

• Fetch products with their category, total sales count, and average rating.

• Only fetch active products that have at least 5 sales.

• Sort products by highest rating first, then by most sales.

• Implement pagination for better performance.

---

1️⃣ Query Builder Approach (Optimized for Speed)

$products = DB::table('products')
    ->join('categories', 'products.category_id', '=', 'categories.id')
    ->leftJoin('sales', 'products.id', '=', 'sales.product_id')
    ->leftJoin('reviews', 'products.id', '=', 'reviews.product_id')
    ->select(
        'products.id',
        'products.name',
        'categories.name as category',
        DB::raw('COUNT(sales.id) as total_sales'),
        DB::raw('AVG(reviews.rating) as average_rating')
    )
    ->where('products.status', 'active')
    ->groupBy('products.id', 'products.name', 'categories.name')
    ->havingRaw('COUNT(sales.id) >= ?', [5])
    ->orderByDesc('average_rating')
    ->orderByDesc('total_sales')
    ->paginate(10);

✅ Optimizations Used:

✔ JOIN instead of multiple queries for efficient data retrieval.
✔ COUNT(sales.id) for total sales instead of looping in PHP.
✔ AVG(reviews.rating) for product rating using DB::raw().
✔ HAVING COUNT(sales.id) >= 5 to filter high-selling products.
✔ ORDER BY optimized to sort by rating first, then sales.
✔ paginate(10) to limit records per page, reducing load.

---

2️⃣ Eloquent Model Approach (Readable & Maintainable)

$products = Product::with(['category', 'sales', 'reviews'])
    ->select('products.*')
    ->withCount('sales as total_sales')
    ->withAvg('reviews as average_rating', 'rating')
    ->where('status', 'active')
    ->having('total_sales', '>=', 5)
    ->orderByDesc('average_rating')
    ->orderByDesc('total_sales')
    ->paginate(10);

✅ Optimizations Used in Eloquent:

✔ with(['category', 'sales', 'reviews']) for eager loading (prevents N+1 query issue).
✔ withCount('sales as total_sales') instead of running multiple queries for sales count.
✔ withAvg('reviews as average_rating', 'rating') for efficient rating calculation.
✔ having('total_sales', '>=', 5) to filter high-selling products.
✔ orderByDesc('average_rating') & orderByDesc('total_sales') for sorting.
✔ paginate(10) for performance-friendly pagination.

---

🔍 Performance Comparison

Feature	Query Builder ✅ (Fast)	Eloquent ❌ (Slower)
Performance	Faster (raw SQL)	Slower (loads models)
Readability	Harder to read	Easier to maintain
Eager Loading	Manual joins required	with() handles N+1 issue
Aggregations	DB::raw() needed	withCount() & withAvg()
Pagination	Efficient	Slightly heavier

---

🚀 When to Use Which?

• Query Builder: ✅ Best for complex reports, huge datasets, and high-performance queries.

• Eloquent: ✅ Best for CRUD operations, small-to-medium datasets, and developer-friendly code.

• Hybrid Approach: Use Query Builder for heavy queries & Eloquent for normal operations.

---

🔥 Extra Optimization Tips

1. Use Indexing in Database: Index frequently queried columns like status, category_id.

2. Use Caching: Store frequently accessed queries in Redis or Laravel Cache.

3. Chunk Large Queries: Use chunk() for bulk data processing.

   
   Product::where('status', 'active')->chunk(100, function ($products) {
       foreach ($products as $product) {
           // Process each product
       }
   });
   

4. Avoid Loading Unnecessary Columns: Use select() to fetch only required fields.

   Product::select('id', 'name', 'price')->where('status', 'active')->get();
   

5. Lazy vs. Eager Loading: Use with() to avoid N+1 queries.

   $orders = Order::with('user')->get(); // Eager loading
   

---

Final Thoughts

✔ Use Query Builder for high-performance queries, reports, and analytics.
✔ Use Eloquent for regular CRUD operations with relationships.
✔ Optimize both using eager loading, pagination, and caching.

Here are more complex query optimization examples using Query Builder and Eloquent in Laravel, covering different real-world scenarios.

---

1️⃣ E-Commerce System: Fetch Products with Inventory & Discounted Price

Goal:

• Fetch products with category, inventory stock, discount price, and total sales.

• Show only in-stock products and apply a discount when available.

• Sort by highest discount percentage.

✅ Query Builder Approach (Faster & Optimized)

$products = DB::table('products')
    ->join('categories', 'products.category_id', '=', 'categories.id')
    ->leftJoin('inventory', 'products.id', '=', 'inventory.product_id')
    ->leftJoin('sales', 'products.id', '=', 'sales.product_id')
    ->leftJoin('discounts', 'products.id', '=', 'discounts.product_id')
    ->select(
        'products.id',
        'products.name',
        'categories.name as category',
        'inventory.stock',
        'products.price',
        DB::raw('IFNULL(discounts.percentage, 0) as discount_percentage'),
        DB::raw('products.price - (products.price * IFNULL(discounts.percentage, 0) / 100) as discounted_price'),
        DB::raw('COUNT(sales.id) as total_sales')
    )
    ->where('inventory.stock', '>', 0)
    ->groupBy('products.id', 'products.name', 'categories.name', 'inventory.stock', 'products.price', 'discount_percentage', 'discounted_price')
    ->orderByDesc('discount_percentage')
    ->paginate(10);

---

✅ Eloquent Approach (Readable & Maintainable)

$products = Product::with(['category', 'inventory', 'sales', 'discount'])
    ->select('products.*')
    ->withCount('sales as total_sales')
    ->withSum('inventory as stock', 'stock')
    ->with(['discount' => function ($query) {
        $query->select('product_id', 'percentage');
    }])
    ->whereHas('inventory', function ($query) {
        $query->where('stock', '>', 0);
    })
    ->orderByDesc('discount.percentage')
    ->paginate(10);

---

2️⃣ Social Media Platform: Fetch Posts with Comments & Likes Count

Goal:

• Get posts with user details, comment count, and like count.

• Fetch only public posts created within the last 30 days.

• Sort by most liked posts first.

✅ Query Builder Approach

$posts = DB::table('posts')
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
    ->leftJoin('likes', 'posts.id', '=', 'likes.post_id')
    ->select(
        'posts.id',
        'posts.title',
        'users.name as author',
        DB::raw('COUNT(DISTINCT comments.id) as total_comments'),
        DB::raw('COUNT(DISTINCT likes.id) as total_likes')
    )
    ->where('posts.privacy', '=', 'public')
    ->where('posts.created_at', '>=', now()->subDays(30))
    ->groupBy('posts.id', 'posts.title', 'users.name')
    ->orderByDesc('total_likes')
    ->paginate(10);

---

✅ Eloquent Approach

$posts = Post::with(['user', 'comments', 'likes'])
    ->select('posts.*')
    ->withCount('comments as total_comments')
    ->withCount('likes as total_likes')
    ->where('privacy', 'public')
    ->where('created_at', '>=', now()->subDays(30))
    ->orderByDesc('total_likes')
    ->paginate(10);

---

3️⃣ Banking System: Fetch Users with Their Total Transactions & Balance

Goal:

• Fetch users with their total deposits, withdrawals, and balance.

• Exclude users with zero transactions.

• Sort by highest balance first.

✅ Query Builder Approach

$users = DB::table('users')
    ->join('transactions', 'users.id', '=', 'transactions.user_id')
    ->select(
        'users.id',
        'users.name',
        DB::raw('SUM(CASE WHEN transactions.type = "deposit" THEN transactions.amount ELSE 0 END) as total_deposit'),
        DB::raw('SUM(CASE WHEN transactions.type = "withdrawal" THEN transactions.amount ELSE 0 END) as total_withdrawal'),
        DB::raw('SUM(CASE WHEN transactions.type = "deposit" THEN transactions.amount ELSE -transactions.amount END) as balance')
    )
    ->groupBy('users.id', 'users.name')
    ->having('balance', '>', 0)
    ->orderByDesc('balance')
    ->paginate(10);

---

✅ Eloquent Approach

$users = User::with(['transactions'])
    ->select('users.*')
    ->withSum(['transactions as total_deposit' => function ($query) {
        $query->where('type', 'deposit');
    }], 'amount')
    ->withSum(['transactions as total_withdrawal' => function ($query) {
        $query->where('type', 'withdrawal');
    }], 'amount')
    ->withSum(['transactions as balance' => function ($query) {
        $query->select(DB::raw('SUM(CASE WHEN type = "deposit" THEN amount ELSE -amount END)'));
    }], 'amount')
    ->having('balance', '>', 0)
    ->orderByDesc('balance')
    ->paginate(10);

---

4️⃣ HR System: Fetch Employees with Attendance & Leave Count

Goal:

• Get employees with attendance count and leave count.

• Show only employees who have worked for at least 20 days in the month.

• Sort by highest attendance first.

✅ Query Builder Approach

$employees = DB::table('employees')
    ->leftJoin('attendance', 'employees.id', '=', 'attendance.employee_id')
    ->leftJoin('leaves', 'employees.id', '=', 'leaves.employee_id')
    ->select(
        'employees.id',
        'employees.name',
        DB::raw('COUNT(DISTINCT attendance.id) as total_attendance'),
        DB::raw('COUNT(DISTINCT leaves.id) as total_leaves')
    )
    ->whereMonth('attendance.date', '=', date('m'))
    ->groupBy('employees.id', 'employees.name')
    ->having('total_attendance', '>=', 20)
    ->orderByDesc('total_attendance')
    ->paginate(10);

---

✅ Eloquent Approach

$employees = Employee::with(['attendance', 'leaves'])
    ->select('employees.*')
    ->withCount([
        'attendance as total_attendance' => function ($query) {
            $query->whereMonth('date', date('m'));
        },
        'leaves as total_leaves'
    ])
    ->having('total_attendance', '>=', 20)
    ->orderByDesc('total_attendance')
    ->paginate(10);

---

🚀 Advanced Optimization Techniques

1. Use Indexing in Database:

   sql
   CREATE INDEX idx_status ON products(status);
   CREATE INDEX idx_user_transactions ON transactions(user_id);
   

2. Use Caching (Redis, Laravel Cache):

   
   Cache::remember('top_products', 60, function () {
       return Product::orderByDesc('sales_count')->limit(10)->get();
   });
   

3. Chunk Large Queries:

   
   User::chunk(500, function ($users) {
       foreach ($users as $user) {
           // Process each user
       }
   });
   

4. Use select() to Avoid Loading Unnecessary Columns:

   User::select('id', 'name', 'email')->get();
   

---

📌 Final Verdict

• Use Query Builder ✅ when working with large datasets and need maximum speed.

• Use Eloquent ✅ when working with CRUD operations and need developer-friendly syntax.

• Use a hybrid approach 🏆 to combine performance & maintainability.

Here are more complex query optimization examples from large-scale projects like E-commerce, Banking, Social Media, and Enterprise Resource Planning (ERP) Systems using Query Builder and Eloquent.

---

1️⃣ E-Commerce System: Fetch Orders with Customer, Payment & Shipping Details

Goal:

• Fetch orders with customer details, total items, total amount, payment status, and shipping details.

• Show only completed orders from the last 3 months.

• Sort by highest total amount.

✅ Query Builder Approach (Faster & Optimized)

$orders = DB::table('orders')
    ->join('customers', 'orders.customer_id', '=', 'customers.id')
    ->join('payments', 'orders.id', '=', 'payments.order_id')
    ->join('shippings', 'orders.id', '=', 'shippings.order_id')
    ->join('order_items', 'orders.id', '=', 'order_items.order_id')
    ->select(
        'orders.id',
        'customers.name as customer_name',
        'customers.email as customer_email',
        'payments.status as payment_status',
        'shippings.address as shipping_address',
        DB::raw('COUNT(order_items.id) as total_items'),
        DB::raw('SUM(order_items.price * order_items.quantity) as total_amount')
    )
    ->where('orders.status', '=', 'completed')
    ->where('orders.created_at', '>=', now()->subMonths(3))
    ->groupBy('orders.id', 'customers.name', 'customers.email', 'payments.status', 'shippings.address')
    ->orderByDesc('total_amount')
    ->paginate(10);

---

✅ Eloquent Approach (Readable & Maintainable)

$orders = Order::with(['customer', 'payment', 'shipping', 'items'])
    ->select('orders.*')
    ->withCount('items as total_items')
    ->withSum('items as total_amount', DB::raw('price * quantity'))
    ->where('status', 'completed')
    ->where('created_at', '>=', now()->subMonths(3))
    ->orderByDesc('total_amount')
    ->paginate(10);

---

2️⃣ Banking System: Fetch Customers with Their Total Transactions & Loan Balances

Goal:

• Fetch customers with their total deposits, total withdrawals, total loans, and remaining balance.

• Exclude customers with zero transactions.

• Sort by highest balance first.

✅ Query Builder Approach

$customers = DB::table('customers')
    ->leftJoin('transactions', 'customers.id', '=', 'transactions.customer_id')
    ->leftJoin('loans', 'customers.id', '=', 'loans.customer_id')
    ->select(
        'customers.id',
        'customers.name',
        'customers.email',
        DB::raw('SUM(CASE WHEN transactions.type = "deposit" THEN transactions.amount ELSE 0 END) as total_deposit'),
        DB::raw('SUM(CASE WHEN transactions.type = "withdrawal" THEN transactions.amount ELSE 0 END) as total_withdrawal'),
        DB::raw('SUM(loans.amount) as total_loans'),
        DB::raw('(SUM(CASE WHEN transactions.type = "deposit" THEN transactions.amount ELSE 0 END) - 
                 SUM(CASE WHEN transactions.type = "withdrawal" THEN transactions.amount ELSE 0 END) - 
                 SUM(loans.amount)) as balance')
    )
    ->groupBy('customers.id', 'customers.name', 'customers.email')
    ->having('balance', '>', 0)
    ->orderByDesc('balance')
    ->paginate(10);

---

✅ Eloquent Approach

$customers = Customer::with(['transactions', 'loans'])
    ->select('customers.*')
    ->withSum(['transactions as total_deposit' => function ($query) {
        $query->where('type', 'deposit');
    }], 'amount')
    ->withSum(['transactions as total_withdrawal' => function ($query) {
        $query->where('type', 'withdrawal');
    }], 'amount')
    ->withSum('loans as total_loans', 'amount')
    ->withSum(['transactions as balance' => function ($query) {
        $query->select(DB::raw('SUM(CASE WHEN type = "deposit" THEN amount ELSE -amount END)'));
    }], 'amount')
    ->having('balance', '>', 0)
    ->orderByDesc('balance')
    ->paginate(10);

---

3️⃣ Social Media System: Fetch Most Popular Posts with Engagements

Goal:

• Fetch posts with user details, total comments, total likes, and total shares.

• Only fetch public posts from the last 7 days.

• Sort by total engagement score (likes + comments + shares).

✅ Query Builder Approach

$posts = DB::table('posts')
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->leftJoin('comments', 'posts.id', '=', 'comments.post_id')
    ->leftJoin('likes', 'posts.id', '=', 'likes.post_id')
    ->leftJoin('shares', 'posts.id', '=', 'shares.post_id')
    ->select(
        'posts.id',
        'posts.title',
        'users.name as author',
        DB::raw('COUNT(DISTINCT comments.id) as total_comments'),
        DB::raw('COUNT(DISTINCT likes.id) as total_likes'),
        DB::raw('COUNT(DISTINCT shares.id) as total_shares'),
        DB::raw('(COUNT(DISTINCT likes.id) + COUNT(DISTINCT comments.id) + COUNT(DISTINCT shares.id)) as engagement_score')
    )
    ->where('posts.privacy', '=', 'public')
    ->where('posts.created_at', '>=', now()->subDays(7))
    ->groupBy('posts.id', 'posts.title', 'users.name')
    ->orderByDesc('engagement_score')
    ->paginate(10);

---

✅ Eloquent Approach

$posts = Post::with(['user', 'comments', 'likes', 'shares'])
    ->select('posts.*')
    ->withCount('comments as total_comments')
    ->withCount('likes as total_likes')
    ->withCount('shares as total_shares')
    ->where('privacy', 'public')
    ->where('created_at', '>=', now()->subDays(7))
    ->orderByDesc(DB::raw('(total_likes + total_comments + total_shares)'))
    ->paginate(10);

---

🚀 Large Project Optimization Techniques

✅ 1. Use Indexing in Database

sql
CREATE INDEX idx_order_status ON orders(status);
CREATE INDEX idx_user_transactions ON transactions(user_id);

✅ 2. Use Caching (Redis, Laravel Cache)

Cache::remember('top_orders', 60, function () {
    return Order::orderByDesc('total_amount')->limit(10)->get();
});

✅ 3. Use Chunking for Large Queries

User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        // Process user data
    }
});

✅ 4. Use select() to Avoid Unnecessary Data Load

User::select('id', 'name', 'email')->get();

---

📌 Final Verdict

• Use Query Builder ✅ when working with large datasets that require maximum speed.

• Use Eloquent ✅ for CRUD operations and developer-friendly syntax.

• Use Hybrid Approach 🏆 by combining both for better performance & maintainability.