[Technical Debt] Refactor Item Attribute Search to Fix Pagination, Sorting, and Multi-Attribute Search

Problem Summary

The current attribute search implementation uses GROUP_CONCAT to aggregate attribute values into concatenated strings for search. This approach has several critical issues:

1. Broken Pagination (#2819) - Row counts are incorrect when searching attributes, showing wrong pagination totals
2. No Multi-Attribute Search (#2407) - Searching multiple attributes returns no results
3. Sorting by Attribute Columns Impossible (#2722) - Cannot sort by attribute columns
4. GROUP_CONCAT Limits - Requires SET SESSION group_concat_max_len=49152 workarounds
5. Visibility vs Searchability (#2919) - Attributes can only be searched if "show in items" flag is set

Current Architecture Issues

Location: app/Models/Item.php lines 213-239

// Current problematic approach:
$builder->select('GROUP_CONCAT(DISTINCT CONCAT_WS(\'_\', definition_id, attribute_value) ORDER BY definition_id SEPARATOR \'|\') AS attribute_values');
$builder->havingLike('attribute_values', $search);

Problems:

• HAVING LIKE on concatenated string produces incorrect COUNT(DISTINCT items.item_id) results
• Multi-attribute queries need proper AND/OR logic, not string concatenation
• Sort columns for attributes need individual columns, not concatenated values
• group_concat_max_len truncates on large result sets

---

Phase 1: Core Search Refactor (Priority: Critical)

Scope: Refactor Item::search() to eliminate GROUP_CONCAT dependency

Files to Modify:

• app/Models/Item.php - Primary refactor
• app/Controllers/Items.php - Pass correct filter parameters

New Architecture

1. New Method: search_by_attributes()

/**
 * Get item_ids matching attribute search criteria
 * 
 * @param string $search Search term
 * @param array $definition_ids Attribute definition IDs to search
 * @param string $logic 'AND' or 'OR' for multi-attribute matching
 * @return array Array of matching item_ids
 */
public function search_by_attributes(string $search, array $definition_ids, string $logic = 'OR'): array
{
    $builder = $this->db->table('attribute_links');
    $builder->join('attribute_values', 'attribute_values.attribute_id = attribute_links.attribute_id');
    $builder->select('DISTINCT attribute_links.item_id');
    $builder->groupStart();
    $builder->like('attribute_value', $search);
    $builder->orWhere('attribute_decimal', $search);
    // Handle date format conversion for search
    $builder->groupEnd();
    $builder->whereIn('definition_id', $definition_ids);
    $builder->where('sale_id', null);
    $builder->where('receiving_id', null);
    
    return array_column($builder->get()->getResultArray(), 'item_id');
}

2. Refactored Item::search() Method

public function search(string $search, array $filters, ?int $rows = 0, ?int $limit_from = 0, ?string $sort = 'items.name', ?string $order = 'asc', ?bool $count_only = false)
{
    // ... existing setup code ...
    
    $matching_item_ids = null;
    
    // When attribute search is enabled, get matching item_ids first
    if ($attributes_enabled && $filters['search_custom'] && !empty($search)) {
        $matching_item_ids = $this->search_by_attributes($search, $filters['definition_ids']);
        
        // If no matches and we're only searching attributes, return early
        if (empty($matching_item_ids) && $filters['search_custom']) {
            return $count_only ? 0 : $this->db->table('items')->where('1', '0')->get();
        }
    }
    
    // Build main query
    $builder = $this->db->table('items AS items');
    // ... existing select/join code ...
    
    // Apply item_id filter if we have attribute matches
    if ($matching_item_ids !== null) {
        if (count($matching_item_ids) > 0) {
            $builder->whereIn('items.item_id', $matching_item_ids);
        }
    }
    
    // Apply standard search (name, item_number, etc.)
    if (!empty($search) && !$filters['search_custom']) {
        $builder->groupStart();
        $builder->like('name', $search);
        // ... existing search conditions ...
        $builder->groupEnd();
    }
    
    // Combined search: both regular fields AND attributes
    if (!empty($search) && $filters['search_custom'] && $matching_item_ids !== null) {
        $builder->groupStart();
        $builder->whereIn('items.item_id', $matching_item_ids);
        $builder->orWhereLike('name', $search);
        // ... other fields ...
        $builder->groupEnd();
    }
    
    // ... rest of existing logic ...
}

3. Attribute Column Sorting Support

/**
 * Apply sorting by attribute column
 * 
 * @param BaseBuilder $builder Query builder reference
 * @param string $sort Sort column name
 * @param string $order 'asc' or 'desc'
 * @param array $definition_ids Attribute definitions to consider for sorting
 * @return void
 */
private function apply_attribute_sort(&$builder, string $sort, string $order, array $definition_ids): void
{
    // Check if sort column is an attribute (format: attr_NNN where NNN is definition_id)
    if (preg_match('/^attr_(\d+)$/', $sort, $matches)) {
        $definition_id = (int)$matches[1];
        
        // Join attribute tables for sorting
        $alias = "sort_attr_{$definition_id}";
        $builder->join(
            "attribute_links AS {$alias}",
            "{$alias}.item_id = items.item_id AND {$alias}.definition_id = {$definition_id} AND {$alias}.sale_id IS NULL AND {$alias}.receiving_id IS NULL",
            'left'
        );
        $builder->join(
            "attribute_values AS {$alias}_val",
            "{$alias}_val.attribute_id = {$alias}.attribute_id",
            'left'
        );
        $builder->orderBy("{$alias}_val.attribute_value", $order);
    }
}

---

Phase 2: Enable All Attributes in Search (Priority: High)

Scope: Separate searchability from visibility in Items table

Files to Modify:

• app/Models/Attribute.php - Add SHOW_IN_SEARCH constant
• app/Controllers/Items.php - Use all searchable attributes for filter
• app/Database/Migrations/ - New migration for definition_flags column

New Constant:

public const SHOW_IN_SEARCH = 8;  // New flag value

Controller Update:

// Current: Only shows attributes with SHOW_IN_ITEMS flag
$definition_names = $this->attribute->get_definitions_by_flags(Attribute::SHOW_IN_ITEMS);

// New: Include all searchable attributes  
$definition_names = $this->attribute->get_definitions_by_flags(Attribute::SHOW_IN_ITEMS | Attribute::SHOW_IN_SEARCH);

---

Phase 3: Multi-Attribute Search UI (Priority: Medium)

Scope: Add syntax for searching multiple attributes

Search Syntax Options:

1. color: blue size: large - Attribute-specific search
2. color: blue AND size: large - Explicit AND logic
3. color: blue OR size: large - Explicit OR logic

Files to Modify:

• public/js/items.js - Parse search syntax
• app/Models/Item.php - Handle parsed attribute queries

---

Database Schema Changes

Migration Required: None for Phase 1 (uses existing schema)

Future Enhancement (Phase 2):

ALTER TABLE ospos_attribute_definitions 
MODIFY COLUMN definition_flags INT;
-- Add SHOW_IN_SEARCH = 8 bitmask value

---

Testing Checklist

• Single attribute search returns correct results
• Multi-attribute search (AND logic) returns correct results
• Multi-attribute search (OR logic) returns correct results
• Pagination count matches actual result rows
• Sorting by name/category still works
• Sorting by attribute column works (after Phase 3)
• Search with no results shows "No items found"
• Performance acceptable with 1000+ items
• No group_concat_max_len warnings in logs

---

Related Issues

• Fixes #2819
• Fixes #2407
• Fixes #2722
• Related #2919
• Related #2600
• Related #2890
• Related #3293
• Related #2564
• Related #2663
• Related #2470
• Related #2847
• Related #2244
• Related #2817

---

Estimated Effort

Phase	Effort	Priority
Phase 1: Core Refactor	3-5 days	Critical
Phase 2: Searchable Attributes	1 day	High
Phase 3: Multi-Attribute UI	2 days	Medium
Phase 4: Sort by Attribute	2 days	Medium