How to search case insensitive with addQuickSearch in a CRUD?

#1

By default in a CRUD, addQuickSearch is Case Sensitive. I would like to make a search case insensitive. Is it possible?
I reduced my code as much as possible to keep it complete and working:

<?php
include_once ('../include/config.php');
require '../vendor/autoload.php';

class SYSPC_MODEL  extends \atk4\data\Model {
    public $table = 'SYSPC_MODEL';
    function init() {
        parent::init();
        $this->addFields([  ['MODEL_NAME',     'caption'=>'Nom du modele'],
                            ['MODEL_BASE_RPM', 'caption'=>'Rpm de base']
                        ]);
    }
}     // End of class SYSPC_MODEL

class DHCP_PC  extends \atk4\data\Model {
    public $table = 'DHCP_PC';
    function init() {
        parent::init();
        $this->addFields([  ['PCNAME',      'required'=>true, 'caption'=>'Nom du pc'],
                            ['IP',          'required'=>true, 'caption'=>'IP du pc']
                        ]);
        $this->setOrder('PCNAME','asc');
        $this->hasOne('FK_MODEL_id',['required'=>true,new SYSPC_MODEL(),'ui'=>['visible'=>false]])->addField('MODEL_NAME','MODEL_NAME');
    }
}      // End of class DHCP_PC

class PcForm extends \atk4\ui\Form {
    function setModel($m, $fields = null) {
        parent::setModel($m, false);
        $gr = $this->addGroup('PC name');
        $gr->addField('PCNAME',['required'=>true,'caption'=>'Nom du pc']);
        $gr = $this->addGroup('IP');
        $gr->addField('IP');
        return $this->model;
    }
}     // End of class PcForm

$app = new \atk4\ui\App();
$app->initLayout($app->stickyGET('layout') ?: 'Admin');
$app->db = new \atk4\data\Persistence_SQL(
    "pgsql:host=".$GLOBALS['dbhost'].";dbname=".$GLOBALS['dbname'],
    $GLOBALS['dbuser'],
    $GLOBALS['dbpass']
);
$g = $app->add(['CRUD', 'formDefault'=>new PcForm()]);
$g->setIpp([10, 25, 50, 100]);
$g->setModel(new DHCP_PC($app->db),['PCNAME','IP','MODEL_NAME']);
$g->addQuickSearch(['PCNAME','IP','MODEL_NAME']);
?>
#2

Hello there,

My CRUD QuickSearch is case insensitive without any special settings. This is just a guess but your issue might be persistence-related. I’m not an expert so someone else may know better.

#3

Yes it seems dependent on the database. I actually modified the source files for addQuickSearch on my instance so that it would convert the search words and the database fields to lowercase as a part of the SQL (Oracle).

#4

it uses “like” which in MySQL is case insensitive. Probably there are some options you can set.

#5

I tried the PgSql regular expression operator ~* ( ~* is case insensitive):

            $cond[] = [$field, "~*", "'".$Q."'"];   // ~* => regular expression operator (case insensitive) in PgSql, but not accepted by AgileToolkit, why ?

without success. I created a new function where search item is converted in uppercase, but table field value is not converted in Uppercase. I don’t know how to convert table field value in Uppercase.

// addQuickSearchInUpper function has to be placed just after addQuickSearch function in vendor/atk4/ui/src/Grid.php
public function addQuickSearchInUpper($fields = []) {
    if (!$fields) {
        $fields = [$this->model->title_field];
    }

    if (!$this->menu) {
        throw new Exception(['Unable to add QuickSearch without Menu']);
    }

    $view = $this->menu
        ->addMenuRight()->addItem()->setElement('div')
        ->add('View');

    $this->quickSearch = $view->add(['jsSearch', 'reload' => $this->container]);

    if ($q = $this->stickyGet('_q')) {
        $cond = [];
        $Q = strtoupper($q);
        foreach ($fields as $field) {
            $cond[] = [$field, 'like', '%'.$Q.'%'];                     // Works fine: search item are converted in uppercase, but table field is not converted in Uppercase.
            // $cond[] = [$field, 'like', '%'.$q.'%'];                  // Original line: works perfectly but case sensitive.
            // $cond[] = [$field, "~*", "'".$Q."'"];                    // ~* => regular expression operator (case insensitive) in PgSql, but not accepted by AgileToolkit, why ?
            // $cond[] = [$field, "~*", "'.*".$Q.".*'"];                // ~* => regular expression operator (case insensitive) in PgSql, but not accepted by AgileToolkit, why ?
            // $cond[] = ['upper(\"'.$field.'\")', 'like', '%'.$Q.'%']; // not accepted by AgileToolkit
        }
        $this->model->addCondition($cond);
    }
}
#6

Blockquote
gcooka
Yes it seems dependent on the database. I actually modified the source files for addQuickSearch on my instance so that it would convert the search words and the database fields to lowercase as a part of the SQL (Oracle).

Thanks for the idea.
It could help a lot to see the code you used to convert the table field value into lower case.
See my function addQuickSearchInUpper where I try to do the same with uppercase.