← Back to Architecture ← Back to Table of Contents

3.2 Model Behaviors

This section provides comprehensive documentation for KMP’s custom Model Behaviors, which provide reusable functionality for common data management patterns across the application.

Table of Contents

Overview

KMP implements three custom CakePHP behaviors that encapsulate common data management patterns:

  1. ActiveWindowBehavior - Temporal filtering for date-bounded entities
  2. JsonFieldBehavior - Enhanced JSON field querying capabilities
  3. SortableBehavior - Comprehensive sortable list management

These behaviors promote code reuse, ensure consistent data handling, and provide powerful querying capabilities across the application.

Behavior Architecture

graph TD
    TableClass[Table Class] --> |addBehavior()| Behavior[Behavior Instance]
    Behavior --> |beforeSave()| ORMEvents[ORM Events]
    Behavior --> |Custom Finders| QueryBuilder[Query Builder]
    Behavior --> |Methods| BusinessLogic[Business Logic]
    BusinessLogic --> Database[(Database)]
    
    subgraph "Behavior Types"
        ActiveWindow[ActiveWindow<br/>Temporal Filtering]
        JsonField[JsonField<br/>JSON Querying] 
        Sortable[Sortable<br/>Position Management]
    end

ActiveWindow Behavior

The ActiveWindowBehavior provides sophisticated temporal filtering for entities with date-bounded lifecycles, enabling queries for current, upcoming, or expired records.

Features

Database Schema

Tables using ActiveWindow behavior require these fields:

CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    start_on DATETIME NOT NULL,        -- When record becomes active
    expires_on DATETIME NULL,          -- When record expires (NULL = permanent)
    -- other fields...
    
    INDEX idx_active_window (start_on, expires_on),
    INDEX idx_start_on (start_on),
    INDEX idx_expires_on (expires_on)
);

Configuration

// In Table initialize() method
public function initialize(array $config): void
{
    parent::initialize($config);
    
    // Basic configuration (no options required)
    $this->addBehavior('ActiveWindow');
}

Query Methods

findCurrent()

Find records that are currently active:

// Find currently active officer assignments
$activeOfficers = $this->Officers->find('current');

// Find what was active at a specific historical date  
$historicalDate = new DateTime('2023-12-01');
$activeAtDate = $this->Officers->find('current', effectiveDate: $historicalDate);

// Chain with other conditions
$currentMemberOfficers = $this->Officers->find('current')
    ->where(['member_id' => $memberId])
    ->contain(['Offices', 'Members']);

Generated SQL Logic:

WHERE start_on <= :effectiveDate 
  AND (expires_on >= :effectiveDate OR expires_on IS NULL)

findUpcoming()

Find records that will be active in the future or haven’t expired yet:

// Find upcoming officer appointments
$upcomingOfficers = $this->Officers->find('upcoming');

// Find appointments upcoming as of specific date
$futureDate = new DateTime('2024-06-01');  
$upcomingAtDate = $this->Officers->find('upcoming', effectiveDate: $futureDate);

// Find upcoming warrants for preparation
$upcomingWarrants = $this->Warrants->find('upcoming')
    ->where(['processed' => false])
    ->orderBy(['start_on' => 'ASC']);

Generated SQL Logic:

WHERE start_on > :effectiveDate 
   OR (expires_on > :effectiveDate OR expires_on IS NULL)

findPrevious()

Find records that have definitively expired:

// Find expired officer assignments for historical analysis
$expiredOfficers = $this->Officers->find('previous');

// Find assignments expired before cutoff date
$cutoffDate = new DateTime('2023-12-31');
$expiredBefore = $this->Officers->find('previous', effectiveDate: $cutoffDate);

// Archive expired warrants
$expiredWarrants = $this->Warrants->find('previous')
    ->where(['archived' => false])
    ->contain(['Members']);

Generated SQL Logic:

WHERE expires_on < :effectiveDate

KMP Use Cases

Officer Assignment Management

// Officers Plugin - Track officer appointment lifecycle
class OfficersTable extends Table
{
    public function initialize(array $config): void
    {
        $this->addBehavior('ActiveWindow');
    }
    
    // Find current officers for a branch
    public function getCurrentBranchOfficers(int $branchId): Query
    {
        return $this->find('current')
            ->matching('Offices.Departments', function ($q) use ($branchId) {
                return $q->where(['Departments.branch_id' => $branchId]);
            });
    }
}

Warrant Period Validation

// Core KMP - Warrant temporal management
class WarrantsTable extends Table
{
    public function validateMemberWarrant(int $memberId, ?DateTime $effectiveDate = null): bool
    {
        $currentWarrants = $this->find('current', effectiveDate: $effectiveDate)
            ->where(['member_id' => $memberId])
            ->count();
            
        return $currentWarrants > 0;
    }
}

Activity Authorization Windows

// Activities Plugin - Time-bounded permissions
class AuthorizationsTable extends Table  
{
    public function getMemberActiveAuthorizations(int $memberId): Query
    {
        return $this->find('current')
            ->where(['member_id' => $memberId, 'status' => 'approved']);
    }
}

Advanced Usage Patterns

Batch Processing with Date Context

// Process records with custom effective date
public function processExpiredRecords(DateTime $processDate): array
{
    $expired = $this->find('previous', effectiveDate: $processDate)
        ->where(['processed' => false])
        ->toArray();
        
    foreach ($expired as $record) {
        $this->markAsProcessed($record);
    }
    
    return $expired;
}

Reporting and Analytics

// Generate temporal reports
public function getActivityReport(DateTime $startDate, DateTime $endDate): array
{
    return [
        'active_at_start' => $this->find('current', effectiveDate: $startDate)->count(),
        'active_at_end' => $this->find('current', effectiveDate: $endDate)->count(),
        'expired_during_period' => $this->find()
            ->where([
                'expires_on >=' => $startDate,
                'expires_on <' => $endDate
            ])->count(),
    ];
}

JsonField Behavior

The JsonFieldBehavior enables deep querying into JSON field structures using database-native JSON functions, providing powerful search capabilities for dynamic data.

Features

Database Requirements

Configuration

// In Table initialize() method
public function initialize(array $config): void
{
    parent::initialize($config);
    
    // Basic configuration
    $this->addBehavior('JsonField');
    
    // Future: Extended configuration options
    // $this->addBehavior('JsonField', [
    //     'fields' => ['additional_info', 'metadata'],
    //     'cacheExpire' => 3600
    // ]);
}

Query Methods

addJsonWhere()

Add JSON path-based WHERE conditions to queries:

/**
 * @param SelectQuery $query The query to modify
 * @param string $field JSON field name in table
 * @param string $path JSON path using $.notation  
 * @param mixed $value Value to match against
 * @return SelectQuery Modified query
 */
public function addJsonWhere($query, $field, $path, $value): SelectQuery

JSON Path Syntax

The behavior supports standard JSON Path syntax:

Syntax Description Example
$.field Root level field $.name
$.nested.field Nested object field $.contact.email
$.array[0] Array element by index $.phones[0]
$.array[*].field All array elements’ field $.addresses[*].city

Usage Examples

Member Preference Queries

// Find members with notifications enabled
$notificationEnabled = $this->Members->find()
    ->addJsonWhere('additional_info', '$.preferences.notifications', true);

// Find members by emergency contact relationship
$spouseContacts = $this->Members->find()  
    ->addJsonWhere('additional_info', '$.emergency.relationship', 'spouse');

// Complex nested queries
$localMembers = $this->Members->find()
    ->addJsonWhere('additional_info', '$.address.state', 'CA')
    ->addJsonWhere('additional_info', '$.preferences.local_events', true);

Activity Metadata Searches

// Activities Plugin - Search event requirements
$outdoorActivities = $this->Events->find()
    ->addJsonWhere('metadata', '$.requirements.location_type', 'outdoor');

// Find events by equipment requirements  
$archeryEvents = $this->Events->find()
    ->addJsonWhere('metadata', '$.equipment[*].type', 'archery');

Officer Qualification Tracking

// Officers Plugin - Search officer certifications
$certifiedInstructors = $this->Officers->find()  
    ->addJsonWhere('qualifications', '$.certifications[*].type', 'instructor')
    ->addJsonWhere('qualifications', '$.certifications[*].status', 'current');

KMP Use Cases

Member Profile Management

class MembersTable extends Table
{
    public function findByPreference(string $preference, $value): Query
    {
        return $this->find()
            ->addJsonWhere('additional_info', "$.preferences.{$preference}", $value);
    }
    
    public function findByEmergencyContactType(string $relationship): Query
    {
        return $this->find()
            ->addJsonWhere('additional_info', '$.emergency.relationship', $relationship);
    }
    
    public function findWithDietaryRestrictions(): Query
    {
        return $this->find()
            ->addJsonWhere('additional_info', '$.dietary.has_restrictions', true);
    }
}

Application Settings Management

class AppSettingsTable extends Table
{
    public function getSettingByPath(string $path): mixed
    {
        $result = $this->find()
            ->addJsonWhere('configuration', $path, true)
            ->first();
            
        return $result ? $result->configuration : null;
    }
}

Advanced Usage Patterns

Complex JSON Queries

// Multiple JSON conditions  
$complexQuery = $this->Members->find()
    ->addJsonWhere('additional_info', '$.preferences.newsletter', true)
    ->addJsonWhere('additional_info', '$.contact.phone_type', 'mobile')
    ->where(['status' => 'active']);

// Combining with regular WHERE clauses
$filteredResults = $this->Events->find()
    ->addJsonWhere('metadata', '$.capacity.maximum', 50)
    ->where([
        'start_date >=' => new DateTime(),
        'registration_open' => true
    ]);

Dynamic Path Building

public function searchByDynamicPath(string $category, string $subcategory, $value): Query
{
    $path = "$.{$category}.{$subcategory}";
    
    return $this->find()
        ->addJsonWhere('additional_info', $path, $value);
}

Sortable Behavior

The SortableBehavior provides comprehensive sortable list management with automatic position assignment, conflict resolution, and intuitive reordering methods.

Features

Database Schema

Tables using Sortable behavior require a position field and optional grouping fields:

CREATE TABLE recommendations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stack_rank INT NOT NULL DEFAULT 0,     -- Position field
    category_id INT NULL,                   -- Optional: grouping field
    status VARCHAR(50) NULL,                -- Optional: additional grouping
    -- other fields...
    
    INDEX idx_sortable (category_id, status, stack_rank),
    INDEX idx_position (stack_rank)
);

Configuration

// In Table initialize() method
public function initialize(array $config): void
{
    parent::initialize($config);
    
    // Basic configuration
    $this->addBehavior('Sortable');
    
    // Custom configuration
    $this->addBehavior('Sortable', [
        'field' => 'stack_rank',           // Position field name
        'group' => ['category_id', 'status'], // Grouping fields
        'start' => 1,                      // Starting position  
        'step' => 1,                       // Position increment
    ]);
}

Configuration Options

Option Description Default Example
field Position field name 'position' 'stack_rank', 'display_order'
group Grouping fields array [] ['category_id'], ['status', 'priority']
start Starting position value 1 0, 10
step Position increment 1 10, 100

Core Methods

Position Movement Methods

toTop()

Move entity to the first position in its group:

// Move recommendation to top of stack
$success = $this->Recommendations->toTop($recommendationId);

// With error handling
if ($this->Recommendations->toTop($itemId)) {
    $this->Flash->success('Moved to top successfully');
} else {
    $this->Flash->error('Failed to move item');
}
toBottom()

Move entity to the last position in its group:

// Move item to bottom of list
$success = $this->Recommendations->toBottom($recommendationId);

// Move problematic item to end
$this->MenuItems->toBottom($problematicItemId);
move()

Move entity to specific position (core method):

// Move to specific position
$this->Recommendations->move($itemId, 5);

// Create gap without moving item (for batch operations)  
$this->Recommendations->move($itemId, 5, false);

// Complex reordering
$newOrder = [15, 7, 23, 4]; // New sequence of IDs
foreach ($newOrder as $position => $id) {
    $this->Items->move($id, ($position + 1) * 10);
}
moveBefore() / moveAfter()

Relative positioning methods:

// Move item before another item
$this->Recommendations->moveBefore($sourceId, $targetId);

// Move item after another item  
$this->Recommendations->moveAfter($sourceId, $targetId);

// Drag and drop implementation
$success = $this->Items->moveBefore($draggedId, $dropTargetId);
echo json_encode(['success' => $success]);

Utility Methods

getStart()

Get the starting position value:

$startPosition = $this->getStart(); // Returns configured start value
getStep()

Get the position increment value:

$increment = $this->getStep(); // Returns configured step value
getNew()

Calculate position for new entity (at end of list):

// Get position for new item
$newPosition = $this->getNew();

// Get position within specific group
$groupConditions = ['category_id' => 5];
$newPosition = $this->getNew($groupConditions);
getLast()

Get current highest position in group:

$lastPosition = $this->getLast(); // Highest position across all records
$lastInGroup = $this->getLast(['category_id' => 3]); // Highest in group

Group-Based Sorting

When group fields are configured, each unique combination of group values maintains its own independent sorted list:

// Configuration for group-based sorting
$this->addBehavior('Sortable', [
    'field' => 'priority',
    'group' => ['category_id', 'status']
]);

// Each group maintains independent positions:
// category_id=1, status='active'   -> positions: 1, 2, 3, 4...
// category_id=1, status='pending'  -> positions: 1, 2, 3...  
// category_id=2, status='active'   -> positions: 1, 2...

KMP Use Cases

Award Recommendation Stack Ranking

// Awards Plugin - Recommendation prioritization
class RecommendationsTable extends Table
{
    public function initialize(array $config): void
    {
        $this->addBehavior('Sortable', [
            'field' => 'stack_rank',
        ]);
    }
    
    // Prioritize recommendation
    public function prioritizeRecommendation(int $id): bool
    {
        return $this->toTop($id);
    }
    
    // Reorder recommendations by priority
    public function reorderByIds(array $orderedIds): void
    {
        foreach ($orderedIds as $position => $id) {
            $this->move($id, $position + 1);
        }
    }
}

// Navigation menu item ordering
class MenuItemsTable extends Table
{
    public function initialize(array $config): void
    {
        $this->addBehavior('Sortable', [
            'field' => 'display_order',
            'group' => ['parent_id', 'category'], // Each menu level separate
            'start' => 10,
            'step' => 10, // Allow easy insertion
        ]);
    }
}

Document Management

// Document list ordering
class DocumentsTable extends Table
{
    public function initialize(array $config): void
    {
        $this->addBehavior('Sortable', [
            'field' => 'sort_order',
            'group' => ['folder_id', 'document_type'],
        ]);
    }
    
    public function organizeDocuments(int $folderId, array $documentIds): void
    {
        foreach ($documentIds as $index => $docId) {
            $this->move($docId, ($index + 1) * 100);
        }
    }
}

Advanced Usage Patterns

Batch Reordering Operations

public function reorderEntireCategory(int $categoryId, array $newOrder): bool
{
    $this->getConnection()->transactional(function () use ($categoryId, $newOrder) {
        foreach ($newOrder as $position => $entityId) {
            $this->move($entityId, ($position + 1) * $this->getStep());
        }
    });
    
    return true;
}

Drag and Drop API Implementation

// Controller method for drag-and-drop
public function reorder(): JsonResponse
{
    $draggedId = $this->request->getData('draggedId');
    $targetId = $this->request->getData('targetId');
    $position = $this->request->getData('position'); // 'before' or 'after'
    
    try {
        if ($position === 'before') {
            $success = $this->Table->moveBefore($draggedId, $targetId);
        } else {
            $success = $this->Table->moveAfter($draggedId, $targetId);
        }
        
        return $this->response->withType('application/json')
            ->withStringBody(json_encode(['success' => $success]));
            
    } catch (Exception $e) {
        return $this->response->withStatus(500)
            ->withType('application/json')  
            ->withStringBody(json_encode(['error' => $e->getMessage()]));
    }
}

Position Range Operations

// Move multiple items to sequential positions
public function moveItemsToRange(array $itemIds, int $startPosition): void
{
    foreach ($itemIds as $index => $itemId) {
        $this->move($itemId, $startPosition + ($index * $this->getStep()));
    }
}

// Swap positions of two items
public function swapPositions(int $itemId1, int $itemId2): bool
{
    $item1 = $this->get($itemId1, ['fields' => ['id', $this->_config['field']]]);
    $item2 = $this->get($itemId2, ['fields' => ['id', $this->_config['field']]]);
    
    $pos1 = $item1->{$this->_config['field']};
    $pos2 = $item2->{$this->_config['field']};
    
    return $this->move($itemId1, $pos2) && $this->move($itemId2, $pos1);
}

Implementation Examples

Complete Table Implementation

Here’s a complete example showing how to implement all three behaviors in a single table:

<?php
declare(strict_types=1);

namespace App\Model\Table;

use Cake\ORM\Table;
use Cake\Validation\Validator;

/**
 * Example table demonstrating all three behaviors
 */
class ProjectTasksTable extends Table
{
    public function initialize(array $config): void
    {
        parent::initialize($config);
        
        $this->setTable('project_tasks');
        $this->setPrimaryKey('id');
        
        // Temporal behavior for task scheduling
        $this->addBehavior('ActiveWindow');
        
        // JSON field for task metadata  
        $this->addBehavior('JsonField');
        
        // Sortable behavior for task prioritization
        $this->addBehavior('Sortable', [
            'field' => 'priority_rank',
            'group' => ['project_id', 'status'],
            'start' => 100,
            'step' => 100,
        ]);
        
        // Associations
        $this->belongsTo('Projects');
        $this->belongsTo('AssignedMembers', [
            'className' => 'Members',
            'foreignKey' => 'assigned_to',
        ]);
    }
    
    public function validationDefault(Validator $validator): Validator
    {
        $validator
            ->integer('id')
            ->allowEmptyString('id', null, 'create');
            
        $validator
            ->scalar('title')
            ->maxLength('title', 255)
            ->requirePresence('title', 'create')
            ->notEmptyString('title');
            
        $validator
            ->dateTime('start_on')
            ->requirePresence('start_on', 'create')
            ->notEmptyDateTime('start_on');
            
        $validator
            ->dateTime('expires_on')
            ->allowEmptyDateTime('expires_on');
            
        $validator
            ->integer('priority_rank')
            ->allowEmptyString('priority_rank');
            
        return $validator;
    }
    
    // Custom finder combining behaviors
    public function findCurrentTasksByProject(int $projectId): \Cake\ORM\Query
    {
        return $this->find('current') // ActiveWindow behavior
            ->where(['project_id' => $projectId])
            ->orderBy(['priority_rank' => 'ASC']) // Sortable order
            ->contain(['AssignedMembers']);
    }
    
    // JSON field queries for task metadata
    public function findTasksByRequirement(string $requirement): \Cake\ORM\Query  
    {
        return $this->find()
            ->addJsonWhere('metadata', '$.requirements.skills', $requirement);
    }
    
    public function findHighPriorityTasks(): \Cake\ORM\Query
    {
        return $this->find()
            ->addJsonWhere('metadata', '$.priority.level', 'high')
            ->find('current')
            ->orderBy(['priority_rank' => 'ASC']);
    }
    
    // Sortable operations  
    public function prioritizeTask(int $taskId): bool
    {
        return $this->toTop($taskId);
    }
    
    public function reorderProjectTasks(int $projectId, array $orderedIds): void
    {
        foreach ($orderedIds as $index => $taskId) {
            $this->move($taskId, ($index + 1) * 100);
        }
    }
}

Controller Integration

<?php
declare(strict_types=1);

namespace App\Controller;

/**  
 * Example controller using behavior-enhanced table
 */
class ProjectTasksController extends AppController
{
    // Display current tasks with JSON metadata
    public function currentTasks(int $projectId): void
    {
        $tasks = $this->ProjectTasks->findCurrentTasksByProject($projectId);
        
        $this->set(compact('tasks', 'projectId'));
    }
    
    // Search tasks by requirements
    public function searchBySkill(): void  
    {
        $skill = $this->request->getQuery('skill');
        
        if ($skill) {
            $tasks = $this->ProjectTasks->findTasksByRequirement($skill);
            $this->set(compact('tasks', 'skill'));
        }
    }
    
    // AJAX endpoint for drag-and-drop reordering
    public function reorder(): void
    {
        $this->request->allowMethod(['post']);
        
        $taskIds = $this->request->getData('taskIds');
        $projectId = $this->request->getData('projectId');
        
        try {
            $this->ProjectTasks->reorderProjectTasks($projectId, $taskIds);
            
            $this->set(['success' => true]);
        } catch (\Exception $e) {
            $this->response = $this->response->withStatus(500);
            $this->set(['success' => false, 'error' => $e->getMessage()]);
        }
        
        $this->viewBuilder()->setOption('serialize', ['success', 'error']);
    }
    
    // Prioritize task (move to top)
    public function prioritize(int $id): void
    {
        $this->request->allowMethod(['post']);
        
        if ($this->ProjectTasks->prioritizeTask($id)) {
            $this->Flash->success('Task prioritized successfully');
        } else {
            $this->Flash->error('Failed to prioritize task');
        }
        
        return $this->redirect($this->referer());
    }
}

Testing Behaviors

Unit Testing Approach

<?php
declare(strict_types=1);

namespace App\Test\TestCase\Model\Behavior;

use Cake\TestSuite\TestCase;
use Cake\ORM\TableRegistry;
use Cake\I18n\DateTime;

class ActiveWindowBehaviorTest extends TestCase
{
    protected $fixtures = [
        'app.Officers',
        'app.Members',
    ];
    
    public function setUp(): void
    {
        parent::setUp();
        $this->Officers = TableRegistry::getTableLocator()->get('Officers');
        $this->Officers->addBehavior('ActiveWindow');
    }
    
    public function testFindCurrent(): void
    {
        $currentDate = new DateTime('2024-01-15 12:00:00');
        
        $result = $this->Officers->find('current', effectiveDate: $currentDate);
        
        $this->assertInstanceOf('Cake\ORM\Query', $result);
        $this->assertNotEmpty($result->toArray());
    }
    
    public function testFindUpcoming(): void
    {
        $currentDate = new DateTime('2024-01-01 12:00:00');
        
        $upcoming = $this->Officers->find('upcoming', effectiveDate: $currentDate);
        $upcomingArray = $upcoming->toArray();
        
        $this->assertNotEmpty($upcomingArray);
        
        // Verify all results are actually upcoming
        foreach ($upcomingArray as $officer) {
            $this->assertTrue(
                $officer->start_on > $currentDate || 
                $officer->expires_on === null ||
                $officer->expires_on > $currentDate
            );
        }
    }
    
    public function testFindPrevious(): void
    {
        $currentDate = new DateTime('2024-12-31 23:59:59');
        
        $previous = $this->Officers->find('previous', effectiveDate: $currentDate);
        $previousArray = $previous->toArray();
        
        foreach ($previousArray as $officer) {
            $this->assertNotNull($officer->expires_on);
            $this->assertTrue($officer->expires_on < $currentDate);
        }
    }
}

class JsonFieldBehaviorTest extends TestCase
{
    protected $fixtures = [
        'app.Members',
    ];
    
    public function setUp(): void
    {
        parent::setUp();
        $this->Members = TableRegistry::getTableLocator()->get('Members');
        $this->Members->addBehavior('JsonField');
    }
    
    public function testAddJsonWhere(): void
    {
        $query = $this->Members->find()
            ->addJsonWhere('additional_info', '$.preferences.notifications', true);
            
        $sql = $query->sql();
        $this->assertStringContains('JSON_EXTRACT', $sql);
        $this->assertStringContains('$.preferences.notifications', $sql);
    }
    
    public function testJsonQueryExecution(): void
    {
        // Create test record with JSON data
        $member = $this->Members->newEntity([
            'sca_name' => 'Test Member',
            'additional_info' => json_encode([
                'preferences' => [
                    'notifications' => true,
                    'newsletter' => false,
                ],
                'contact' => [
                    'email' => 'test@example.com',
                ]
            ])
        ]);
        $this->Members->save($member);
        
        // Test JSON query
        $results = $this->Members->find()
            ->addJsonWhere('additional_info', '$.preferences.notifications', true)
            ->toArray();
            
        $this->assertCount(1, $results);
        $this->assertEquals('Test Member', $results[0]->sca_name);
    }
}

class SortableBehaviorTest extends TestCase  
{
    protected $fixtures = [
        'app.Recommendations',
    ];
    
    public function setUp(): void
    {
        parent::setUp();
        $this->Recommendations = TableRegistry::getTableLocator()->get('Awards.Recommendations');
        $this->Recommendations->addBehavior('Sortable', [
            'field' => 'stack_rank',
        ]);
    }
    
    public function testToTop(): void
    {
        // Get a recommendation that's not at the top
        $recommendation = $this->Recommendations->find()
            ->where(['stack_rank >' => 1])
            ->first();
        
        $this->assertNotNull($recommendation);
        
        $success = $this->Recommendations->toTop($recommendation->id);
        $this->assertTrue($success);
        
        // Verify it's now at position 1
        $updated = $this->Recommendations->get($recommendation->id);
        $this->assertEquals(1, $updated->stack_rank);
    }
    
    public function testMoveBefore(): void
    {
        $items = $this->Recommendations->find()->orderBy(['stack_rank' => 'ASC'])->limit(3)->toArray();
        
        $this->assertCount(3, $items);
        
        $success = $this->Recommendations->moveBefore($items[2]->id, $items[0]->id);
        $this->assertTrue($success);
        
        // Verify new order
        $reordered = $this->Recommendations->find()->orderBy(['stack_rank' => 'ASC'])->toArray();
        $this->assertEquals($items[2]->id, $reordered[0]->id);
    }
    
    public function testGetNew(): void
    {
        $newPosition = $this->Recommendations->getNew();
        
        $this->assertIsNumeric($newPosition);
        $this->assertGreaterThan(0, $newPosition);
    }
}

Integration Testing

<?php
declare(strict_types=1);

namespace App\Test\TestCase\Controller;

use Cake\TestSuite\IntegrationTestTrait;
use Cake\TestSuite\TestCase;

class ProjectTasksControllerTest extends TestCase
{
    use IntegrationTestTrait;
    
    protected $fixtures = [
        'app.ProjectTasks',
        'app.Projects',
        'app.Members',
    ];
    
    public function testReorderAction(): void
    {
        $taskIds = [3, 1, 2]; // New order
        $projectId = 1;
        
        $this->post('/project-tasks/reorder', [
            'taskIds' => $taskIds,
            'projectId' => $projectId,
        ]);
        
        $this->assertResponseOk();
        $this->assertResponseContains('"success":true');
        
        // Verify database changes
        $tasks = $this->getTableLocator()->get('ProjectTasks')
            ->find()
            ->where(['project_id' => $projectId])
            ->orderBy(['priority_rank' => 'ASC'])
            ->toArray();
            
        $actualOrder = array_column($tasks, 'id');
        $this->assertEquals($taskIds, $actualOrder);
    }
}

Performance Optimization

Database Indexing

ActiveWindow Behavior Indexes

-- Composite index for temporal queries
CREATE INDEX idx_active_window ON table_name (start_on, expires_on);

-- Individual field indexes for specific queries  
CREATE INDEX idx_start_on ON table_name (start_on);
CREATE INDEX idx_expires_on ON table_name (expires_on);

-- Group-aware temporal index
CREATE INDEX idx_group_temporal ON table_name (group_field, start_on, expires_on);

JsonField Behavior Optimization

-- MySQL: Virtual columns for frequently queried JSON paths
ALTER TABLE members 
ADD notification_pref BOOLEAN AS (JSON_EXTRACT(additional_info, '$.preferences.notifications')) STORED,
ADD INDEX idx_notification_pref (notification_pref);

-- PostgreSQL: GIN indexes for JSON fields
CREATE INDEX idx_additional_info_gin ON members USING GIN (additional_info);

-- Functional indexes for specific paths
CREATE INDEX idx_member_email ON members ((additional_info->>'contact'->>'email'));

Sortable Behavior Indexes

-- Basic position index
CREATE INDEX idx_position ON table_name (position);

-- Group-based sorting index (most important)
CREATE INDEX idx_group_position ON table_name (group_field1, group_field2, position);

-- Covering index for position operations
CREATE INDEX idx_sortable_ops ON table_name (group_field, position, id);

Query Optimization Tips

ActiveWindow Behavior

// Use specific effective dates to leverage indexes
$specificDate = new DateTime('2024-01-15');
$query = $this->find('current', effectiveDate: $specificDate);

// Add additional WHERE clauses early in the query
$query = $this->find('current')
    ->where(['status' => 'active']) // Filter first
    ->contain(['Related']); // Then join

// Avoid N+1 queries with proper containment
$query = $this->find('current')
    ->contain(['Members', 'Offices.Departments']);

JsonField Behavior

// Prefer virtual columns for frequently queried paths
// (Define in table schema, then query normally)
$query = $this->find()->where(['notification_pref' => true]);

// Limit JSON queries to necessary records
$query = $this->find()
    ->where(['status' => 'active']) // Filter first
    ->addJsonWhere('metadata', '$.type', 'premium'); // Then JSON filter

// Use result caching for expensive JSON queries
$results = Cache::remember('complex_json_query', function() {
    return $this->find()
        ->addJsonWhere('metadata', '$.complex.path', 'value')
        ->toArray();
});

Sortable Behavior

// Batch position updates in transactions
$this->getConnection()->transactional(function() use ($updates) {
    foreach ($updates as $id => $position) {
        $this->move($id, $position);
    }
});

// Use larger step values for frequently reordered lists
$this->addBehavior('Sortable', [
    'step' => 100, // Allows 99 items between any two positions
]);

// Optimize group queries with proper indexing
$this->find()->where(['group_field' => $value])->orderBy(['position' => 'ASC']);

Caching Strategies

// Cache temporal query results
public function getCachedCurrentItems(int $groupId): array
{
    $cacheKey = "current_items_{$groupId}_" . date('Y-m-d-H');
    
    return Cache::remember($cacheKey, function() use ($groupId) {
        return $this->find('current')
            ->where(['group_id' => $groupId])
            ->toArray();
    }, '1 hour');
}

// Cache JSON query results with dependency tags
public function getCachedJsonResults(string $path, $value): array
{
    $cacheKey = "json_query_" . md5($path . serialize($value));
    
    return Cache::remember($cacheKey, function() use ($path, $value) {
        return $this->find()
            ->addJsonWhere('metadata', $path, $value)
            ->toArray();
    }, ['tags' => ['json_queries', 'metadata_queries']]);
}
← Back to Architecture ← Back to Table of Contents