Ordering and grouping in Laravel - Mini Project: Build & Apply

Ordering and grouping with Laravel Collections

📖 Scenario: You are building a simple Laravel application to manage a list of books in a library. Each book has a title, author, and year of publication.You want to display the books ordered by their publication year and grouped by author.

🎯 Goal: Use Laravel collection methods to order books by year ascending and group them by author.

📋 What You'll Learn

Create a books array where each book has title, author, and year.

Order the books by year ascending using sortBy.

Group the books by author using Laravel collection methods.

Return the grouped and ordered books.

💡 Why This Matters

🌍 Real World

Ordering and grouping data is common in applications like libraries, stores, or any list where you want to organize items by categories and sort them.

💼 Career

Understanding Laravel collections and how to order and group data is essential for backend developers working with Laravel to build efficient and readable data queries.

1

Create the books data array

Create a variable called books that contains an array of associative arrays. Each associative array should have keys 'title', 'author', and 'year' with these exact entries:
{'title' => 'The Hobbit', 'author' => 'J.R.R. Tolkien', 'year' => 1937},
{'title' => '1984', 'author' => 'George Orwell', 'year' => 1949},
{'title' => 'Animal Farm', 'author' => 'George Orwell', 'year' => 1945},
{'title' => 'The Fellowship of the Ring', 'author' => 'J.R.R. Tolkien', 'year' => 1954}.

Laravel

# Create the books array with the given entries
# Your code here

Hint

Use a PHP array with associative arrays inside. Each book is an associative array with keys 'title', 'author', and 'year'.

2

Convert the array to a Laravel collection

Create a variable called booksCollection and set it to a Laravel collection created from the $books array using collect().

Laravel

$books = [
    ['title' => 'The Hobbit', 'author' => 'J.R.R. Tolkien', 'year' => 1937],
    ['title' => '1984', 'author' => 'George Orwell', 'year' => 1949],
    ['title' => 'Animal Farm', 'author' => 'George Orwell', 'year' => 1945],
    ['title' => 'The Fellowship of the Ring', 'author' => 'J.R.R. Tolkien', 'year' => 1954],
];

// Convert the array to a Laravel collection
# Your code here

Hint

Use the collect() helper function to create a collection from the array.

3

Order the books by year ascending

Use the sortBy method on $booksCollection to order the books by the 'year' key in ascending order. Save the result back to $booksCollection.

Laravel

$books = [
    ['title' => 'The Hobbit', 'author' => 'J.R.R. Tolkien', 'year' => 1937],
    ['title' => '1984', 'author' => 'George Orwell', 'year' => 1949],
    ['title' => 'Animal Farm', 'author' => 'George Orwell', 'year' => 1945],
    ['title' => 'The Fellowship of the Ring', 'author' => 'J.R.R. Tolkien', 'year' => 1954],
];

$booksCollection = collect($books);

// Order the books by year ascending
# Your code here

Hint

Use sortBy('year') on the collection to order by the year key.

4

Group the books by author

Use the groupBy method on $booksCollection to group the books by the 'author' key. Save the result back to $booksCollection.

Laravel

$books = [
    ['title' => 'The Hobbit', 'author' => 'J.R.R. Tolkien', 'year' => 1937],
    ['title' => '1984', 'author' => 'George Orwell', 'year' => 1949],
    ['title' => 'Animal Farm', 'author' => 'George Orwell', 'year' => 1945],
    ['title' => 'The Fellowship of the Ring', 'author' => 'J.R.R. Tolkien', 'year' => 1954],
];

$booksCollection = collect($books);

$booksCollection = $booksCollection->sortBy('year');

// Group the books by author
# Your code here

Hint

Use groupBy('author') on the collection to group books by author.

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