Feature Implementation Plan: Add Search to Top Endpoints¶

📋 Todo Checklist¶

[ ] Update the VarietyRepository's findTopByAvailableBeanCount method to accept a search term.
[ ] Update the /api/varieties/top endpoint to accept and pass on the search parameter.
[ ] Update the RegionRepository's findTopByAvailableBeanCount method to accept a search term.
[ ] Update the /api/locations/regions/top endpoint to accept and pass on the search parameter.
[ ] Update OpenAPI documentation for both endpoints to include the new search parameter.
[ ] Write unit and integration tests for the new search functionality.
[ ] Final Review and Testing

🔍 Analysis & Investigation¶

Codebase Structure¶

Varieties: The logic will be updated in src/Controller/Api/VarietyController.php and src/Repository/VarietyRepository.php.
Regions: The logic will be updated in src/Controller/Api/LocationController.php and src/Repository/RegionRepository.php.

Current Architecture¶

The plan enhances the existing "top" endpoint architecture. The core principle of using a dedicated endpoint for aggregated data remains. The change involves adding a conditional WHERE clause to the aggregation queries before the GROUP BY and COUNT operations. This is the correct and most efficient way to implement this feature, as it filters the dataset early, ensuring that the aggregation is performed only on the relevant (searched-for) items that have available beans.

Dependencies & Integration Points¶

NelmioApiDocBundle: The OpenAPI documentation for both /api/varieties/top and /api/locations/regions/top will be updated to reflect the new optional search parameter.
Doctrine: The Doctrine Query Builder will be used to conditionally add the AND WHERE name LIKE :search clause to the existing aggregation queries.

Considerations & Challenges¶

Query Logic: It is critical to add the search condition to the WHERE clause, not a HAVING clause. A WHERE clause filters rows before aggregation, which is what we need. A HAVING clause filters groups after aggregation and would be less efficient and semantically incorrect here.
Performance: Adding a LIKE clause can impact query performance. However, since the query is already joining on and filtering by coffee_bean, the dataset is constrained. Ensuring the name columns on the variety and region tables are indexed is a good practice to mitigate any potential slowdown.

📝 Implementation Plan¶

Prerequisites¶

No new external dependencies are required.

Step-by-Step Implementation¶

Update Top Varieties Endpoint
- Files to modify: src/Controller/Api/VarietyController.php, src/Repository/VarietyRepository.php
- VarietyController.php Changes:
  - In getTopVarieties, get the optional search parameter from the Request.
  - Pass the search term to the findTopByAvailableBeanCount repository method.
  - Update the #[OA\Parameter] annotations to document the new optional search parameter.
- VarietyRepository.php Changes:
  - Modify the findTopByAvailableBeanCount method signature to accept an optional ?string $search = null.
  - Inside the method, add a conditional block to the query builder:
```
// ... after the join('v.coffeeBeans', 'cb') ...
if ($search) {
    $qb->andWhere('LOWER(v.name) LIKE LOWER(:search)')
       ->setParameter('search', '%' . $search . '%');
}
// ... before the groupBy('v.id') ...
```
Update Top Regions Endpoint
- Files to modify: src/Controller/Api/LocationController.php, src/Repository/RegionRepository.php
- LocationController.php Changes:
  - In getTopRegions, get the optional search parameter from the Request.
  - Pass the search term to the findTopByAvailableBeanCount repository method.
  - Update the #[OA\Parameter] annotations to document the new optional search parameter.
- RegionRepository.php Changes:
  - Modify the findTopByAvailableBeanCount method signature to accept an optional ?string $search = null.
  - Inside the method, add a conditional block to the query builder:
```
// ... after the join('r.coffeeBeans', 'cb') ...
if ($search) {
    $qb->andWhere('LOWER(r.name) LIKE LOWER(:search)')
       ->setParameter('search', '%' . $search . '%');
}
// ... before the groupBy('r.id') ...
```

Testing Strategy¶

Unit Tests:
- Update the unit tests for both VarietyRepository::findTopByAvailableBeanCount and RegionRepository::findTopByAvailableBeanCount.
- Test each method twice: once without the search term, and once with it, asserting that the correct WHERE clause is added to the query.
Integration Tests:
- Update the integration tests for both /api/varieties/top and /api/locations/regions/top.
- For each endpoint, add a new test that passes a search parameter.
- Assert that the results are filtered by the search term.
- Assert that the results are still correctly sorted by beanCount in descending order.
- Assert that a search for a term that matches a variety/region with no available beans returns an empty array.

🎯 Success Criteria¶

The /api/varieties/top endpoint can now be filtered by an optional search parameter on the variety name.
The /api/locations/regions/top endpoint can now be filtered by an optional search parameter on the region name.
In both cases, the search only considers items with at least one available coffee bean.
The results for both endpoints remain correctly sorted by the count of available beans.
The OpenAPI documentation for both endpoints is updated to include the new search parameter.
All updated functionality is covered by tests.