Stage 4

Eloquent Query Challenges

Implement complex Eloquent queries to solve real-world e-commerce scenarios.

Task

In this stage, you'll implement various Eloquent queries in routes/web.php. These queries will help you practice writing complex database queries using Eloquent.

Eloquent Query Challenges

Exercise 1: Get all products with their categories

routes/web.php (Exercise 1)
php
// Exercise 1: Get all products with their categories
Route::get('/query-1', function () {
    // TODO: Retrieve all products and eager load their categories
    // Hint: Use the with() method to eager load relationships
    
    // Your code here...
});

Exercise 2: Get all orders for a specific user with items and products

routes/web.php (Exercise 2)
php
// Exercise 2: Get all orders for a specific user with items and products
Route::get('/query-2', function () {
    $userId = 1; // You can change this or make it dynamic with a route parameter
    
    // TODO: Retrieve all orders for the specified user
    // Eager load the order items and their products
    // Hint: Use nested eager loading with with(['relation.nestedRelation'])
    
    // Your code here...
});

Exercise 3: Find the most popular products (highest quantity in order items)

routes/web.php (Exercise 3)
php
// Exercise 3: Find the most popular products (highest quantity in order items)
Route::get('/query-3', function () {
    // TODO: Find the most popular products based on the total quantity ordered
    // Hint: Use join(), selectRaw(), groupBy(), and orderByDesc()
    
    // Your code here...
});

Exercise 4: Find products with average rating above 4

routes/web.php (Exercise 4)
php
// Exercise 4: Find products with average rating above 4
Route::get('/query-4', function () {
    // TODO: Find products with an average rating above 4
    // Hint: Use join(), selectRaw(), groupBy(), and havingRaw()
    
    // Your code here...
});

Exercise 5: Find top spending customers

routes/web.php (Exercise 5)
php
// Exercise 5: Find top spending customers
Route::get('/query-5', function () {
    // TODO: Find the top 5 customers who have spent the most money
    // Hint: Use join(), selectRaw(), groupBy(), and orderByDesc()
    
    // Your code here...
});

Exercise 6: Find products in a specific category and its subcategories

routes/web.php (Exercise 6)
php
// Exercise 6: Find products in a specific category and its subcategories
Route::get('/query-6', function () {
    $categoryId = 1; // You can change this or make it dynamic with a route parameter
    
    // TODO: Find all products in the specified category and its subcategories
    // Hint: First get the category and its subcategories, then find products in those categories
    
    // Your code here...
});

Exercise 7: Find products that have never been ordered

routes/web.php (Exercise 7)
php
// Exercise 7: Find all products that have never been ordered
Route::get('/query-7', function () {
    // TODO: Find all products that have never been ordered
    // Hint: Use whereDoesntHave() to find models that don't have related models
    
    // Your code here...
});

Exercise 8: Find users who have products in wishlist but never ordered them

routes/web.php (Exercise 8)
php
// Exercise 8: Find users who have products in wishlist but never ordered them
Route::get('/query-8', function () {
    // TODO: Find users who have products in their wishlist but have never ordered those products
    // Hint: This is a complex query that requires nested whereHas() and whereDoesntHave()
    
    // Your code here...
});

Hint:

This is the most challenging query. You'll need to:
1. Find users who have wishlists
2. For those wishlists, check if they contain products
3. For those products, check if they have NOT been ordered by the same user
4. You'll need to use nested whereHas() and whereDoesntHave() with closures

Checkpoint 4

Add this route to verify your query implementations:

routes/web.php
php
Route::get('/check-queries', function () {
    $endpoints = [
        '/query-1', '/query-2', '/query-3', '/query-4',
        '/query-5', '/query-6', '/query-7', '/query-8'
    ];
    
    $implemented = [];
    foreach ($endpoints as $endpoint) {
        try {
            $routes = \Illuminate\Support\Facades\Route::getRoutes();
            $implemented[$endpoint] = $routes->hasNamedRoute($endpoint) || 
                                     collect($routes->getRoutes())->contains(function ($route) use ($endpoint) {
                                         return $route->uri() == ltrim($endpoint, '/');
                                     });
        } catch (\Exception $e) {
            $implemented[$endpoint] = false;
        }
    }
    
    return [
        'implemented_queries' => $implemented,
        'completion_percentage' => (count(array_filter($implemented)) / count($endpoints)) * 100 . '%'
    ];
});

Visit /check-queries and share a screenshot of the output on Slack. The output should show which queries you have implemented.

Final Submission

Create a GitHub repository with your project and submit the following:

  1. GitHub repository URL
  2. Screenshots of all checkpoint outputs
  3. A short summary (3-5 sentences) of what you learned and what was most challenging

Canvas Submission

Submit your GitHub repository link to the "Laravel Eloquent" assignment on Canvas LMS. Make sure your repository is public so it can be accessed for grading.