Ordering and grouping in Laravel - Step-by-Step Execution

Concept Flow - Ordering and grouping

Start Query Builder

↓

Apply GroupBy

↓

Apply OrderBy

↓

Execute Query

↓

Get Results

This flow shows how Laravel's query builder applies grouping first, then ordering, before running the query to get results.

Execution Sample

Laravel

DB::table('users')
  ->select('role', DB::raw('count(*) as total'))
  ->groupBy('role')
  ->orderBy('total', 'desc')
  ->get();

This code groups users by their role, counts them, and orders the groups by count descending.

Execution Table

Step	Action	Query Part	Effect	Result Preview
1	Start Query Builder	FROM users	Prepare base table	No data fetched yet
2	Select columns	SELECT role, count(*) as total	Prepare columns and count	No data fetched yet
3	Apply groupBy	GROUP BY role	Groups rows by role	Rows grouped by role
4	Apply orderBy	ORDER BY total DESC	Sort groups by count descending	Groups ordered by total desc
5	Execute query	Run SQL	Fetch grouped and ordered data	Returns grouped roles with counts sorted
6	Get results	Collect data	Results ready for use	[{"role": "admin", "total": 5}, {"role": "user", "total": 3}]

💡 Query executed and results fetched with grouping and ordering applied

Variable Tracker

Variable	Start	After groupBy	After orderBy	Final
query	DB::table('users')	Grouped by role	Ordered by total desc	Executed and fetched results
results	null	null	null	[{"role": "admin", "total": 5}, {"role": "user", "total": 3}]

Key Moments - 2 Insights

Why do we apply groupBy before orderBy in Laravel queries?

Because grouping creates summary rows, and ordering sorts these grouped results. The execution_table rows 3 and 4 show groupBy applied first, then orderBy.

What does orderBy('total', 'desc') do after grouping?

It sorts the grouped results by the count named 'total' in descending order, as shown in execution_table row 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the query part applied at step 3?

A GROUP BY role

B ORDER BY total DESC

C SELECT role, count(*) as total

D FROM users

💡 Hint

Check the 'Query Part' column in execution_table row 3

At which step does the query actually run and fetch data?

A Step 2

B Step 5

C Step 4

D Step 6

💡 Hint

Look for 'Execute query' action in execution_table

If we remove groupBy, what will happen to the orderBy in this query?

A It will order by role instead

B It will cause a syntax error

C It will order individual rows, not grouped counts

D It will have no effect

💡 Hint

Think about how orderBy works without grouping, referencing variable_tracker and execution_table

Concept Snapshot

Laravel Ordering and Grouping:
- Use groupBy('column') to group rows.
- Use select with DB::raw('count(*) as alias') for counts.
- Use orderBy('alias', 'desc') to sort grouped results.
- groupBy runs before orderBy.
- Call get() to execute and fetch results.

Full Transcript

This lesson shows how Laravel's query builder handles ordering and grouping. First, the query builder starts with the base table. Then, it selects columns including a count with an alias. Next, groupBy groups rows by a column, creating summary rows. After grouping, orderBy sorts these groups by the count in descending order. Finally, the query runs and fetches the grouped and ordered results. Variables track the query state from start to execution. Key points include applying groupBy before orderBy and understanding that orderBy sorts grouped results. The visual quiz checks understanding of query parts and execution steps.

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()