Ordering and grouping in Laravel - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What is the output of this Laravel Eloquent query?

Consider the following Eloquent query in Laravel:

$results = User::select('role', DB::raw('count(*) as total'))
    ->groupBy('role')
    ->orderBy('total', 'desc')
    ->get();

return $results->toArray();

What does this query return?

Laravel

use Illuminate\Support\Facades\DB;

$results = User::select('role', DB::raw('count(*) as total'))
    ->groupBy('role')
    ->orderBy('total', 'desc')
    ->get();

return $results->toArray();

A A syntax error because 'total' cannot be used in orderBy after groupBy.

B An array of users ordered alphabetically by role name.

C An array of all users with a total count of all users appended to each record.

D An array of roles with their user counts, ordered from highest to lowest count.

💡 Hint

Think about what groupBy and orderBy do together in Eloquent queries.

The query groups users by their role, counts how many users each role has, and orders the results by that count descending. So the output is a list of roles with their user counts from highest to lowest.

📝 Syntax

intermediate

Which option correctly groups and orders users by status and creation date?

You want to group users by their 'status' and order each group by the earliest 'created_at' date ascending. Which Eloquent query is correct?

A

User::select('status', DB::raw('min(created_at) as earliest'))
    ->groupBy('status')
    ->orderBy('earliest', 'asc')
    ->get();

B User::orderBy('created_at', 'asc')->groupBy('status')->get();

C User::groupBy('status')->orderBy('created_at', 'asc')->get();

D User::groupBy('status')->orderByRaw('MIN(created_at) ASC')->get();

💡 Hint

Remember that ordering by an aggregate requires selecting it first.

Option A correctly selects the minimum created_at per status group, groups by status, and orders by that minimum date ascending. Other options either misuse orderBy or groupBy.

🔧 Debug

advanced

Why does this Laravel query throw an SQL error?

Given this Laravel query:

$users = User::select('status', DB::raw('count(*) as total'))
    ->orderBy('total', 'desc')
    ->groupBy('status')
    ->get();

It throws an SQL error about 'total' in ORDER BY. Why?

Laravel

use Illuminate\Support\Facades\DB;

$users = User::select('status', DB::raw('count(*) as total'))
    ->orderBy('total', 'desc')
    ->groupBy('status')
    ->get();

A Because 'total' is not a column in the users table, orderBy cannot use it.

B Because orderBy is called before groupBy, the alias 'total' is not recognized in ORDER BY.

C Because groupBy must come before select in the query chain.

D Because count(*) cannot be aliased in select when using groupBy.

💡 Hint

Think about the order SQL processes clauses and how Laravel builds the query.

In SQL, ORDER BY is processed after GROUP BY and SELECT. But Laravel builds the query in the order methods are called. Calling orderBy before groupBy causes the alias 'total' to be unknown at that point, causing an SQL error.

🧠 Conceptual

advanced

How does Laravel handle ordering by a computed column in a grouped query?

In Laravel Eloquent, when you group records and select a computed column like count(*), how can you order the results by that computed column?

A You can orderBy the computed column name directly without selecting it first.

B Laravel automatically orders grouped queries by the first selected column.

C You must select the computed column with an alias and then orderBy using that alias.

D You cannot order grouped queries by computed columns in Laravel.

💡 Hint

Think about how SQL requires ordering by columns present in the SELECT clause.

To order by a computed column in a grouped query, you must select it with an alias and then orderBy that alias. Otherwise, SQL will not recognize the column in ORDER BY.

❓ state_output

expert

What is the count of groups returned by this Laravel query?

Given the users table has 100 records with 4 distinct 'department' values, what is the count of items in the result of this query?

$groups = User::groupBy('department')->get();
return $groups->count();

Laravel

use App\Models\User;

$groups = User::groupBy('department')->get();
return $groups->count();

A 100

B 4

C 1

D 0

💡 Hint

Remember that groupBy alone does not reduce the number of rows returned without aggregation.

In Laravel, calling groupBy without selecting aggregates returns all rows, not grouped rows. So the count is the total number of records, 100, not the number of groups.

Practice

1. What does the orderBy method do in Laravel's Eloquent?

easy

A. Sorts the query results by a specified column

B. Groups the query results by a specified column

C. Filters the query results by a condition

D. Joins two tables in the query

Solution

1. Step 1: Understand the purpose of orderBy

   The orderBy method is used to sort query results by a column in ascending or descending order.

2. Step 2: Differentiate from grouping and filtering

   Grouping collects rows with the same value, filtering limits rows by condition, and joining combines tables. These are different from sorting.

3. Final Answer:

   Sorts the query results by a specified column -> Option A

4. Quick Check:

   Ordering = Sorting [OK]

Hint: Remember: orderBy sorts, groupBy groups data [OK]

Common Mistakes:

• Confusing orderBy with groupBy
• Thinking orderBy filters data
• Mixing orderBy with join operations

2. Which of the following is the correct syntax to order users by their created_at date descending in Laravel?

easy

A. User::order('created_at', 'desc')->get();

B. User::orderBy('created_at', 'desc')->get();

C. User::groupBy('created_at')->desc()->get();

D. User::sortBy('created_at', 'desc')->get();

Solution

1. Step 1: Identify correct method for ordering

   Laravel uses orderBy with column and direction as parameters to sort results.

2. Step 2: Check method chaining and spelling

   orderBy is correct, desc is lowercase string, and get() fetches results. Other options use invalid methods.

3. Final Answer:

   User::orderBy('created_at', 'desc')->get(); -> Option B

4. Quick Check:

   Correct syntax = User::orderBy('created_at', 'desc')->get(); [OK]

Hint: Use orderBy('column', 'desc') to sort descending [OK]

Common Mistakes:

• Using groupBy instead of orderBy
• Using order() or sortBy() which don't exist in Eloquent
• Incorrect method chaining order

3. Given the code:

$results = DB::table('orders')
  ->select('status', DB::raw('count(*) as total'))
  ->groupBy('status')
  ->orderBy('total', 'desc')
  ->get();

What will $results contain?

medium

A. A list of orders sorted by status alphabetically

B. An error because you cannot order by an alias

C. A list of orders grouped by status without counts

D. A list of statuses with their order counts sorted descending by count

Solution

1. Step 1: Understand grouping and counting

   The query groups orders by status and counts how many orders each status has, naming count as total.

2. Step 2: Analyze ordering by count

   The results are ordered by total descending, so statuses with more orders appear first.

3. Final Answer:

   A list of statuses with their order counts sorted descending by count -> Option D

4. Quick Check:

   Group + count + orderBy total desc = A list of statuses with their order counts sorted descending by count [OK]

Hint: groupBy + count + orderBy alias works in Laravel [OK]

Common Mistakes:

• Thinking orderBy sorts by status alphabetically
• Missing that count(*) is aliased as total
• Assuming ordering by alias causes error

4. What is wrong with this Laravel query?

$users = User::groupBy('role')->orderBy('name')->get();

medium

A. Ordering by 'name' is invalid because 'name' is not in groupBy

B. Nothing is wrong; this query works fine

C. groupBy requires an aggregate function with orderBy

D. You cannot use orderBy after groupBy

Solution

1. Step 1: Understand SQL grouping rules

   When grouping by a column, all selected columns must be in groupBy or aggregated. Ordering by a column not in groupBy can cause errors.

2. Step 2: Check if 'name' is grouped or aggregated

   The query groups by 'role' but orders by 'name' which is not grouped or aggregated, causing SQL errors.

3. Final Answer:

   Ordering by 'name' is invalid because 'name' is not in groupBy -> Option A

4. Quick Check:

   Order column must be grouped or aggregated [OK]

Hint: Order columns must be in groupBy or aggregated [OK]

Common Mistakes:

• Assuming orderBy always works after groupBy
• Ignoring SQL strict mode errors
• Thinking groupBy requires aggregate with orderBy

5. You want to get the total sales amount per product category, but only for categories with more than 5 sales, ordered by total sales descending. Which Laravel query achieves this?

hard

A. DB::table('sales')->select('category', DB::raw('sum(amount) as total'))
->orderBy('total', 'desc')->groupBy('category')->having('total', '>', 5)
->get();

B. DB::table('sales')->select('category', DB::raw('sum(amount) as total'))
->groupBy('category')->where('total', '>', 5)
->orderBy('total', 'desc')->get();

C. DB::table('sales')->select('category', DB::raw('sum(amount) as total'))
->groupBy('category')->having('total', '>', 5)
->orderBy('total', 'desc')->get();

D. DB::table('sales')->select('category', DB::raw('sum(amount) as total'))
->groupBy('category')->orderBy('total', 'desc')->where('total', '>', 5)
->get();

Solution

1. Step 1: Use groupBy and sum aggregation

   Group sales by category and sum the amount as total.

2. Step 2: Filter groups with having clause

   Use having('total', '>', 5) to filter categories with more than 5 sales. where cannot filter aggregated results.

3. Step 3: Order results by total descending

   Use orderBy('total', 'desc') after grouping and filtering.

4. Final Answer:

   DB::table('sales')->select('category', DB::raw('sum(amount) as total')) ->groupBy('category')->having('total', '>', 5) ->orderBy('total', 'desc')->get(); -> Option C

5. Quick Check:

   Use having for aggregates, orderBy after groupBy [OK]

Hint: Use having() to filter grouped aggregates, not where() [OK]

Common Mistakes:

• Using where() instead of having() for aggregate filters
• Ordering before grouping
• Misplacing having() after where()