Query optimization in Laravel - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

Understanding Eager Loading Impact

Consider a Laravel application with Post and Comment models where each post has many comments.

What will be the main difference in database queries between these two code snippets?

1. $posts = Post::all(); foreach ($posts as $post) { echo $post->comments->count(); }
2. $posts = Post::with('comments')->get(); foreach ($posts as $post) { echo $post->comments->count(); }

A The first code runs one query for posts and one query per post for comments; the second runs two queries total using eager loading.

B Both codes run the same number of queries because Laravel caches comments automatically.

C The first code runs a single query joining posts and comments; the second runs multiple queries.

D The second code runs one query per comment, while the first runs only one query.

💡 Hint

Think about how Laravel loads related data lazily vs eagerly.

The first code triggers the N+1 query problem: one query for posts plus one query per post to get comments. The second code uses eager loading, running only two queries: one for posts and one for all related comments.

📝 Syntax

intermediate

Correct Use of Query Builder for Optimization

Which of the following Laravel query builder snippets correctly limits the selected columns to optimize the query?

A $users = DB::table('users')->get(['id', 'name']);

B $users = DB::table('users')->select('id', 'name')->get();

C $users = DB::table('users')->columns('id', 'name')->get();

D $users = DB::table('users')->only('id', 'name')->get();

💡 Hint

Check the Laravel documentation for selecting specific columns.

The select() method specifies which columns to retrieve. The get() method does not accept column names as parameters. Methods columns() and only() do not exist in Laravel's query builder.

🔧 Debug

advanced

Identifying Cause of Slow Query

A Laravel query is running very slowly:

$orders = Order::where('status', 'pending')->get(); foreach ($orders as $order) { echo $order->user->name; }

What is the most likely cause of the slow performance?

A The query is missing eager loading for the related user, causing N+1 queries.

B The <code>where</code> clause is invalid and causes a full table scan.

C The <code>get()</code> method is not fetching any data, causing errors.

D The <code>user</code> relation is not defined, causing a syntax error.

💡 Hint

Think about how Laravel loads related models when accessed inside a loop.

Accessing $order->user inside the loop without eager loading triggers a separate query for each order, causing the N+1 query problem and slow performance.

🧠 Conceptual

advanced

Effect of Using Chunking on Large Datasets

What is the main advantage of using Laravel's chunk() method when processing large datasets?

A It combines multiple queries into a single complex query for faster execution.

B It automatically caches all records to speed up repeated queries.

C It reduces memory usage by processing records in smaller batches instead of loading all at once.

D It disables eager loading to improve query speed.

💡 Hint

Think about how loading many records at once affects memory.

The chunk() method processes records in small groups, preventing high memory usage that occurs when loading large datasets all at once.

❓ state_output

expert

Output of Complex Query with Subquery Optimization

Given the following Laravel query, what will be the output?

$latestPosts = Post::select('id', 'title')
    ->whereIn('id', function ($query) {
        $query->selectRaw('MAX(id)')
              ->from('posts')
              ->groupBy('user_id');
    })->get();

foreach ($latestPosts as $post) {
    echo $post->title . ', ';
}

Assuming the posts table has multiple posts per user, what does this query return?

A Titles of posts with the lowest id for each user.

B All post titles from the posts table, separated by commas.

C An empty collection because the subquery is invalid.

D Titles of the latest post (highest id) for each user, separated by commas.

💡 Hint

Focus on what the subquery selects and how it filters the main query.

The subquery selects the maximum post id per user. The main query selects posts whose id is in that list, effectively fetching the latest post per user.

Practice

1. What is the main benefit of using eager loading in Laravel queries?

easy

A. It reduces the number of database queries by loading related data upfront.

B. It automatically caches all query results for faster access.

C. It encrypts the query results for security.

D. It sorts the query results alphabetically.

Solution

1. Step 1: Understand eager loading purpose

   Eager loading loads related data in one query instead of many separate queries.

2. Step 2: Identify benefit in query optimization

   By reducing the number of queries, it speeds up the app and lowers database load.

3. Final Answer:

   It reduces the number of database queries by loading related data upfront. -> Option A

4. Quick Check:

   Eager loading = fewer queries [OK]

Hint: Eager loading means load related data once, not many times [OK]

Common Mistakes:

• Confusing eager loading with caching
• Thinking it sorts or encrypts data
• Believing it loads data lazily

2. Which of the following is the correct way to select only the 'name' and 'email' columns from the users table in Laravel?

easy

A. $users = DB::table('users')->select('name', 'email')->get();

B. $users = DB::table('users')->columns('name', 'email')->fetch();

C. $users = DB::table('users')->only('name', 'email')->all();

D. $users = DB::table('users')->pick('name', 'email')->fetchAll();

Solution

1. Step 1: Recall Laravel query builder syntax

   The method to specify columns is select(), and to get results is get().

2. Step 2: Match correct syntax

   $users = DB::table('users')->select('name', 'email')->get(); uses select('name', 'email')->get(), which is correct Laravel syntax.

3. Final Answer:

   $users = DB::table('users')->select('name', 'email')->get(); -> Option A

4. Quick Check:

   Select columns = select() + get() [OK]

Hint: Use select() to pick columns, then get() to fetch [OK]

Common Mistakes:

• Using non-existent methods like columns() or only()
• Confusing fetch() with get()
• Trying to fetch all columns without select()

3. Given the code below, what will be the number of queries executed?

Post::with('comments')->where('status', 'published')->get();

medium

A. 1 query

B. Number of comments queries

C. Number of posts + 1 queries

D. 2 queries

Solution

1. Step 1: Understand eager loading with 'with'

   The with('comments') loads related comments in a separate query.

2. Step 2: Count queries executed

   One query fetches posts with status 'published', second query fetches all related comments.

3. Final Answer:

   2 queries -> Option D

4. Quick Check:

   with() = 2 queries [OK]

Hint: with() runs 2 queries: main + related data [OK]

Common Mistakes:

• Thinking with() runs only 1 query
• Assuming one query per post for comments
• Confusing with() with lazy loading

4. Identify the problem in this Laravel query that causes slow performance:

$users = User::all();
foreach ($users as $user) {
  echo $user->profile->bio;
}

medium

A. The profile relation does not exist in User model.

B. The foreach loop syntax is incorrect.

C. Using all() loads all users without eager loading profiles, causing N+1 queries.

D. Using echo inside loop slows down PHP execution.

Solution

1. Step 1: Analyze query and loop

   User::all() loads all users but does not load related profiles eagerly.

2. Step 2: Identify N+1 query problem

   Accessing $user->profile inside loop triggers one query per user, causing many queries.

3. Final Answer:

   Using all() loads all users without eager loading profiles, causing N+1 queries. -> Option C

4. Quick Check:

   all() without eager loading = N+1 queries [OK]

Hint: Use with('profile') to avoid N+1 queries [OK]

Common Mistakes:

• Blaming loop syntax instead of query
• Assuming profile relation missing without checking
• Thinking echo slows query performance

5. You want to optimize a query that fetches posts with their authors and comments, but only need the post title, author name, and comment content. Which approach is best?

hard

A. Run separate queries for posts, authors, and comments and merge results in PHP.

B. Use eager loading with select() on posts, authors, and comments to fetch only needed columns.

C. Use lazy loading to fetch authors and comments only when accessed.

D. Fetch all posts, authors, and comments without filtering columns to avoid missing data.

Solution

1. Step 1: Understand data needed

   Only post title, author name, and comment content are required, so select only these columns.

2. Step 2: Apply eager loading with column selection

   Use with() to eager load authors and comments, and select() to limit columns for optimization.

3. Final Answer:

   Use eager loading with select() on posts, authors, and comments to fetch only needed columns. -> Option B

4. Quick Check:

   Eager load + select columns = best optimization [OK]

Hint: Combine eager loading and select() to fetch only needed data [OK]

Common Mistakes:

• Fetching all columns wastes resources
• Using lazy loading causes many queries
• Merging in PHP is slower than optimized queries