Fix Roaster Sorting in RoasterCrawlConfigCrudController Index

Priority: 🟡 MEDIUM - UX Improvement Status: Planning Type: Bug Fix / Enhancement Related Files: - src/Controller/Admin/RoasterCrawlConfigCrudController.php - src/Entity/RoasterCrawlConfig.php - src/Entity/Roaster.php

Problem Statement

The roaster column in RoasterCrawlConfigCrudController INDEX view currently sorts by roaster ID (numeric) instead of roaster name (alphabetical). This makes it difficult for administrators to find crawl configs by roaster name, as the sort order is unintuitive.

User Story

As an administrator I want to sort crawl configs by roaster name alphabetically So that I can quickly find configurations for a specific roaster

Current Behavior

When clicking the "Roaster" column header: - Sorts by roaster.id (default AssociationField behavior) - Order: 1, 2, 3, 10, 11, 100, etc. - Not useful for finding roasters by name

Example current sort:

ID  | Roaster Name
----|-------------
1   | Zebra Coffee
2   | Alpha Roasters
3   | Beta Beans
10  | Charlie's Coffee

Expected sort:

Name           | Roaster
---------------|-------------
Alpha Roasters | 2
Beta Beans     | 3
Charlie's Coffee | 10
Zebra Coffee   | 1

Root Cause

File: src/Controller/Admin/RoasterCrawlConfigCrudController.php (Lines 69-75)

yield AssociationField::new('roaster')
    ->setRequired(true)
    ->setFormTypeOptions([
        'placeholder' => 'Choose a roaster...',
        'empty_data'  => '',
    ]);

Issue: No setSortProperty() configured, so EasyAdmin defaults to sorting by the related entity's ID.

Proposed Solution

Configure the AssociationField to sort by roaster name using setSortProperty().

Implementation Approach

Recommended by Architect: Use setSortProperty('roaster.name')

yield AssociationField::new('roaster')
    ->setRequired(true)
    ->setSortable(true)  // Explicitly enable sorting
    ->setSortProperty('roaster.name')  // Sort by name, not ID
    ->setFormTypeOptions([
        'placeholder' => 'Choose a roaster...',
        'empty_data'  => '',
    ]);

How It Works

EasyAdmin DQL Path Resolution: 1. User clicks "Roaster" column header 2. EasyAdmin generates SQL: ORDER BY roaster.name ASC/DESC 3. Database performs JOIN and sorts alphabetically 4. Results returned in alphabetical order

Generated SQL (approximate):

SELECT rcc.*, r.name as roaster_name
FROM roaster_crawl_config rcc
LEFT JOIN roaster r ON rcc.roaster_id = r.id
ORDER BY r.name ASC
LIMIT 25;

Alternative Approaches Considered

❌ Option 1: Custom Query Builder

public function createIndexQueryBuilder(/*...*/)
{
    return parent::createIndexQueryBuilder(/*...*/)
        ->leftJoin('entity.roaster', 'r')
        ->addOrderBy('r.name', 'ASC');
}

Why rejected: - Hardcodes sort order - user can't toggle ASC/DESC - Prevents sorting by other columns - Overrides default sort behavior - setSortProperty() is the idiomatic solution

❌ Option 2: Virtual Field with Roaster Name

yield TextField::new('roasterName')
    ->setSortable(true)
    ->formatValue(fn($value, RoasterCrawlConfig $entity) => $entity->getRoaster()->getName());

Why rejected: - Duplicates data (roaster already displayed) - Confusing to have two roaster columns - Doesn't fix the original column - More complex than needed

❌ Option 3: Override Default Sort in configureCrud()

public function configureCrud(Crud $crud): Crud
{
    return $crud
        ->setDefaultSort(['roaster.name' => 'ASC']);
}

Why rejected: - Only sets default sort on page load - Doesn't fix the column header sorting behavior - User clicking column still sorts by ID - Incomplete solution

Implementation Plan

Step 1: Update AssociationField Configuration

File: src/Controller/Admin/RoasterCrawlConfigCrudController.php Location: Lines 69-75

Current code:

yield AssociationField::new('roaster')
    ->setRequired(true)
    // Use select instead of autocomplete, with empty_data to start empty
    ->setFormTypeOptions([
        'placeholder' => 'Choose a roaster...',
        'empty_data'  => '',
    ]);

Updated code:

yield AssociationField::new('roaster')
    ->setRequired(true)
    ->setSortable(true)  // Explicitly enable sorting on this column
    ->setSortProperty('roaster.name')  // Sort by roaster's name property instead of ID
    // Use select instead of autocomplete, with empty_data to start empty
    ->setFormTypeOptions([
        'placeholder' => 'Choose a roaster...',
        'empty_data'  => '',
    ]);

Step 2: Verify Database Index (Performance)

File: src/Entity/Roaster.php

Check if name column has an index:

#[ORM\Entity(repositoryClass: RoasterRepository::class)]
#[ORM\HasLifecycleCallbacks]
#[ORM\Index(name: 'idx_roaster_name', columns: ['name'])]  // ← Check if this exists
class Roaster
{
    #[ORM\Column(length: 255)]
    private ?string $name = null;
    // ...
}

If index doesn't exist, add it for better sort performance:

#[ORM\Index(name: 'idx_roaster_name', columns: ['name'])]

Then create migration:

make migration
make migrate

Step 3: Test Sorting Behavior

1. Navigate to RoasterCrawlConfig INDEX
2. Click "Roaster" column header
3. Verify alphabetical ASC sort
4. Click again - verify alphabetical DESC sort
5. Sort by other columns - verify no regression

Performance Considerations

Database Index Analysis

Current indexes on Roaster: - Primary key on id (automatic) - Unique index on slug (if exists) - Need to verify: Index on name

Impact of sorting without index:

-- Without index on name
EXPLAIN SELECT * FROM roaster_crawl_config rcc
LEFT JOIN roaster r ON rcc.roaster_id = r.id
ORDER BY r.name ASC;

-- Result: "Using filesort" (slow for large tables)

Impact with index:

-- With index on name
EXPLAIN SELECT * FROM roaster_crawl_config rcc
LEFT JOIN roaster r ON rcc.roaster_id = r.id
ORDER BY r.name ASC;

-- Result: "Using index" (fast, sorted at retrieval)

Performance Metrics

Expected performance: - <100 roasters: Index optional, negligible difference - 100-1000 roasters: Index recommended, ~10x faster sorts - 1000+ roasters: Index critical, ~100x faster sorts

Current production scale: - Estimated roasters: ~50-100 (based on typical coffee roaster directories) - Conclusion: Index is nice-to-have, not critical

Query Overhead

Additional cost: - JOIN operation: Already performed by EasyAdmin for display - No additional queries - sorting happens in existing query - Marginal overhead: ~1-5ms on indexed column

Testing Strategy

Manual Testing

• [ ] Click "Roaster" column header - verify ASC sort
• [ ] Click again - verify DESC sort
• [ ] Verify roaster names in alphabetical order
• [ ] Test with special characters (é, ñ, ü, etc.)
• [ ] Test with case sensitivity (ACME vs acme)
• [ ] Verify pagination maintains sort order
• [ ] Test search/filter with sorting

Performance Testing

• [ ] Use Symfony profiler to check query count
• [ ] Verify JOIN is efficient
• [ ] Check query execution time (<100ms)
• [ ] Test with 100+ crawl configs

Database Testing

-- Test query manually
SELECT rcc.name, r.name as roaster_name
FROM roaster_crawl_config rcc
LEFT JOIN roaster r ON rcc.roaster_id = r.id
ORDER BY r.name ASC
LIMIT 25;

-- Verify alphabetical order
-- Check EXPLAIN plan
EXPLAIN SELECT rcc.name, r.name as roaster_name
FROM roaster_crawl_config rcc
LEFT JOIN roaster r ON rcc.roaster_id = r.id
ORDER BY r.name ASC;

Regression Testing

• [ ] Verify other columns still sortable
• [ ] Verify default sort (name ASC) still works
• [ ] Verify search functionality unaffected
• [ ] Verify filters work correctly
• [ ] Test form submission (roaster field)
• [ ] Verify detail/edit pages work

Edge Cases to Consider

1. Roasters with Same Name

Scenario: Two roasters named "Blue Bottle Coffee"

Behavior: - Both appear in alphabetical order - Secondary sort by ID (automatic) - No duplicate issues

Example:

Blue Bottle Coffee (ID: 5)
Blue Bottle Coffee (ID: 12)
Counter Culture (ID: 3)

2. Null Roaster (Edge Case)

Scenario: RoasterCrawlConfig with NULL roaster (shouldn't happen due to nullable: false)

Behavior: - NULL values sort first (PostgreSQL default) - Non-null roasters follow alphabetically

3. Special Characters and Collation

Scenario: Roasters with accents: "Café Grumpy", "Blue Bottle"

Behavior depends on database collation: - utf8_general_ci: Case-insensitive, accent-insensitive - utf8_unicode_ci: Case-insensitive, accent-aware - PostgreSQL: Uses locale-based collation (typically accent-aware)

Example sort (locale-dependent):

Alpha Coffee
Blue Bottle
Café Grumpy  (or before/after Blue depending on locale)
Dapper & Wise

4. Very Long Roaster Names

Scenario: Name exceeds display width

Behavior: - EasyAdmin truncates display - Sort uses full name (correct behavior) - No issues expected

Success Criteria

• [ ] Roaster column sorts alphabetically (A-Z, Z-A)
• [ ] Clicking column header toggles ASC/DESC
• [ ] Sort order is case-insensitive
• [ ] Special characters handled correctly
• [ ] No performance degradation
• [ ] No additional database queries
• [ ] Regression tests pass
• [ ] Symfony profiler shows efficient queries

Deployment Plan

Pre-Deployment

1. Implement code changes
2. Test locally with various roaster names
3. Check Symfony profiler for query efficiency
4. Verify index on roaster.name (add if needed)
5. Create migration if index added

Deployment Steps

1. Create branch: fix/roaster-crawl-config-alphabetical-sort
2. Commit: fix: Sort roaster column alphabetically in RoasterCrawlConfig index
3. If index added, commit migration: feat: Add index on Roaster.name for efficient sorting
4. Create PR
5. Code review
6. Merge to main
7. Deploy to staging
8. Test sorting in staging admin
9. Run migration if needed: make migrate
10. Deploy to production
11. Run migration if needed

Post-Deployment

1. Verify sorting works in production
2. Check Symfony profiler for performance
3. Monitor database query times
4. Gather user feedback

Risk Assessment

Risk: VERY LOW 🟢

Why: - Single-line change (add setSortProperty()) - Uses built-in EasyAdmin functionality - No schema changes (unless adding index) - Easy to rollback - No business logic affected

Potential Issues

1. Collation differences - Sort order may vary by locale
2. Mitigation: Test with production data
3. Performance on large datasets - Without index, slow sorts
4. Mitigation: Add index on roaster.name
5. Case sensitivity - Unexpected sort order
6. Mitigation: Database collation handles this

Database Migration (If Needed)

Check Current Indexes

php bin/console doctrine:schema:validate

Create Index Migration

php bin/console make:migration

Expected migration content:

public function up(Schema $schema): void
{
    $this->addSql('CREATE INDEX idx_roaster_name ON roaster (name)');
}

public function down(Schema $schema): void
{
    $this->addSql('DROP INDEX idx_roaster_name');
}

Apply Migration

php bin/console doctrine:migrations:migrate

Estimated Effort

Without Index: - Implementation: 5 minutes - Testing: 15 minutes - Deployment: 10 minutes - Total: ~30 minutes

With Index: - Implementation: 5 minutes - Migration Creation: 10 minutes - Testing: 20 minutes - Deployment: 15 minutes - Total: ~50 minutes

Related Issues

Similar Fixes Needed?

Audit other CRUD controllers for similar sorting issues:

# Search for AssociationField without setSortProperty
grep -r "AssociationField::new" src/Controller/Admin/

Candidates for similar fixes: - CoffeeBeanCrudController (if sorting by roaster) - CrawlUrlCrudController (if sorting by roaster or bean) - Any controller with AssociationField displayed on INDEX

Documentation Updates

Update Admin Panel Documentation (if exists)

• Document sorting behavior
• Note roaster column sorts alphabetically
• Include screenshot of sorted view

References

• EasyAdmin AssociationField: https://symfony.com/bundles/EasyAdminBundle/current/fields/AssociationField.html
• setSortProperty Documentation: https://symfony.com/bundles/EasyAdminBundle/current/fields.html#sorting-data
• Doctrine Indexes: https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/annotations-reference.html#index
• PostgreSQL Collation: https://www.postgresql.org/docs/current/collation.html

Assignee

TBD

Status Updates

• 2025-11-09: Plan created based on architect recommendations
• Next: Implement sorting fix and verify index