Comprehensive Faceting System

This document describes the new Elasticsearch-inspired faceting system implemented for the OpenRegister application. The system provides powerful, flexible faceting capabilities that support both metadata and object field facets with enumerated values and range buckets.

Overview

The faceting system provides a modern, user-friendly approach to building faceted search interfaces. It supports:

Disjunctive faceting - Facet options don't disappear when selected
Multiple facet types - Terms, date histograms, and numeric ranges
Metadata and object field facets - Both table columns and JSON data
Facetable field discovery - Automatic detection of available faceting options
Elasticsearch-style API - Familiar structure for developers
Performance optimization - Efficient database queries with proper indexing

Key Features

1. Disjunctive Faceting

Each facet shows counts as if its own filter were not applied. This prevents facet options from disappearing when selected, providing a better user experience.

Terms aggregation - For categorical data (status, priority, etc.)
Date histogram - For time-based data with configurable intervals
Range aggregation - For numeric data with custom buckets

3. Dual Data Sources

Metadata facets - Based on ObjectEntity table columns (@self)
Object field facets - Based on JSON object data

4. Enhanced Labels with Caching

Automatic resolution of register, schema, organisation IDs, and object UUIDs to human-readable names using an optimized caching mechanism. The system intelligently detects UUIDs in any facet and resolves them to object names (naam, name, title, etc.) using batch loading and multi-tier caching. Facet buckets are automatically sorted alphabetically by label for consistent, user-friendly display.

UUID Resolution Technical Implementation

The faceting system includes intelligent UUID-to-name resolution that works automatically:

Resolution Process:

UUID Detection - Identifies bucket values containing hyphens (UUID format)
Lazy Service Loading - ObjectCacheService loaded from container only when needed
Batch Resolution - All UUIDs in facets resolved in a single database query
Multi-Tier Caching - Checks in-memory cache → distributed cache → database
Name Extraction - Searches common name fields (naam, name, title, contractNummer, achternaam)
Alphabetical Sorting - Facets sorted by resolved labels (case-insensitive A-Z)
Graceful Fallback - Uses UUID if name cannot be resolved

Example Transformation:

Before UUID resolution:

{
  'value': '01c26b42-e047-4322-95ba-46d53a1696c0',
  'count': 2,
  'label': '01c26b42-e047-4322-95ba-46d53a1696c0'
}

After UUID resolution:

{
  'value': '01c26b42-e047-4322-95ba-46d53a1696c0',
  'count': 2,
  'label': 'Component Name Here'
}

Performance Characteristics:

Batch queries: All UUIDs resolved in one DB query (no N+1 problem)
Cached: <10ms for cached names
Uncached: <100ms for 100 UUIDs (batch DB query)
Lazy loading: Service only loaded when facets contain UUIDs

Service Integration:

// Lazy-loading pattern to avoid circular dependencies
private function getObjectCacheService(): ?ObjectCacheService
{
    if ($this->objectCacheServiceAttempted) {
        return $this->objectCacheService;
    }
    
    $this->objectCacheServiceAttempted = true;
    
    try {
        $this->objectCacheService = \OC::$server->get(ObjectCacheService::class);
        $this->logger->debug('ObjectCacheService loaded successfully');
    } catch (\Exception $e) {
        $this->logger->warning('ObjectCacheService not available for UUID resolution', [
            'error' => $e->getMessage()
        ]);
        return null;
    }
    
    return $this->objectCacheService;
}

This ensures the service is only loaded when facets containing UUIDs are encountered, avoiding performance overhead for regular facets.

5. Facetable Field Discovery

Automatic analysis of available fields and their characteristics to help frontends build dynamic facet interfaces.

Facetable Field Discovery

The system includes powerful discovery capabilities that analyze your data to determine which fields can be used for faceting and what types of facets are appropriate.

Discovery API

// Get facetable fields for a specific context
$facetableFields = $objectService->getFacetableFields($baseQuery, $sampleSize);

// Example with context filters
$baseQuery = [
    '@self' => ['register' => 1],
    '_search' => 'customer'
];

$facetableFields = $objectService->getFacetableFields($baseQuery, 100);

Discovery Response Structure

[
    '@self' => [
        'register' => [
            'type' => 'categorical',
            'description' => 'Register that contains the object',
            'facet_types' => ['terms'],
            'has_labels' => true,
            'sample_values' => [
                ['value' => 1, 'label' => 'Publications Register', 'count' => 150],
                ['value' => 2, 'label' => 'Events Register', 'count' => 75]
            ]
        ],
        'created' => [
            'type' => 'date',
            'description' => 'Date and time when the object was created',
            'facet_types' => ['date_histogram', 'range'],
            'intervals' => ['day', 'week', 'month', 'year'],
            'has_labels' => false,
            'date_range' => [
                'min' => '2023-01-01 00:00:00',
                'max' => '2024-12-31 23:59:59'
            ]
        ]
    ],
    'object_fields' => [
        'status' => [
            'type' => 'string',
            'description' => 'Object field: status',
            'facet_types' => ['terms'],
            'cardinality' => 'low',  // ≤50 unique values
            'sample_values' => ['published', 'draft', 'archived'],
            'appearance_rate' => 85  // Count of objects containing this field
        ],
        'priority' => [
            'type' => 'integer',
            'description' => 'Object field: priority',
            'facet_types' => ['range', 'terms'],
            'cardinality' => 'numeric',  // Numeric field type
            'sample_values' => ['1', '2', '3', '4', '5'],
            'appearance_rate' => 72  // Count of objects containing this field
        ]
    ]
]

Field Properties Explained

Key Terms

appearance_rate: The actual count of objects (from the analyzed sample) that contain this field. For example, if 100 objects were analyzed and 85 contained the 'status' field, the appearance_rate would be 85. This is not a percentage but an absolute count.

cardinality: Indicates the uniqueness characteristics of field values:

'low' - String fields with ≤50 unique values (suitable for terms facets)
'numeric' - Integer, float, or numeric string fields
'binary' - Boolean fields (true/false values only)
Not set for date fields (they use intervals instead)

Field Types and Characteristics

Metadata Fields (@self)

Predefined fields from the ObjectEntity table:

register - Categorical with labels from register table
schema - Categorical with labels from schema table
uuid - Identifier field (usually not suitable for faceting)
owner - Categorical user field
organisation - Categorical organisation field
application - Categorical application field
created/updated/published/depublished - Date fields with range support

Object Fields

Dynamically discovered from JSON object data:

string - Text fields (low cardinality suitable for terms facets)
integer/float - Numeric fields (suitable for range and terms facets)
date - Date fields (suitable for date_histogram and range facets)
boolean - Binary fields (suitable for terms facets)

Discovery Configuration

Field Analysis Parameters

Sample Size - Number of objects to analyze (default: 100)
Appearance Threshold - Minimum percentage of objects that must contain the field (default: 10%)
Cardinality Threshold - Maximum unique values for terms facets (default: 50)
Recursion Depth - Maximum nesting level to analyze (default: 2)

Field Filtering

The discovery system automatically filters out:

System fields (starting with @ or _)
Nested objects and arrays of objects
High cardinality string fields (more than 50 unique values)
Fields appearing in less than 10% of objects
Fields with inconsistent types (less than 70% type consistency)

API Integration

Discovery Parameter

Add _facetable=true to any search endpoint to include facetable field information:

GET /api/objects?_facetable=true&limit=0

Response includes additional facetable property:

[
    'results' => [],
    'total' => 0,
    'facetable' => [
        '@self' => [...],
        'object_fields' => [...]
    ]
]

Use discovery results to build facet configurations:

// Frontend example: Build facet config from discovery
const buildFacetConfig = (facetableFields) => {
    const config = { _facets: { '@self': {} } };
    
    // Add metadata facets
    Object.entries(facetableFields['@self']).forEach(([field, info]) => {
        if (info.facet_types.includes('terms')) {
            config._facets['@self'][field] = { type: 'terms' };
        } else if (info.facet_types.includes('date_histogram')) {
            config._facets['@self'][field] = { 
                type: 'date_histogram', 
                interval: 'month' 
            };
        }
    });
    
    // Add object field facets
    Object.entries(facetableFields.object_fields).forEach(([field, info]) => {
        if (info.facet_types.includes('terms')) {
            config._facets[field] = { type: 'terms' };
        } else if (info.facet_types.includes('range')) {
            config._facets[field] = { 
                type: 'range',
                ranges: generateRanges(info.sample_values)
            };
        }
    });
    
    return config;
};

API Structure

Basic Query Structure

$query = [
    // Search filters (same as searchObjects)
    '@self' => [
        'register' => 1,
        'schema' => 2
    ],
    'status' => 'active',
    '_search' => 'customer',
    
    // Facet configuration
    '_facets' => [
        // Metadata facets
        '@self' => [
            'register' => ['type' => 'terms'],
            'schema' => ['type' => 'terms'],
            'created' => [
                'type' => 'date_histogram',
                'interval' => 'month'
            ]
        ],
        
        // Object field facets
        'status' => ['type' => 'terms'],
        'priority' => ['type' => 'terms'],
        'price' => [
            'type' => 'range',
            'ranges' => [
                ['to' => 100],
                ['from' => 100, 'to' => 500],
                ['from' => 500]
            ]
        ]
    ]
];

$facets = $objectService->getFacetsForObjects($query);

Response Structure

[
    'facets' => [
        '@self' => [
            'register' => [
                'type' => 'terms',
                'buckets' => [
                    ['key' => 1, 'doc_count' => 150, 'label' => 'Register Name'],
                    ['key' => 2, 'doc_count' => 75, 'label' => 'Other Register']
                ]
            ],
            'created' => [
                'type' => 'date_histogram',
                'interval' => 'month',
                'buckets' => [
                    ['key' => '2024-01', 'doc_count' => 45],
                    ['key' => '2024-02', 'doc_count' => 67]
                ]
            ]
        ],
        'status' => [
            'type' => 'terms',
            'buckets' => [
                ['key' => 'active', 'doc_count' => 134],
                ['key' => 'inactive', 'doc_count' => 45]
            ]
        ],
        'price' => [
            'type' => 'range',
            'buckets' => [
                ['key' => '0-100', 'from' => 0, 'to' => 100, 'doc_count' => 120],
                ['key' => '100-500', 'from' => 100, 'to' => 500, 'doc_count' => 80],
                ['key' => '500+', 'from' => 500, 'doc_count' => 15]
            ]
        ]
    ]
]

1. Terms Aggregation

For categorical data like status, priority, category, etc.

'_facets' => [
    'status' => ['type' => 'terms'],
    'priority' => ['type' => 'terms'],
    '@self' => [
        'register' => ['type' => 'terms'],
        'schema' => ['type' => 'terms']
    ]
]

Response:

'status' => [
    'type' => 'terms',
    'buckets' => [
        ['key' => 'active', 'doc_count' => 134],
        ['key' => 'pending', 'doc_count' => 45],
        ['key' => 'inactive', 'doc_count' => 23]
    ]
]

2. Date Histogram

For time-based data with configurable intervals.

Supported intervals: day, week, month, year

'_facets' => [
    'event_date' => [
        'type' => 'date_histogram',
        'interval' => 'month'
    ],
    '@self' => [
        'created' => [
            'type' => 'date_histogram',
            'interval' => 'week'
        ]
    ]
]

Response:

'event_date' => [
    'type' => 'date_histogram',
    'interval' => 'month',
    'buckets' => [
        ['key' => '2024-01', 'doc_count' => 45],
        ['key' => '2024-02', 'doc_count' => 67],
        ['key' => '2024-03', 'doc_count' => 52]
    ]
]

3. Range Aggregation

For numeric data with custom buckets.

'_facets' => [
    'price' => [
        'type' => 'range',
        'ranges' => [
            ['to' => 100],                    // 0-100
            ['from' => 100, 'to' => 500],     // 100-500
            ['from' => 500, 'to' => 1000],    // 500-1000
            ['from' => 1000]                  // 1000+
        ]
    ]
]

Response:

'price' => [
    'type' => 'range',
    'buckets' => [
        ['key' => '0-100', 'to' => 100, 'doc_count' => 120],
        ['key' => '100-500', 'from' => 100, 'to' => 500, 'doc_count' => 80],
        ['key' => '500-1000', 'from' => 500, 'to' => 1000, 'doc_count' => 35],
        ['key' => '1000+', 'from' => 1000, 'doc_count' => 15]
    ]
]

Usage Examples

$query = [
    '@self' => ['register' => 1],
    'status' => 'active',
    '_facets' => [
        '@self' => [
            'register' => ['type' => 'terms'],
            'schema' => ['type' => 'terms']
        ],
        'status' => ['type' => 'terms'],
        'priority' => ['type' => 'terms']
    ]
];

$facets = $objectService->getFacetsForObjects($query);

// Use facet data for UI checkboxes
foreach ($facets['facets']['status']['buckets'] as $bucket) {
    $selected = ($bucket['key'] === 'active') ? 'checked' : '';
    echo "<input type='checkbox' {$selected}> {$bucket['key']} ({$bucket['doc_count']})\n";
}

$query = [
    '_facets' => [
        '@self' => [
            'created' => [
                'type' => 'date_histogram',
                'interval' => 'month'
            ]
        ]
    ]
];

$facets = $objectService->getFacetsForObjects($query);

// Use for timeline visualization
foreach ($facets['facets']['@self']['created']['buckets'] as $bucket) {
    echo "{$bucket['key']}: {$bucket['doc_count']} objects\n";
}

$query = [
    '_facets' => [
        'price' => [
            'type' => 'range',
            'ranges' => [
                ['to' => 100],
                ['from' => 100, 'to' => 500],
                ['from' => 500]
            ]
        ]
    ]
];

$facets = $objectService->getFacetsForObjects($query);

// Use for price filter UI
foreach ($facets['facets']['price']['buckets'] as $bucket) {
    $from = $bucket['from'] ?? 0;
    $to = $bucket['to'] ?? '∞';
    echo "€{$from} - €{$to}: {$bucket['doc_count']} items\n";
}

Integration with Search

$query = [
    // Search filters
    '@self' => [
        'register' => [1, 2, 3],
        'organisation' => 'IS NOT NULL'
    ],
    'status' => ['active', 'pending'],
    '_search' => 'important customer',
    '_published' => true,
    
    // Pagination
    '_limit' => 25,
    '_page' => 1,
    
    // Facet configuration
    '_facets' => [
        '@self' => [
            'register' => ['type' => 'terms'],
            'schema' => ['type' => 'terms']
        ],
        'status' => ['type' => 'terms'],
        'priority' => ['type' => 'terms']
    ]
];

// Get complete paginated results with facets
$result = $objectService->searchObjectsPaginated($query);

// Result contains:
// - results: Array of objects
// - total: Total count
// - page/pages: Pagination info
// - facets: Facet data

Disjunctive Faceting Example

// User has selected register=1 and status='active'
$query = [
    '@self' => ['register' => 1],
    'status' => 'active',
    '_facets' => [
        '@self' => ['register' => ['type' => 'terms']],
        'status' => ['type' => 'terms'],
        'priority' => ['type' => 'terms']
    ]
];

$facets = $objectService->getFacetsForObjects($query);

// Register facet shows ALL registers (not just register 1)
// Status facet shows ALL statuses (not just 'active')
// Priority facet shows counts for register=1 AND status='active'

// This allows users to change their register or status selection
// without losing the ability to see other options

The faceting system supports schema-driven facet discovery, allowing you to define which fields are facetable directly in your schema definitions.

Schema Property Configuration

Fields are marked as facetable in schema properties using the facetable flag:

{
  "properties": {
    "status": {
      "type": "string",
      "title": "Status",
      "description": "Current status of the item",
      "facetable": true,
      "example": "active"
    },
    "priority": {
      "type": "integer",
      "title": "Priority Level",
      "description": "Priority from 1 to 10",
      "facetable": true,
      "minimum": 1,
      "maximum": 10
    },
    "created_date": {
      "type": "string",
      "format": "date",
      "title": "Creation Date",
      "description": "When the item was created",
      "facetable": true
    },
    "internal_notes": {
      "type": "string",
      "title": "Internal Notes",
      "description": "Notes for internal use only",
      "facetable": false
    }
  }
}

Based on schema property definitions, the system automatically determines appropriate facet types:

String fields:
- Regular strings → terms facet
- Date/datetime format → date_histogram and range facets
- Email/URI/UUID format → terms facet
Numeric fields (integer/number): range and terms facets
Boolean fields: terms facet
Array fields: terms facet

Schema-Based Discovery Benefits

Performance Advantages:

No object data analysis required - discovery is instant
Consistent behavior - facets always available based on schema design
No sampling overhead - works regardless of data volume
Efficient - eliminates runtime field discovery queries

Consistency:

Facets are always available based on schema, not data content
No need to worry about sample sizes for discovery
Predictable facet availability across all registers

Backend Implementation

The system uses schema-based facet discovery through:

Schema Analysis: Pre-computes facet configuration when schema is saved
Facet Configuration Storage: Stores facet metadata in schema entity
Efficient Retrieval: Fast lookup without object data analysis

Core Components:

ObjectEntityMapper::getFacetableFieldsFromSchemas() - Schema-based discovery
Schema::regenerateFacetsFromProperties() - Automatic facet configuration
MariaDbFacetHandler - Handles JSON object field facets
MetaDataFacetHandler - Handles database table column facets

Performance Considerations

Performance Impact

Real-world performance testing shows the following response time impacts:

Regular API calls - Baseline response time
With faceting (_facets) - Adds approximately ~10ms
With discovery (_facetable=true) - Adds approximately ~15ms
Combined faceting + discovery - Adds approximately ~25ms total

These measurements are based on typical datasets and may vary depending on:

Database size and object complexity
Number of facet fields being analyzed
Sample size used for discovery (default: 100 objects)
Server hardware and database configuration

Asynchronous Operations

For improved performance when using multiple operations (facets + facetable discovery), the system provides asynchronous methods that run database operations concurrently using ReactPHP.

Performance Benefits

Instead of sequential execution (~50ms total):

Facetable discovery: ~15ms
Search results: ~10ms
Facets: ~10ms
Count: ~5ms

Operations run concurrently, reducing total time to ~15ms (longest operation).

Available Methods

searchObjectsPaginatedAsync(array $query): PromiseInterface

Returns a ReactPHP promise that resolves to the same structure as searchObjectsPaginated()
Runs search, count, facets, and facetable discovery concurrently
Ideal for async/await patterns or promise chains

searchObjectsPaginatedSync(array $query): array

Convenience method that executes the async version and waits for results
Provides performance benefits while maintaining synchronous interface
Drop-in replacement for searchObjectsPaginated() with better performance

Usage Examples

// Async with promise handling
$promise = $objectService->searchObjectsPaginatedAsync($query);
$promise->then(function ($results) {
    // Handle results
    return $results;
});

// Sync interface with async performance
$results = $objectService->searchObjectsPaginatedSync($query);

// Traditional sync method (slower for multiple operations)
$results = $objectService->searchObjectsPaginated($query);

When to Use Async Methods

Use async methods when: Requesting both facets and facetable discovery
Use sync methods when: Only requesting search results or single operation
Performance gain: Most significant with _facets + _facetable=true combinations

Optimizations

Database-level aggregations - Uses SQL GROUP BY for efficiency
Indexed fields - Metadata facets use indexed table columns
Disjunctive queries - Optimized to exclude only the relevant filter
Count optimization - Uses COUNT(*) instead of selecting all data
Schema-based discovery - No object data analysis required, instant discovery
Database indexes - Critical indexes on deleted, published, created, updated, organisation, owner
Query batching - Combines multiple facets where possible to reduce database round trips

Database Index Optimization

For optimal facet performance, ensure these indexes exist:

Critical Indexes:

deleted - Used in every query for lifecycle filtering
published - Used for publication status filtering
created, updated - Common facet fields
organisation, owner - Common facet fields
Composite indexes for filter combinations

Performance Targets:

With Indexes: 0.5-1 second for complex facet queries
Without Indexes: 7+ seconds for same queries
Register/Schema/Organisation facets: 50-100ms each (with proper indexes)
JSON field facets: Performance varies by dataset size

Quick Performance Wins:

Apply database migrations to add critical indexes
Use schema-based discovery instead of object analysis
Limit facet scope to essential fields
Use _limit=0 for facet-only queries
Remove _facetable=true if not needed (saves ~15ms)

Best Practices

Use metadata facets when possible - They perform better than JSON field facets
Limit range buckets - Too many ranges can impact performance
Consider caching - Facet results can be cached for frequently accessed data
Index JSON fields - Consider adding indexes for frequently faceted JSON fields
Use _facetable sparingly - Only request facetable discovery when building dynamic interfaces
Optimize sample size - Balance accuracy vs performance for facetable discovery (default: 100 objects)
Cache facetable results - Store discovery results for repeated interface building

Migration from Legacy System

Old Approach

// Legacy faceting
$config = [
    'filters' => ['register' => 1, 'status' => 'active'],
    '_queries' => ['status', 'priority', 'category']
];

$facets = $objectService->getFacets($config['filters'], $config['search']);

New Approach

// New comprehensive faceting
$query = [
    '@self' => ['register' => 1],
    'status' => 'active',
    '_facets' => [
        '@self' => ['register' => ['type' => 'terms']],
        'status' => ['type' => 'terms'],
        'priority' => ['type' => 'terms'],
        'category' => ['type' => 'terms']
    ]
];

$facets = $objectService->getFacetsForObjects($query);

Backward Compatibility

The system maintains backward compatibility. If no _facets configuration is provided, it falls back to the legacy getFacets method.

UI Integration Examples

// React component that discovers and builds facets dynamically
const DynamicFacetInterface = ({ baseQuery }) => {
  const [facetableFields, setFacetableFields] = useState(null);
  const [facetData, setFacetData] = useState(null);
  const [filters, setFilters] = useState({});

  useEffect(() => {
    // Discover available facetable fields
    const discoverFacets = async () => {
      const response = await fetch('/api/objects?_facetable=true&limit=0', {
        method: 'POST',
        body: JSON.stringify(baseQuery)
      });
      const data = await response.json();
      setFacetableFields(data.facetable);
      
      // Build initial facet configuration
      const facetConfig = buildFacetConfig(data.facetable);
      
      // Get actual facet data
      const facetResponse = await fetch('/api/objects', {
        method: 'POST',
        body: JSON.stringify(Object.assign({}, baseQuery, facetConfig))
      });
      const facetData = await facetResponse.json();
      setFacetData(facetData.facets);
    };

    discoverFacets();
  }, [baseQuery]);

  const buildFacetConfig = (facetableFields) => {
    const config = { _facets: { '@self': {} } };
    
    // Add metadata facets
    Object.entries(facetableFields['@self'] || {}).forEach(([field, info]) => {
      if (info.facet_types.includes('terms')) {
        config._facets['@self'][field] = { type: 'terms' };
      } else if (info.facet_types.includes('date_histogram')) {
        config._facets['@self'][field] = { 
          type: 'date_histogram', 
          interval: 'month' 
        };
      }
    });
    
    // Add object field facets
    Object.entries(facetableFields.object_fields || {}).forEach(([field, info]) => {
      if (info.facet_types.includes('terms')) {
        config._facets[field] = { type: 'terms' };
      }
    });
    
    return config;
  };

  if (!facetableFields || !facetData) {
    return <div>Loading facets...</div>;
  }

  return (
    <div className="dynamic-facets">
      <h2>Available Filters</h2>
      
      {/* Metadata facets */}
      {Object.entries(facetData['@self'] || {}).map(([field, facet]) => (
        <FacetFilter 
          key={'@self.' + field}
          field={'@self.' + field}
          facet={facet}
          fieldInfo={facetableFields['@self'][field]}
          onFilterChange={handleFilterChange}
        />
      ))}
      
      {/* Object field facets */}
      {Object.entries(facetData).filter(([key]) => key !== '@self').map(([field, facet]) => (
        <FacetFilter 
          key={field}
          field={field}
          facet={facet}
          fieldInfo={facetableFields.object_fields[field]}
          onFilterChange={handleFilterChange}
        />
      ))}
    </div>
  );
};

// Enhanced facet component with discovery information
const FacetFilter = ({ field, facet, fieldInfo, onFilterChange }) => {
  return (
    <div className="facet-filter">
      <h3>
        {fieldInfo?.description || field}
        <span className="facet-info">
          ({fieldInfo?.type}, {fieldInfo?.appearance_rate} objects)
        </span>
      </h3>
      
      {facet.type === 'terms' && (
        <div className="checkbox-list">
          {facet.buckets.map(bucket => (
            <label key={bucket.key}>
              <input 
                type="checkbox" 
                onChange={() => onFilterChange(field, bucket.key)}
              />
              {bucket.label || bucket.key} ({bucket.results})
            </label>
          ))}
        </div>
      )}
      
      {facet.type === 'range' && (
        <div className="range-list">
          {facet.buckets.map(bucket => (
            <button 
              key={bucket.key}
              onClick={() => onFilterChange(field, bucket)}
            >
              {bucket.key}: {bucket.results} items
            </button>
          ))}
        </div>
      )}
      
      {facet.type === 'date_histogram' && (
        <div className="timeline">
          <div className="interval-selector">
            {fieldInfo?.intervals?.map(interval => (
              <button 
                key={interval}
                onClick={() => changeInterval(field, interval)}
              >
                {interval}
              </button>
            ))}
          </div>
          {facet.buckets.map(bucket => (
            <div key={bucket.key} className="timeline-item">
              <span>{bucket.key}</span>
              <span>{bucket.results}</span>
            </div>
          ))}
        </div>
      )}
      
      {fieldInfo?.sample_values && (
        <div className="sample-values">
          <small>Sample values: {fieldInfo.sample_values.slice(0, 3).join(', ')}</small>
        </div>
      )}
    </div>
  );
};

PHP Controller Example

class SearchController extends Controller
{
    public function search(Request $request): JsonResponse
    {
        $query = [
            // Extract filters from request
            '@self' => [
                'register' => $request->get('register'),
                'schema' => $request->get('schema')
            ],
            'status' => $request->get('status'),
            '_search' => $request->get('q'),
            '_page' => $request->get('page', 1),
            '_limit' => $request->get('limit', 20),
            
            // Facet configuration
            '_facets' => [
                '@self' => [
                    'register' => ['type' => 'terms'],
                    'schema' => ['type' => 'terms']
                ],
                'status' => ['type' => 'terms'],
                'priority' => ['type' => 'terms'],
                'category' => ['type' => 'terms']
            ]
        ];

        $result = $this->objectService->searchObjectsPaginated($query);
        
        // Add facetable field discovery if requested
        if ($request->get('_facetable') === 'true') {
            $baseQuery = $query;
            unset($baseQuery['_facets'], $baseQuery['_limit'], $baseQuery['_page']);
            
            $result['facetable'] = $this->objectService->getFacetableFields(
                $baseQuery, 
                (int) $request->get('_sample_size', 100)
            );
        }
        
        return new JsonResponse($result);
    }
    
    public function getFacetableFields(Request $request): JsonResponse
    {
        $baseQuery = [
            '@self' => [
                'register' => $request->get('register'),
                'schema' => $request->get('schema')
            ],
            '_search' => $request->get('q')
        ];
        
        $sampleSize = (int) $request->get('sample_size', 100);
        
        $facetableFields = $this->objectService->getFacetableFields($baseQuery, $sampleSize);
        
        return new JsonResponse([
            'facetable' => $facetableFields,
            'sample_size' => $sampleSize,
            'base_query' => $baseQuery
        ]);
    }
}

Testing

Unit Test Examples

class FacetingTest extends TestCase
{
    public function testBasicTermsFacet(): void
    {
        $query = [
            '_facets' => [
                'status' => ['type' => 'terms']
            ]
        ];

        $facets = $this->objectService->getFacetsForObjects($query);
        
        $this->assertArrayHasKey('facets', $facets);
        $this->assertArrayHasKey('status', $facets['facets']);
        $this->assertEquals('terms', $facets['facets']['status']['type']);
        $this->assertIsArray($facets['facets']['status']['buckets']);
    }

    public function testDisjunctiveFaceting(): void
    {
        // Create test data with different statuses
        $this->createTestObjects(['status' => 'active'], 10);
        $this->createTestObjects(['status' => 'inactive'], 5);

        $query = [
            'status' => 'active',  // Filter by active
            '_facets' => [
                'status' => ['type' => 'terms']
            ]
        ];

        $facets = $this->objectService->getFacetsForObjects($query);
        
        // Should show both active AND inactive in facets (disjunctive)
        $statusBuckets = $facets['facets']['status']['buckets'];
        $this->assertCount(2, $statusBuckets);
        
        $activeCount = $this->findBucketByKey($statusBuckets, 'active')['results'];
        $inactiveCount = $this->findBucketByKey($statusBuckets, 'inactive')['results'];
        
        $this->assertEquals(10, $activeCount);
        $this->assertEquals(5, $inactiveCount);
    }

    public function testFacetableFieldDiscovery(): void
    {
        // Create test objects with various field types
        $this->createTestObjects([
            'status' => 'active',
            'priority' => 1,
            'created_date' => '2024-01-15',
            'is_featured' => true
        ], 5);
        
        $this->createTestObjects([
            'status' => 'inactive', 
            'priority' => 2,
            'created_date' => '2024-02-20',
            'is_featured' => false
        ], 3);

        $baseQuery = ['@self' => ['register' => 1]];
        $facetableFields = $this->objectService->getFacetableFields($baseQuery, 50);
        
        // Check structure
        $this->assertArrayHasKey('@self', $facetableFields);
        $this->assertArrayHasKey('object_fields', $facetableFields);
        
        // Check metadata fields
        $this->assertArrayHasKey('register', $facetableFields['@self']);
        $this->assertEquals('categorical', $facetableFields['@self']['register']['type']);
        $this->assertContains('terms', $facetableFields['@self']['register']['facet_types']);
        
        // Check object fields
        $this->assertArrayHasKey('status', $facetableFields['object_fields']);
        $this->assertEquals('string', $facetableFields['object_fields']['status']['type']);
        $this->assertContains('terms', $facetableFields['object_fields']['status']['facet_types']);
        
        $this->assertArrayHasKey('priority', $facetableFields['object_fields']);
        $this->assertEquals('integer', $facetableFields['object_fields']['priority']['type']);
        $this->assertContains('range', $facetableFields['object_fields']['priority']['facet_types']);
        
        $this->assertArrayHasKey('is_featured', $facetableFields['object_fields']);
        $this->assertEquals('boolean', $facetableFields['object_fields']['is_featured']['type']);
        $this->assertContains('terms', $facetableFields['object_fields']['is_featured']['facet_types']);
    }

    public function testFacetableFieldFiltering(): void
    {
        // Create objects with high cardinality field (should be filtered out)
        for ($i = 0; $i < 100; $i++) {
            $this->createTestObjects([
                'unique_id' => 'id_' . $i,  // High cardinality
                'category' => 'cat_' . ($i % 3)  // Low cardinality
            ], 1);
        }

        $facetableFields = $this->objectService->getFacetableFields([], 100);
        
        // High cardinality field should be filtered out
        $this->assertArrayNotHasKey('unique_id', $facetableFields['object_fields']);
        
        // Low cardinality field should be included
        $this->assertArrayHasKey('category', $facetableFields['object_fields']);
        $this->assertEquals('low', $facetableFields['object_fields']['category']['cardinality']);
    }

    public function testFacetableFieldAppearanceThreshold(): void
    {
        // Create objects where some fields appear in less than 10% of objects
        $this->createTestObjects(['common_field' => 'value1'], 50);  // 100% appearance
        $this->createTestObjects(['rare_field' => 'value2'], 2);     // 4% appearance
        
        $facetableFields = $this->objectService->getFacetableFields([], 50);
        
        // Common field should be included
        $this->assertArrayHasKey('common_field', $facetableFields['object_fields']);
        
        // Rare field should be filtered out (below 10% threshold)
        $this->assertArrayNotHasKey('rare_field', $facetableFields['object_fields']);
    }
}

Caching and Label Resolution

Overview

The faceting system includes an intelligent caching mechanism that resolves metadata field IDs (registers, schemas, organisations) and object UUIDs to human-readable names without sacrificing performance.

How It Works

Label Resolution Process

When facets are returned, the system automatically resolves IDs and UUIDs to human-readable names:

For metadata fields ('_register', '_schema', '_organisation'):

Collects IDs from all facet buckets for batch processing
Batch loads entities using optimized database queries
Caches results to prevent repeated database calls
Resolves labels by mapping IDs to entity names/titles
Sorts alphabetically by label for consistent ordering (case-insensitive A-Z)

For object fields (any field containing UUIDs):

Detects UUIDs by checking for hyphenated values
Batch resolves using ObjectCacheService.getMultipleObjectNames()
Searches caches (in-memory and distributed) before database
Extracts names from common fields (naam, name, title, etc.)
Sorts alphabetically by resolved names for user-friendly display

Example Response

Before label resolution:

{
  "_register": {
    "buckets": [
      { "value": 5, "count": 114474, "label": 5 },
      { "value": 6, "count": 8794, "label": 6 }
    ]
  }
}

After label resolution (alphabetically sorted):

{
  "_register": {
    "buckets": [
      { "value": 6, "count": 8794, "label": "Events Register" },
      { "value": 5, "count": 114474, "label": "Publications Register" }
    ]
  }
}

Note: Buckets are sorted alphabetically by label (A-Z), not by count or value.

Sorting Behavior

All term-based facets are automatically sorted alphabetically by label:

Metadata facets (_register, _schema, _organisation):

Sorted by resolved entity names (e.g., "Events Register", "Publications Register")
Case-insensitive alphabetical order (A, a, B, b, etc.)

Object field facets (status, category, type, etc.):

Sorted by their resolved labels (UUIDs converted to object names)
Case-insensitive alphabetical order
Numeric strings sorted as text (e.g., "1", "10", "2")
UUIDs automatically resolved to human-readable object names using ObjectCacheService

Date histogram facets:

Not sorted alphabetically (chronological order maintained)

Range facets:

Not sorted alphabetically (range order maintained)

Caching Strategy

Static Caching (SaveObjects)

Used during bulk save operations:

Schema Cache - Stores loaded schemas to avoid repeated DB queries
Register Cache - Stores loaded registers to avoid repeated DB queries
Lifetime - Lasts for the duration of the save operation
Clearing - Automatically cleared after bulk operation completes

// Example: Schema caching during bulk save
$schema = $this->loadSchemaWithCache($schemaId);
// Subsequent calls for same $schemaId return cached instance

Entity Caching (ObjectService)

Used during object retrieval and faceting:

getCachedEntities() - Generic caching method for schemas/registers
Batch Loading - Fetches multiple entities in a single query
Fallback Mechanism - Falls back to DB if cache unavailable

// Example: Batch loading registers for facets
$registers = $this->getCachedEntities(
    'register', 
    $registerIds, 
    [$this->registerMapper, 'findMultiple']
);

Mapper Optimizations

Specialized batch loading methods:

findMultipleOptimized() - Single query for multiple IDs
Returns keyed array - ID => Entity for fast lookups
Used by facet processing - Resolves labels efficiently

// Example: Optimized batch loading
$schemas = $this->schemaMapper->findMultipleOptimized([1, 2, 3]);
// Result: [1 => Schema1, 2 => Schema2, 3 => Schema3]

When object fields contain references to other objects via UUIDs, the system automatically resolves them to human-readable names:

How it works:

Detect UUIDs - Identifies values that look like UUIDs (contain hyphens)
Batch lookup - Uses ObjectCacheService.getMultipleObjectNames() for efficient batch retrieval
Cache first - Checks in-memory and distributed caches before database
Multi-source - Searches both organisations and objects tables
Name extraction - Uses common name fields (naam, name, title, contractNummer, achternaam)
Fallback gracefully - Uses UUID if name cannot be resolved

Example transformation:

// Before UUID resolution:
{
  "customer": {
    "buckets": [
      { "value": "f47ac10b-58cc-4372-a567-0e02b2c3d479", "count": 42, "label": "f47ac10b-58cc-4372-a567-0e02b2c3d479" }
    ]
  }
}

// After UUID resolution (alphabetically sorted):
{
  "customer": {
    "buckets": [
      { "value": "f47ac10b-58cc-4372-a567-0e02b2c3d479", "count": 42, "label": "Acme Corporation" }
    ]
  }
}

Performance considerations:

Cached UUIDs - Already resolved names retrieved instantly from cache
Batch loading - New UUIDs loaded together in a single query
Persistent cache - Resolved names stored in distributed cache for all users
Minimal overhead - Only processes values that look like UUIDs (contain hyphens)

Performance Benefits

Without Caching

1 query per unique ID in facet results
N+1 query problem for large facet sets
Response time increases linearly with unique values

With Caching

1 query for all IDs per facet field
No redundant queries for same entities
Consistent performance regardless of facet size

Real-World Impact

For a facet with 20 unique register IDs:

Without caching: 20 separate queries = ~500ms
With caching: 1 batch query = ~25ms
Performance gain: 20x faster

Implementation Details

SOLR returns raw facets with numeric IDs
processFacetResponse() detects metadata fields
formatMetadataFacetData() called for register/schema/organisation
resolveRegisterLabels()/resolveSchemaLabels() batch load entities
Labels mapped to buckets before returning to frontend

Code Flow Diagram

Which Fields Get Label Resolution

Always Resolved:

'_register' → Register title
'_schema' → Schema title or name
'_organisation' → Organisation name

Never Resolved:

'_created', '_updated' → Date fields use dates as labels
'_application' → String values remain as-is
Object fields → Use raw values as labels

Configuration

The system limits the number of buckets (unique values) returned per facet to prevent performance issues:

Default limit: 1000 buckets per facet

Metadata facets (_register, _schema, _organisation): 1000 buckets
Object field facets (status, category, type, etc.): 1000 buckets

Why limit buckets?

Prevents excessive memory usage
Keeps API responses manageable
Ensures consistent performance

Need more buckets? You can modify the limit in GuzzleSolrService.php:

Line 7851: Metadata fields
Line 7876: Object fields
Line 7906: Fallback facets
Line 8231: buildTermsFacet() method (accepts $limit parameter)

For unlimited buckets, set 'limit' => -1 (use with caution!):

$facetConfig = [
    'type' => 'terms',
    'field' => 'self_register',
    'limit' => -1, // Unlimited buckets
    'mincount' => 1
];

Note: Very large facet sets may impact:

API response time
Frontend rendering performance
Memory usage on both server and client

Disabling Caching

Caching is currently always enabled, but can be modified by changing the 'getCachedEntities()' method implementation:

private function getCachedEntities(...): array
{
    // Current: Always use fallback (cache disabled)
    return call_user_func($fallbackFunc, $ids);
    
    // To enable caching: Implement cache logic here
}

Customizing Label Format

Modify the resolve methods to customize label formatting:

private function resolveSchemaLabels(array $ids): array
{
    // Current: Uses title or name
    $labels[$id] = $schema->getTitle() ?? $schema->getName() ?? "Schema $id";
    
    // Customize: Add more information
    $labels[$id] = $schema->getTitle() . ' (' . $schema->getVersion() . ')';
}

Troubleshooting

Labels Showing as IDs

If facet labels are showing numeric IDs instead of names:

Verify the field is in the metadata fields list ('_register', '_schema', '_organisation')
Check database has entities with those IDs
Ensure entities have 'title'/'name' properties set
Review logs for label resolution errors

Performance Issues

If facet queries are slow:

Ensure batch loading methods are being used
Check database indexes on ID columns
Consider implementing actual caching in 'getCachedEntities()'
Monitor number of unique IDs per facet

Cache Invalidation

If stale labels appear after entity updates:

Static cache clears automatically after operations
For persistent cache (when implemented), clear on entity updates
Consider cache TTL for production deployments

Conclusion

The new faceting system provides a powerful, flexible, and user-friendly approach to building faceted search interfaces. It combines the best practices from modern search systems like Elasticsearch with the specific needs of the OpenRegister application.

Key benefits:

Better UX - Disjunctive faceting prevents options from disappearing
More flexible - Supports multiple facet types and data sources
Better performance - Optimized database queries and intelligent caching
Smart label resolution - Automatic conversion of IDs to human-readable names
Modern API - Familiar structure for developers
Backward compatible - Existing code continues to work
Dynamic discovery - Automatic detection of facetable fields helps build intelligent interfaces
Database-oriented - All analysis happens at the database level for optimal performance

Facetable Discovery Benefits

The facetable field discovery system provides several key advantages:

Dynamic Interface Building - Frontends can automatically discover and build facet interfaces without hardcoding field lists
Data-Driven Configuration - Facet types and options are determined by analyzing actual data
Context Awareness - Discovery respects current filters to show relevant faceting options
Performance Optimization - Database-level analysis ensures efficient field discovery
Type Intelligence - Automatic detection of field types enables appropriate facet configurations

Usage Recommendations

Use _facetable=true for initial interface discovery
Cache discovery results for frequently accessed configurations
Combine with regular faceting for complete search interfaces
Leverage sample data to show users what to expect
Respect appearance rates to focus on commonly used fields

The system is designed to grow with your application's needs while maintaining excellent performance and user experience. The addition of facetable discovery makes it even easier to build intelligent, data-driven search interfaces that adapt to your content automatically.

Technical Architecture

This section provides detailed visualization of the faceting system's architecture and data flow.

Faceting Request Flow

UUID Resolution Process

Disjunctive Faceting Implementation

Caching Strategy

Performance Characteristics

Facet Query Performance:

Without Faceting:    ~50ms  (search only)
With Faceting:       ~80ms  (search + 5 facets)
With UUID Resolution: ~120ms (search + 5 facets + 100 UUID resolutions)
With Full Caching:   ~60ms  (search + 5 facets + cached UUIDs)

Caching Impact:

Memory Cache Hit:     <10ms   per UUID resolution
Distributed Cache:    <50ms   per batch (100 UUIDs)
Database Query:       <100ms  per batch (100 UUIDs)
No Cache:             ~1000ms for 100 individual queries

Code Examples

use OCA\OpenRegister\Service\FacetService;

// Get facetable fields
$facetableFields = $facetService->getFacetableFields([
    '@self' => ['register' => 5],
    '_search' => 'budget'
], 100);

// Build facet configuration
$facets = [
    'status' => [
        'type' => 'terms',
        'field' => 'status_s',
        'limit' => 50
    ],
    'created' => [
        'type' => 'date_histogram',
        'field' => 'self_created',
        'interval' => 'month'
    ]
];

// Execute search with facets
$results = $objectService->findObjects([
    '_source' => 'index',
    '_facetable' => true,
    '_facets' => $facets
]);

// Access facet data
$facets = $results['facets'];

foreach ($facets['@self'] as $fieldName => $facetData) {
    echo "Facet: " . $facetData['description'] . "\n";
    
    foreach ($facetData['data'] as $bucket) {
        echo "  - " . $bucket['label'] . ": " . $bucket['count'] . "\n";
    }
}

// Process object field facets
foreach ($facets['object_fields'] as $fieldName => $facetData) {
    echo "Object Field: " . $fieldName . "\n";
    
    foreach ($facetData['data'] as $bucket) {
        echo "  - " . $bucket['value'] . ": " . $bucket['count'] . "\n";
    }
}

Testing

# Run faceting tests
vendor/bin/phpunit tests/Service/FacetServiceTest.php

# Test specific scenarios
vendor/bin/phpunit --filter testDisjunctiveFaceting
vendor/bin/phpunit --filter testUUIDResolution
vendor/bin/phpunit --filter testFacetDiscovery

# Integration tests
vendor/bin/phpunit tests/Integration/FacetIntegrationTest.php

Test Coverage:

Facet type detection
UUID resolution and caching
Disjunctive faceting behavior
Alphabetical sorting
Date histogram buckets
Range aggregations
Performance benchmarks

Overview​

Key Features​

1. Disjunctive Faceting​

2. Multiple Facet Types​

3. Dual Data Sources​

4. Enhanced Labels with Caching​

UUID Resolution Technical Implementation​

5. Facetable Field Discovery​

Facetable Field Discovery​

Discovery API​

Discovery Response Structure​

Field Properties Explained​

Key Terms​

Field Types and Characteristics​

Metadata Fields (@self)​

Object Fields​

Discovery Configuration​

Field Analysis Parameters​

Field Filtering​

API Integration​

Discovery Parameter​

Dynamic Facet Configuration​

API Structure​

Basic Query Structure​

Response Structure​

Facet Types​

1. Terms Aggregation​

2. Date Histogram​

3. Range Aggregation​

Usage Examples​

Basic Enumerated Facets​

Date Timeline Facets​

Price Range Facets​

Integration with Search​

Combined Search and Facets​

Disjunctive Faceting Example​

Schema-Based Facet Configuration​

Schema Property Configuration​

Automatic Facet Type Detection​

Schema-Based Discovery Benefits​

Backend Implementation​

Performance Considerations​

Performance Impact​

Asynchronous Operations​

Performance Benefits​

Available Methods​

Usage Examples​

When to Use Async Methods​

Optimizations​

Database Index Optimization​

Best Practices​

Migration from Legacy System​

Old Approach​

New Approach​

Backward Compatibility​

UI Integration Examples​

Dynamic Facet Discovery​

Enhanced Facet Component​

PHP Controller Example​

Testing​

Unit Test Examples​

Caching and Label Resolution​

Overview​

How It Works​

Label Resolution Process​

Example Response​

Sorting Behavior​

Caching Strategy​

Static Caching (SaveObjects)​

Entity Caching (ObjectService)​

Mapper Optimizations​

UUID Resolution for Object Field Facets​

Performance Benefits​

Without Caching​

With Caching​

Real-World Impact​

Implementation Details​

Facet Processing Pipeline​

Code Flow Diagram​

Which Fields Get Label Resolution​

Overview

Key Features

1. Disjunctive Faceting

2. Multiple Facet Types

3. Dual Data Sources

4. Enhanced Labels with Caching

UUID Resolution Technical Implementation

5. Facetable Field Discovery

Facetable Field Discovery

Discovery API

Discovery Response Structure

Field Properties Explained

Key Terms

Field Types and Characteristics

Metadata Fields (@self)

Object Fields

Discovery Configuration

Field Analysis Parameters

Field Filtering

API Integration

Discovery Parameter

Dynamic Facet Configuration

API Structure

Basic Query Structure

Response Structure

Facet Types

1. Terms Aggregation

2. Date Histogram

3. Range Aggregation

Usage Examples

Basic Enumerated Facets

Date Timeline Facets

Price Range Facets

Integration with Search

Combined Search and Facets

Disjunctive Faceting Example

Schema-Based Facet Configuration

Schema Property Configuration

Automatic Facet Type Detection

Schema-Based Discovery Benefits

Backend Implementation

Performance Considerations

Performance Impact

Asynchronous Operations

Performance Benefits

Available Methods

Usage Examples

When to Use Async Methods

Optimizations

Database Index Optimization

Best Practices

Migration from Legacy System

Old Approach

New Approach

Backward Compatibility

UI Integration Examples

Dynamic Facet Discovery

Enhanced Facet Component

PHP Controller Example

Testing

Unit Test Examples

Caching and Label Resolution

Overview

How It Works

Label Resolution Process

Example Response

Sorting Behavior

Caching Strategy

Static Caching (SaveObjects)

Entity Caching (ObjectService)

Mapper Optimizations

UUID Resolution for Object Field Facets

Performance Benefits

Without Caching

With Caching

Real-World Impact

Implementation Details

Facet Processing Pipeline

Code Flow Diagram

Which Fields Get Label Resolution