Filament•10mo ago

Global Search Table Column

I want to search table columns globally. and my data was inserted in the database-encrypted form. For example, I want to search for customer email, and it is in encrypted form. So I want to apply a query for the search, like...

 $query->whereEncryptedLike('customer_email', $search)

 $query->whereEncryptedLike('customer_email', $search)

6 Replies

Dennis Koch•10mo ago

I don't think that's possible with Laravel defautl encryption. You would need something like Ciphersweet that offers searchable encryption: https://github.com/paragonie/ciphersweet?tab=readme-ov-file

GitHub

GitHub - paragonie/ciphersweet: Fast, searchable field-level encryp...

Fast, searchable field-level encryption for PHP projects - paragonie/ciphersweet

SourabhOP•10mo ago

I have used database-based encryption and decryption instead of code-based. So that I can able to apply encryption and decryption method on model query https://github.com/TapanDerasari/laravel-mysql-encrypt For example I have applied encryption and decryption method while table search and sorting .

   public function scopeFilter($query, array $filters)
    {
        $query->when($filters['search'] ?? null, function ($query, $search) {
            $query->whereEncryptedLike('company_name', $search)
                ->orWhereEncryptedLike('name', $search)->orWhere('email', 'LIKE', "%{$search}%");
        });
    }

    public function scopeSort($query, array $filters)
    {
        if (in_array($filters['sort'], $this->encryptable)) {
            $query->when($filters ?? null, function ($query, $filters) {
                $query->orderByEncryptedSort($filters['sort'], $filters['direction']);
            });
        } else {
            $query->orderBy($filters['sort'], $filters['direction']);
        }
    }

   public function scopeFilter($query, array $filters)
    {
        $query->when($filters['search'] ?? null, function ($query, $search) {
            $query->whereEncryptedLike('company_name', $search)
                ->orWhereEncryptedLike('name', $search)->orWhere('email', 'LIKE', "%{$search}%");
        });
    }

    public function scopeSort($query, array $filters)
    {
        if (in_array($filters['sort'], $this->encryptable)) {
            $query->when($filters ?? null, function ($query, $filters) {
                $query->orderByEncryptedSort($filters['sort'], $filters['direction']);
            });
        } else {
            $query->orderBy($filters['sort'], $filters['direction']);
        }
    }

So is there way , i can override global search for the model ?

GitHub

GitHub - TapanDerasari/laravel-mysql-encrypt: Laravel 10.x Database...

Laravel 10.x Database encryption mysql side. Contribute to TapanDerasari/laravel-mysql-encrypt development by creating an account on GitHub.

Dennis Koch•10mo ago

Yes. Global search is defined on the resource. Search for a method that includes global search and query

SourabhOP•10mo ago

Thank you for your prompt reply. I have found the method and updated the code in the main resource file .

<?php
 protected static function applyGlobalSearchAttributeConstraint(Builder $query, string $search, array $searchAttributes, bool &$isFirst): Builder
    {
        $query->when($search ?? null, function ($query, $search) {
            $query
                ->whereEncryptedLike('company_name', $search);
        });

        return $query;
    }

<?php
 protected static function applyGlobalSearchAttributeConstraint(Builder $query, string $search, array $searchAttributes, bool &$isFirst): Builder
    {
        $query->when($search ?? null, function ($query, $search) {
            $query
                ->whereEncryptedLike('company_name', $search);
        });

        return $query;
    }

Dwayne•9mo ago

Where did you place this code?

SourabhOP•8mo ago

In the main resource file .

Gaming

Programming

Global Search Table Column