Virtual Columns

Virtual columns let your Eloquent models register SQL-backed computed columns that appear alongside regular database columns in the report builder. They are ideal for resolving polymorphic relationship names, computing derived values, or exposing any SQL expression as a first-class reportable field.

When to Use Virtual Columns

Use virtual columns when you need to expose data that:

Comes from a polymorphic (MorphTo) relationship where the target model varies per row
Requires a SQL expression to compute (CASE WHEN, subqueries, concatenations)
Isn't a direct database column but should be sortable in reports
Is already computed via raw SQL in your Filament resources and you want to reuse it in custom reports

Note: If you need simple math between two columns (add, subtract, multiply, divide), use Calculated Columns instead — they are user-definable through the report builder UI without any code changes.

Implementing Virtual Columns

Step 1: Implement the Interface

Add the HasDataLensVirtualColumns interface to your model:

use Padmission\DataLens\Contracts\HasDataLensVirtualColumns;
use Padmission\DataLens\Data\VirtualColumnDefinition;
use Padmission\DataLens\Enums\ColumnType;

class Transaction extends Model implements HasDataLensVirtualColumns
{
    public static function dataLensVirtualColumns(): array
    {
        return [
            'payee_name' => VirtualColumnDefinition::make('payee_name')
                ->label('Payee Name')
                ->type(ColumnType::TEXT)
                ->expression(fn (string $table): string => "CASE
                    WHEN {$table}.payeeable_type = 'App\\\\Models\\\\Vendor'
                        THEN (SELECT name FROM vendors WHERE vendors.id = {$table}.payeeable_id LIMIT 1)
                    WHEN {$table}.payeeable_type = 'App\\\\Models\\\\PropertyOwner'
                        THEN (SELECT name FROM property_owners WHERE property_owners.id = {$table}.payeeable_id LIMIT 1)
                    ELSE NULL
                END")
                ->sortable(),
        ];
    }
}

Step 2: That's It

No configuration changes, no migrations, no registration. Data Lens automatically discovers virtual columns on any model that implements the interface. The columns appear in the field picker when users create or edit a report using that model.

VirtualColumnDefinition API

Each virtual column is defined using a fluent builder:

VirtualColumnDefinition::make('field_name')  // Required: unique key for this column
    ->label('Display Label')                  // Label shown in the report builder
    ->type(ColumnType::TEXT)                  // Column type for formatting
    ->expression(fn (string $table) => ...)   // SQL expression (receives table name)
    ->sortable()                              // Enable sorting (default: true)
    ->searchable()                            // Enable searching (default: false)

Available Column Types

The type() method accepts any ColumnType enum value:

ColumnType::TEXT — Plain text (default)
ColumnType::MONEY — Currency formatting
ColumnType::NUMBER — Numeric formatting
ColumnType::DATE — Date formatting
ColumnType::DATE_TIME — Date and time formatting
ColumnType::BOOLEAN — Yes/No icon display
ColumnType::BADGE — Colored badge display

The type is automatically detected by the report builder when users select the field — they don't need to set it manually.

The Expression Closure

The expression() method receives a closure that gets the table name as its argument and must return a valid SQL string:

->expression(function (string $table): string {
    return "CONCAT({$table}.first_name, ' ', {$table}.last_name)";
})

The table name is injected at query time, so your SQL works correctly even when the table name varies (e.g., in subqueries or joins).

Important: The expression must return valid SQL for your database engine. Data Lens wraps it in parentheses and aliases it automatically — you don't need to add AS column_name.

Common Patterns

Resolving Polymorphic Relationship Names

The most common use case — resolve a MorphTo relationship to a display name:

'payee_name' => VirtualColumnDefinition::make('payee_name')
    ->label('Payee Name')
    ->type(ColumnType::TEXT)
    ->expression(fn (string $table): string => "CASE
        WHEN {$table}.payeeable_type = 'App\\\\Models\\\\Vendor'
            THEN (SELECT name FROM vendors WHERE vendors.id = {$table}.payeeable_id LIMIT 1)
        WHEN {$table}.payeeable_type = 'App\\\\Models\\\\PropertyOwner'
            THEN (SELECT name FROM property_owners WHERE property_owners.id = {$table}.payeeable_id LIMIT 1)
        WHEN {$table}.payeeable_type = 'App\\\\Models\\\\Agency'
            THEN (SELECT name FROM agencies WHERE agencies.id = {$table}.payeeable_id LIMIT 1)
        ELSE NULL
    END")
    ->sortable(),

Tip: If your app uses a morph map, use the short morph aliases instead of fully-qualified class names — this avoids backslash escaping issues and is more portable across database engines.

Computed Money Fields

Combine multiple columns into a computed financial value:

'net_amount' => VirtualColumnDefinition::make('net_amount')
    ->label('Net Amount')
    ->type(ColumnType::MONEY)
    ->expression(fn (string $table): string => "{$table}.amount - COALESCE({$table}.fee, 0)")
    ->sortable(),

Conditional Status Labels

Map numeric or enum values to human-readable labels:

'status_label' => VirtualColumnDefinition::make('status_label')
    ->label('Status')
    ->type(ColumnType::TEXT)
    ->expression(fn (string $table): string => "CASE {$table}.status
        WHEN 'pending' THEN 'Pending Review'
        WHEN 'approved' THEN 'Approved'
        WHEN 'rejected' THEN 'Rejected'
        ELSE {$table}.status
    END")
    ->sortable(),

Cross-Table Lookups

Pull a value from a related table without defining a formal relationship:

'project_name' => VirtualColumnDefinition::make('project_name')
    ->label('Project Name')
    ->type(ColumnType::TEXT)
    ->expression(fn (string $table): string =>
        "(SELECT name FROM projects WHERE projects.id = {$table}.project_id LIMIT 1)")
    ->sortable(),

How It Works

When a report includes a virtual column, Data Lens:

Discovery — Checks if the model implements HasDataLensVirtualColumns and merges virtual columns into the field list with a virtual:: prefix
Type Detection — Reads the column type from the VirtualColumnDefinition to auto-set formatting
Query Building — Adds selectRaw("(expression) as virtual_field_name") to the report query
Sorting — Uses ORDER BY virtual_field_name on the computed alias
Export — Virtual columns are included in CSV/XLSX exports like any other column

Virtual columns are not eager-loaded — they execute as part of the main SELECT statement, so they don't cause N+1 queries.

Limitations

Searching is disabled by default for virtual columns. SQL aliases cannot be used in WHERE clauses without wrapping in a subquery, which could impact performance.
Filtering through the visual filter builder is not supported for virtual columns. Use regular database columns for filter conditions.
Database-specific SQL — The expression must be valid for your database engine (MySQL, PostgreSQL, etc.). Data Lens does not abstract SQL differences.
Performance — Complex subqueries in CASE WHEN expressions execute per-row. For large datasets, consider adding indexes on the foreign key columns used in subqueries.

Documents