Record Exclusion (noSelectFields)

NexaModel menyediakan method untuk mengecualikan record/baris tertentu dari hasil query berdasarkan kondisi field, memberikan kontrol yang lebih granular dalam filtering data.

NexaModel · Storage() · NexaDb

Alur

$this->Storage('news')->where()->first();

NexaModel menyediakan method untuk mengecualikan record/baris tertentu dari hasil query berdasarkan kondisi field, memberikan kontrol yang lebih granular dalam filtering data.

Basic Record Exclusion

`noSelectFields()` Method

PHP

// Tidak menampilkan record dengan account_name = 'Biaya Gaji'
$accounts = $this->Storage('accounts')
    ->noSelectFields(['account_name' => 'Biaya Gaji'])
    ->get();

// Multiple conditions (AND logic)
$accounts = $this->Storage('accounts')
    ->noSelectFields([
        'account_name' => 'Biaya Gaji',
        'status' => 'inactive'
    ])
    ->get();

// Menggunakan array values (NOT IN)
$accounts = $this->Storage('accounts')
    ->noSelectFields([
        'account_name' => ['Biaya Gaji', 'Biaya Internet', 'Biaya Air']
    ])
    ->get();

// ❌ SALAH - Key yang sama akan ditimpa
$users = $this->Storage('users')
    ->noSelectFields([
        'id' => 10,
        'id' => 40  // Ini akan menimpa nilai 10
    ])
    ->get();

// ✅ BENAR - Gunakan array untuk multiple values
$users = $this->Storage('users')
    ->noSelectFields([
        'id' => [10, 40]  // NOT IN (10, 40)
    ])
    ->get();

// ✅ BENAR - Chain multiple calls
$users = $this->Storage('users')
    ->noSelectFields(['id' => 10])
    ->noSelectFields(['id' => 40])
    ->get();

// Null value handling
$accounts = $this->Storage('accounts')
    ->noSelectFields(['deleted_at' => null]) // WHERE deleted_at IS NOT NULL
    ->get();

Method Aliases

PHP

// Semua method ini berfungsi sama dengan noSelectFields()

// Menyembunyikan record
$accounts = $this->Storage('accounts')
    ->hideRecords(['account_name' => 'Biaya Gaji'])
    ->get();

// Mengecualikan record
$accounts = $this->Storage('accounts')
    ->excludeRecords(['status' => 'banned'])
    ->get();

// Melewati record
$accounts = $this->Storage('accounts')
    ->skipRecords(['account_name' => 'Biaya Gaji'])
    ->get();

// Tanpa record tertentu
$accounts = $this->Storage('accounts')
    ->withoutRecords(['status' => 'deleted'])
    ->get();

// Filter keluar record
$accounts = $this->Storage('accounts')
    ->filterOut(['account_name' => 'Biaya Gaji'])
    ->get();

Shortcut Methods for Common Scenarios

Status-based Filtering

PHP

// Hanya record aktif (is_active = 1)
$users = $this->Storage('users')
    ->onlyActive()
    ->get();

// Custom status field
$products = $this->Storage('products')
    ->onlyActive('published')
    ->get();

// Hanya record tidak aktif
$users = $this->Storage('users')
    ->onlyInactive()
    ->get();

Soft Delete Handling

PHP

// Tidak termasuk yang dihapus (soft delete)
$posts = $this->Storage('posts')
    ->notDeleted()
    ->get();

// Custom deleted field
$documents = $this->Storage('documents')
    ->notDeleted('archived_at')
    ->get();

// Hanya yang dihapus
$deletedPosts = $this->Storage('posts')
    ->onlyDeleted()
    ->get();

Advanced Filtering Combinations

Complex Conditions

PHP

// Kombinasi multiple exclusions
$transactions = $this->Storage('transactions')
    ->noSelectFields([
        'status' => ['cancelled', 'failed'],
        'amount' => 0,
        'type' => 'test'
    ])
    ->onlyActive()
    ->notDeleted()
    ->get();

Chaining with Regular Where Clauses

PHP

// Kombinasi noSelectFields dengan where biasa
$orders = $this->Storage('orders')
    ->noSelectFields(['status' => 'cancelled'])  // Exclude cancelled orders
    ->where('created_at', '>=', '2024-01-01')    // From this year
    ->where('total_amount', '>', 0)              // With amount > 0
    ->orderBy('created_at', 'DESC')
    ->get();

Real World Examples

Financial Dashboard (Hide Sensitive Accounts)

PHP

public function getAccountsForDashboard()
{
    $sensitiveAccounts = ['Biaya Gaji', 'Biaya Rahasia', 'Dana Darurat'];

    return $this->Storage('accounts')
        ->noSelectFields(['account_name' => $sensitiveAccounts])
        ->onlyActive()
        ->orderBy('budget', 'DESC')
        ->get();
}

Public Blog Posts (Hide Drafts and Private)

PHP

public function getPublicPosts()
{
    return $this->Storage('posts')
        ->noSelectFields([
            'status' => ['draft', 'private'],
            'published' => 0
        ])
        ->notDeleted()
        ->orderBy('published_at', 'DESC')
        ->get();
}

User Management (Hide System and Banned Users)

PHP

public function getRegularUsers()
{
    return $this->Storage('users')
        ->noSelectFields([
            'role' => ['system', 'bot'],
            'status' => ['banned', 'suspended']
        ])
        ->onlyActive()
        ->noSensitive()
        ->orderBy('name')
        ->get();
}

Product Catalog (Hide Out of Stock and Discontinued)

PHP

public function getAvailableProducts()
{
    return $this->Storage('products')
        ->noSelectFields([
            'status' => ['discontinued', 'out_of_stock'],
            'stock_quantity' => 0,
            'is_hidden' => 1
        ])
        ->onlyActive()
        ->orderBy('name')
        ->get();
}

SQL Output Examples

PHP

// noSelectFields example
$this->Storage('accounts')
    ->noSelectFields(['account_name' => 'Biaya Gaji'])
    ->toSql();
// Result: "SELECT * FROM accounts WHERE account_name != ?"

// Multiple conditions
$this->Storage('accounts')
    ->noSelectFields([
        'account_name' => 'Biaya Gaji',
        'status' => 'inactive'
    ])
    ->toSql();
// Result: "SELECT * FROM accounts WHERE account_name != ? AND status != ?"

// Array values (NOT IN)
$this->Storage('accounts')
    ->noSelectFields(['account_name' => ['Biaya Gaji', 'Biaya Internet']])
    ->toSql();
// Result: "SELECT * FROM accounts WHERE account_name NOT IN (?, ?)"

// ✅ BENAR - Multiple IDs menggunakan array
$this->Storage('users')
    ->noSelectFields(['id' => [10, 40, 50]])
    ->toSql();
// Result: "SELECT * FROM users WHERE id NOT IN (?, ?, ?)"

// ✅ BENAR - Chain multiple calls
$this->Storage('users')
    ->noSelectFields(['id' => 10])
    ->noSelectFields(['status' => 'banned'])
    ->toSql();
// Result: "SELECT * FROM users WHERE id != ? AND status != ?"

// Null handling
$this->Storage('accounts')
    ->noSelectFields(['deleted_at' => null])
    ->toSql();
// Result: "SELECT * FROM accounts WHERE deleted_at IS NOT NULL"

// ❌ PERHATIAN - Key yang sama tidak bisa digunakan berulang
$this->Storage('users')
    ->noSelectFields([
        'id' => 10,
        'id' => 40  // Nilai 10 akan ditimpa oleh 40
    ])
    ->toSql();
// Result: "SELECT * FROM users WHERE id != ?" (hanya mengecualikan id = 40)

Combination Examples

Field Exclusion + Record Exclusion

PHP

// Kombinasi noSelect (exclude columns) dan noSelectFields (exclude records)
$safeAccounts = $this->Storage('accounts')
    ->noSensitive()                                    // Hide sensitive columns
    ->noSelectFields(['account_name' => 'Biaya Gaji']) // Hide specific records
    ->onlyActive()                                     // Only active records
    ->orderBy('account_name')
    ->get();

Complex Business Logic

PHP

public function getFinancialReport($excludeSalary = true, $includeInactive = false)
{
    $query = $this->Storage('accounts')
        ->noTimestamps(); // Hide timestamp columns

    if ($excludeSalary) {
        $query->noSelectFields([
            'account_name' => ['Biaya Gaji', 'Biaya Operasional', 'Tunjangan']
        ]);
    }

    if (!$includeInactive) {
        $query->onlyActive();
    }

    return $query->orderBy('budget', 'DESC')->get();
}

API Response Filtering

PHP

public function getFilteredAPIResponse($filters = [])
{
    $query = $this->Storage('users')
        ->noSensitive()  // Hide sensitive columns
        ->notDeleted();  // Hide deleted records

    // Dynamic field exclusions based on user permissions
    if (isset($filters['hide_records'])) {
        $query->noSelectFields($filters['hide_records']);
    }

    // Dynamic column exclusions
    if (isset($filters['hide_columns'])) {
        $query->noSelect($filters['hide_columns']);
    }

    return $query->paginate($filters['page'] ?? 1, $filters['limit'] ?? 20);
}

// Usage:
$response = $this->getFilteredAPIResponse([
    'hide_records' => ['status' => ['banned', 'inactive']],
    'hide_columns' => ['email', 'phone'],
    'page' => 1,
    'limit' => 50
]);

Performance Considerations

PHP

// Good: Index pada kolom yang sering digunakan untuk exclusion
// CREATE INDEX idx_accounts_name ON accounts(account_name);
// CREATE INDEX idx_users_status ON users(status);

// Efficient exclusion dengan indexed columns
$accounts = $this->Storage('accounts')
    ->noSelectFields(['status' => 'inactive'])  // Uses index
    ->orderBy('created_at', 'DESC')
    ->get();

// Combine with other optimizations
$users = $this->Storage('users')
    ->selectMinimal()                           // Less data transfer
    ->noSelectFields(['role' => 'system'])      // Indexed exclusion
    ->onlyActive()                             // Indexed filtering
    ->limit(100)                               // Limit results
    ->get();

Lanjut

Topik sebelumnya: Field Exclusion (noSelect). Topik berikutnya: Operasi CRUD. Lihat juga Models, Events, Helper & template, daftar dokumentasi.

Nxdom Framework