Capstone – Article 11: Expense Categories & Advanced Queries (Expense Tracker)

1. Introduction

Your Expense Tracker is secure and functional. Now we make it useful and powerful.

In this article, we will:

• Improve category handling
• Add filtering by category & date
• Add pagination & sorting
• Prepare the ground for reports

These are real-world backend requirements.

---

2. Category Handling Strategy

For now, we’ll keep categories simple and flexible:

• Stored as String (e.g., FOOD, TRAVEL, RENT)
• Validated at API layer
• Can be converted to enum later

This avoids premature complexity.

---

3. Improving ExpenseRequest Validation

Update ExpenseRequest:

@NotBlank(message = "Category is required")
private String category;

@NotNull(message = "Expense date is required")
private LocalDate expenseDate;

Optional:

• Enforce uppercase categories
• Validate against allowed list later

---

4. Adding Advanced Repository Queries

Update ExpenseRepository:

public interface ExpenseRepository extends JpaRepository<Expense, Long> {

    List<Expense> findByUserId(Long userId);

    List<Expense> findByUserIdAndCategory(Long userId, String category);

    List<Expense> findByUserIdAndExpenseDateBetween(
            Long userId, LocalDate start, LocalDate end);
}

Spring Data derives queries automatically.

---

5. Adding Pagination & Sorting

Use Pageable instead of List.

5.1 Repository Support

Page<Expense> findByUserId(Long userId, Pageable pageable);

---

5.2 Service Layer Pagination

public Page<ExpenseResponse> getMyExpenses(Pageable pageable) {

    String email = SecurityUtil.getCurrentUserEmail();
    User user = userRepository.findByEmail(email)
        .orElseThrow(() -> new UserNotFoundException("User not found"));

    return expenseRepository.findByUserId(user.getId(), pageable)
        .map(this::mapToResponse);
}

---

6. Filtering Expenses by Category

public List<ExpenseResponse> getExpensesByCategory(String category) {

    String email = SecurityUtil.getCurrentUserEmail();
    User user = userRepository.findByEmail(email)
        .orElseThrow(() -> new UserNotFoundException("User not found"));

    return expenseRepository
        .findByUserIdAndCategory(user.getId(), category)
        .stream()
        .map(this::mapToResponse)
        .toList();
}

---

7. Filtering Expenses by Date Range

public List<ExpenseResponse> getExpensesBetweenDates(
        LocalDate start, LocalDate end) {

    String email = SecurityUtil.getCurrentUserEmail();
    User user = userRepository.findByEmail(email)
        .orElseThrow(() -> new UserNotFoundException("User not found"));

    return expenseRepository
        .findByUserIdAndExpenseDateBetween(user.getId(), start, end)
        .stream()
        .map(this::mapToResponse)
        .toList();
}

---

8. Updating ExpenseController

@GetMapping("/filter/category")
public List<ExpenseResponse> getByCategory(@RequestParam String category) {
    return expenseService.getExpensesByCategory(category);
}

@GetMapping("/filter/date")
public List<ExpenseResponse> getByDateRange(
        @RequestParam LocalDate start,
        @RequestParam LocalDate end) {
    return expenseService.getExpensesBetweenDates(start, end);
}

@GetMapping
public Page<ExpenseResponse> getMyExpenses(
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "10") int size) {

    Pageable pageable = PageRequest.of(page, size, Sort.by("expenseDate").descending());
    return expenseService.getMyExpenses(pageable);
}

---

9. Testing Advanced Queries

Examples:

GET /expenses?page=0&size=5
GET /expenses/filter/category?category=FOOD
GET /expenses/filter/date?start=2026-01-01&end=2026-01-31

Verify:

• Only logged-in user data returned
• Pagination works
• Sorting works

---

10. Why This Matters in Interviews

This article demonstrates: ✔ Pagination knowledge
✔ Filtering & querying skills
✔ Practical API design
✔ Real business logic

Interviewers expect this level of thinking.

---

11. Common Mistakes

❌ Returning all data without pagination
❌ Filtering in controller
❌ Ignoring date handling
❌ Mixing business logic & security

---

12. Git Commit (Important)

git add .
git commit -m "Add expense filtering, pagination, and sorting"

---

13. What You Should Have Now

At this point:

• Expense APIs are flexible
• Data is paginated
• Filtering works by category & date
• Backend feels production-ready

---

14. What’s Next?

➡ Capstone – Article 12: Reporting & Aggregations

• Monthly expense summary
• Category-wise totals
• Dashboard-ready APIs

Type Next when you’re ready 🚀