Ordering and grouping in Laravel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to order users by their name in ascending order.

Laravel

$users = User::orderBy('[1]')->get();

Drag options to blanks, or click blank then click option'

Acreated_at

Bemail

Cid

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column that does not exist.

Forgetting to put the column name in quotes.

Ordering by 'name' sorts users alphabetically by their name.

2fill in blank

medium

Complete the code to group orders by their status.

Laravel

$orders = Order::select('status', DB::raw('count(*) as total'))
    ->groupBy('[1]')
    ->get();

Drag options to blanks, or click blank then click option'

Astatus

Buser_id

Ccreated_at

Dprice

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by a column not selected.

Using a wrong column name.

Grouping by 'status' groups orders by their status value.

3fill in blank

hard

Fix the error in ordering products by price descending.

Laravel

$products = Product::orderBy('price', '[1]')->get();

Drag options to blanks, or click blank then click option'

Aascending

Basc

Cdesc

Ddescending

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'descending' instead of 'desc'.

Using 'asc' for descending order.

Use 'desc' to order by descending price in Laravel.

4fill in blank

hard

Fill both blanks to order posts by created date descending and group by user_id.

Laravel

$posts = Post::orderBy('[1]', '[2]')
    ->groupBy('user_id')
    ->get();

Drag options to blanks, or click blank then click option'

Acreated_at

Bdesc

Casc

Dupdated_at

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping order direction.

Using wrong column names.

Order posts by 'created_at' descending, then group by 'user_id'.

5fill in blank

hard

Fill all three blanks to select category, count products, group by category, and order by count descending.

Laravel

$result = Product::select('[1]', DB::raw('count(*) as [2]'))
    ->groupBy('[3]')
    ->orderBy('[2]', 'desc')
    ->get();

Drag options to blanks, or click blank then click option'

Acategory

Btotal

Ccategory_id

Dprice

Attempts:

3 left

💡 Hint

Common Mistakes

Using different columns for select and groupBy.

Forgetting to alias the count.

Ordering by a column not selected.

Select 'category', count products as 'total', group by 'category' and order by 'total' descending.

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