Feature Implementation Plan: Fix Incomplete Filtered Collections

📋 Todo Checklist

• [ ] Refactor the CoffeeBeanRepository's findByRequest method to use a two-step query process.
• [ ] Ensure the new implementation works correctly with pagination.
• [ ] Verify that the fix does not negatively impact performance.
• [ ] Write an integration test that specifically reproduces and then confirms the fix for this bug.
• [ ] Final Review and Testing

🔍 Analysis & Investigation

Codebase Structure

• Affected Repository: The root cause is in src/Repository/CoffeeBeanRepository.php, specifically in the findByRequest method (or its private helper methods).
• Affected Endpoint: The primary endpoint affected is /api/coffee-beans, but this bug could potentially affect any endpoint that filters on a to-many relationship. The fix applied here should serve as a blueprint for other repositories if they exhibit the same behavior.

Root Cause Analysis

The current implementation uses a single Doctrine query with JOINs to filter the CoffeeBean entities. When a filter is applied to a to-many relationship (like processingMethods), Doctrine's hydration process only loads the parts of the collection that matched the WHERE clause. This results in an incomplete object being returned to the client, where a coffee bean that has two processing methods is shown with only the one that was used in the filter. This is incorrect behavior.

Solution: Two-Step Query Process

The plan is to refactor the repository method to use a two-step query process, which is the standard Doctrine best practice for this scenario: 1. Query for IDs: First, execute a query with all the complex joins and filters, but select only the DISTINCT IDs of the root entity (CoffeeBean). This query will also handle pagination (setMaxResults, setFirstResult). 2. Query for Full Entities: Second, execute a clean query that fetches the full CoffeeBean objects (with all their collections) using a WHERE id IN (:ids) clause, passing the IDs from the first query.

This approach ensures that the filtering logic is cleanly separated from the data-loading logic, guaranteeing that the final hydrated entities are complete and accurate.

📝 Implementation Plan

Prerequisites

• No new external dependencies are required.

Step-by-Step Implementation

1. Refactor CoffeeBeanRepository

   • Files to modify: src/Repository/CoffeeBeanRepository.php
   • Changes needed:
     • Locate the executeCoffeeBeanQuery (or similar private method called by findByRequest).
     • Step 1: Create the ID Query:
       • Start with the existing QueryBuilder instance.
       • Change the select statement to select('DISTINCT cb.id').
       • Keep all the existing leftJoin, andWhere, setParameter, setMaxResults, and setFirstResult calls.
       • Execute this query and fetch the IDs into an array: $ids = $idQuery->getQuery()->getSingleColumnResult();.
     • Step 2: Create the Data Query:
       • Create a new QueryBuilder instance.
       • This query will be much simpler. It will select the full CoffeeBean object and JOIN all the necessary collections (processingMethods, roastLevels, etc.) without any filtering WHERE clauses on those joins.
       • Add a single WHERE clause: $qb->where('cb.id IN (:ids)')->setParameter('ids', $ids).
       • Add the orderBy clause here to ensure the final result is sorted correctly.
       • Execute this query to get the final, complete list of CoffeeBean entities.
     • Pagination: The Paginator object should be constructed from the second query (the data query), but the totalItems count should be derived from a separate COUNT query that mirrors the filtering logic of the first query (the ID query).

2. Update DTO Mapping and Caching

   • The DTO mapping and caching logic in findByRequest should remain largely the same, as it will now receive a list of complete entities from the refactored query method. No significant changes should be needed here, but it's important to verify.

Testing Strategy

• Integration Tests:
  • Create a new, specific integration test that reproduces the exact bug.
  • Ensure a CoffeeBean exists in the test database with at least two ProcessingMethods.
  • Make an API call to GET /api/coffee-beans and filter by one of those processing methods (?processingMethodIds[]=...).
  • Assert that the response contains the target coffee bean.
  • Crucially, assert that the processingMethods array in the JSON response for that bean has a count of 2 (or its total number of methods), not 1.
  • This test should fail before the fix and pass after the fix.
  • Run the entire existing test suite for the /api/coffee-beans endpoint to ensure that this refactoring has not introduced any regressions to other filtering or pagination behaviors.

🎯 Success Criteria

• When filtering the /api/coffee-beans endpoint by a to-many relationship (like processingMethodIds), the returned CoffeeBean objects are complete and contain all of their associated items, not just the ones that matched the filter.
• The bug is verifiably fixed by a dedicated integration test.
• All existing filtering, pagination, and sorting functionalities continue to work as expected.
• The API now returns consistent and accurate data regardless of the filter combination.