Selective Column Loading (Partial Entities)

• Problem
• Solution
• Backend Usage
  • Automatic Field Processing (Generic Controllers)
  • Custom List Actions
  • How It Works
  • Example Backend Test
• Frontend Usage
  • Store Configuration with Partial Columns
  • Editing Partial Records (Safe Pattern)
  • Protection Against Saving Partial Records
  • Loading Single Records (Always Full)
• Complete Example
  • Beratungsfall Grid with Partial Loading
• Performance Impact
  • Beratungsfall Example (100 records)
• Important Notes
  • Backward Compatibility
  • System Columns Are Always Included
  • Computed Fields
  • When to Use Selective Loading

Problem

BISO entities use read/write table separation with database views that JOIN multiple tables, resulting in entities with ~100 properties (e.g., Beratungsfall). The backend always loaded ALL view columns via SELECT *, causing:

• Slow list queries due to complex SQL views
• Large network payloads (unnecessary data transfer)
• No way for frontend to request only needed fields

In list views (grids), only 10-15 fields are typically needed for display, but the backend would load all 100 fields for each record.

Solution

The Selective Column Loading feature allows the frontend to specify which entity fields it needs. The backend returns partial entities with only the requested columns, reducing query complexity and network payload.

Key Components:

• Backend: BISOEntityBuilder::applyColumnSelection() handles field filtering with automatic system column inclusion
• Frontend: KP.base.Store supports partialColumns config to request specific fields
• Frontend: KP.base.Model prevents saving partial records to avoid data loss

Backend Usage

Automatic Field Processing (Generic Controllers)

Controllers using EntityRequestTrait::entityList() automatically support field selection:

// No changes needed - already supports 'fields' parameter
class PersonController extends KPController {
    use EntityRequestTrait;

    public function listAction() {
        return $this->entityList(Person::class);
    }
}

Custom List Actions

For controllers with custom list logic, call processFields():

// File: webroot/backend/controllers/BeratungsfallController.php
public function listAction() {
    $builder = $this->createEntityBuilder(Beratungsfall::class);

    // Add this line to support selective columns
    $this->processFields($builder, $this->params);

    // ... rest of your custom logic
    return $builder->executeSelect()->fetchAll();
}

How It Works

1. Frontend sends fields parameter:

   POST /backend/Beratungsfall/list
   fields: ["kunde_name", "kunde_vorname", "berater_name"]
   

2. Backend processes fields:

   • EntityRequestTrait::processFields() extracts and decodes the fields parameter
   • BISOEntityBuilder::applyColumnSelection() automatically adds:
     • Entity ID field (from #[IdProperty] attribute)
     • Access control columns: user_id, group_id, group_mod, world_mod
     • Audit columns: creation_date, modification_date
   • Sanitizes field names to prevent SQL injection
   • Calls $builder->columns($fields) to generate SELECT id, kunde_name, kunde_vorname, ...

3. Backend returns partial entities:

   • Only requested columns are loaded from database
   • Entities marked as partial via $entity->setPartial()
   • Reduced SQL complexity (fewer JOINs executed)

Example Backend Test

// File: backend-test/specs/logic/BISOEntityBuilderTest.php
public function testApplyColumnSelectionAddsSystemColumns() {
    $eb = $this->entityBuilder(Beratungsfall::class);
    $eb->applyColumnSelection(['kunde_name']);

    $sql = $eb->getQuery()->getSql();

    // Verify ID and system columns are included
    $this->assertStringContainsString('id', $sql);
    $this->assertStringContainsString('user_id', $sql);
    $this->assertStringContainsString('creation_date', $sql);
    $this->assertStringContainsString('kunde_name', $sql);
}

Frontend Usage

Store Configuration with Partial Columns

// File: webroot/public/app/src/beratungsfall/List.js
Ext.define('KP.beratungsfall.List', {
    extend: 'Ext.grid.Panel',

    initComponent() {
        this.store = Ext.create('KP.beratungsfall.Store', {
            // Specify only the fields needed for grid display
            partialColumns: [
                'kunde_name',
                'kunde_vorname',
                'kunde_name_vorname',  // computed field
                'berater_name',
                'berater_vorname',
                'creation_date',
                'abschluss_am',
                'regionalstelle_kurz'
            ]
        });

        this.callParent();
    }
});

What happens:

• Store sends fields parameter to backend on every load/filter
• Backend returns only specified columns (+ system columns)
• Records marked as isPartial = true
• Network payload reduced by ~80% (from 50KB to 10KB per 100 records)

Editing Partial Records (Safe Pattern)

Partial records cannot be saved directly to prevent data loss. Reload the full record before editing:

// File: webroot/public/app/src/beratungsfall/ListController.js
editRecord(record) {
    if (record.isPartial) {
        // Show loading indicator
        this.getView().setLoading(AG('LADEN'));

        // Reload full record
        record.reloadFull({
            success: (fullRecord) => {
                this.getView().setLoading(false);
                this.openFormWithRecord(fullRecord);
            },
            failure: (record, operation) => {
                this.getView().setLoading(false);
                Ext.Msg.alert('Error', 'Failed to load full record');
            }
        });
    } else {
        this.openFormWithRecord(record);
    }
}

openFormWithRecord(record) {
    // record.isPartial === false
    // Safe to edit and save
    const form = Ext.create('KP.beratungsfall.Form', { record: record });
    form.show();
}

Protection Against Saving Partial Records

// Automatic protection in KP.base.Model
save() {
    if (this.isPartial) {
        throw new Error(
            'Cannot save partial record. Reload the full record first.'
        );
    }
    return this.callParent(arguments);
}

Loading Single Records (Always Full)

// Model.load() always loads all fields (not partial)
KP.beratungsfall.Model.load(123, {
    success: (record) => {
        // record.isPartial === false
        // All fields loaded, safe to edit and save
    }
});

Complete Example

Beratungsfall Grid with Partial Loading

1. Store Configuration

// File: webroot/public/app/src/beratungsfall/List.js
this.store = Ext.create('KP.beratungsfall.Store', {
    partialColumns: [
        'kunde_name',
        'kunde_vorname',
        'kunde_name_vorname',
        'berater_name',
        'berater_vorname',
        'berater_name_kurz',
        'creation_date',
        'abschluss_am',
        'regionalstelle_kurz'
    ]
});

2. Backend Processing

// File: webroot/backend/controllers/BeratungsfallController.php
public function listAction() {
    $builder = $this->createEntityBuilder(Beratungsfall::class);

    // Enable selective column loading
    $this->processFields($builder, $this->params);

    // Apply custom filters
    if (!empty($this->params['homeFilter'])) {
        $builder->where('user_id = :userId', [':userId' => $this->getUserId()]);
    }

    return $builder->executeSelect()->fetchAll();
}

3. Edit Workflow

// File: webroot/public/app/src/beratungsfall/ListController.js
onItemDblClick(grid, record) {
    this.editRecord(record);
}

editRecord(record) {
    if (record.isPartial) {
        this.getView().setLoading(AG('LADEN'));

        record.reloadFull({
            success: (fullRecord) => {
                this.getView().setLoading(false);
                this.openFormWithRecord(fullRecord);
            },
            failure: () => {
                this.getView().setLoading(false);
                Ext.Msg.alert('Error', AG('ERROR_LOADING_RECORD'));
            }
        });
    } else {
        this.openFormWithRecord(record);
    }
}

openFormWithRecord(record) {
    Ext.globalEvents.fireEvent(
        KP.GlobalEventsController.getShowBeratungsfall(),
        record.getId()
    );
}

Performance Impact

Beratungsfall Example (100 records)

Before (full load):

• Columns: ~100
• View JOINs: 8-10 tables
• Query time: 200-500ms
• Network payload: ~50KB

After (41 partial columns):

• Columns: 41 (+ system columns)
• View JOINs: 3-4 tables
• Query time: 50-150ms (60-70% faster)
• Network payload: ~10KB (80% reduction)

Important Notes

Backward Compatibility

Stores without partialColumns config work unchanged:

// Full load (all columns) - backward compatible
Ext.create('KP.person.Store', {
    // No partialColumns - loads all fields as before
}).load();

System Columns Are Always Included

You don't need to specify these - the backend adds them automatically:

• Entity ID field
• Access control: user_id, group_id, group_mod, world_mod
• Audit: creation_date, modification_date

Computed Fields

If your view has computed fields (e.g., kunde_name_vorname computed from kunde_name + kunde_vorname), include the computed field name in partialColumns:

partialColumns: [
    'kunde_name',          // Base field
    'kunde_vorname',       // Base field
    'kunde_name_vorname'   // Computed field (requires both base fields)
]

When to Use Selective Loading

Use for:

• List views (grids) with many records
• Entities with 50+ properties
• Views that JOIN many tables
• High-traffic pages

Don't use for:

• Forms (need full data for editing)
• Single record loads via Model.load()
• Small entities (<20 fields)