How to search case insensitive with addQuickSearch in a CRUD?

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']);
?>

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.

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).

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

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);
    }
}

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.

Yes some code refactoring probably would be good. Please feel free to contribute, it’s an open-source framework.

I don’t have good success with github, so I’m posting here a block of code I changed within the getData() function in AutoComplete.php:

        if (isset($_GET['q'])) {
        if ($this->search instanceof Closure) {
            $this->search($this->model, $_GET['q']);
        } elseif ($this->search && is_array($this->search)) {
			if(get_class($this->model->persistence->connection) == 'atk4\dsql\Connection_Oracle') {
				$this->model->addCondition(array_map(function ($field) {
					/* case-insensitive, single-quote-replaced functions may only work with Oracle */
					return [$this->model->expr("(lower([" . $field . "]) like lower('%" . str_replace('\'', '\'\'', $_GET['q']) . "%'))")];
				}, $this->search));
			} elseif(get_class($this->model->persistence->connection) == 'atk4\dsql\Connection') {
				$this->model->addCondition(array_map(function ($field) {
					return [$field, 'like', '*'.$_GET['q'].'*'];
				}, $this->search));
			} else {
				$this->model->addCondition(array_map(function ($field) {
					return [$field, 'like', '%'.$_GET['q'].'%'];
				}, $this->search));
			}
        } else {
			if(get_class($this->model->persistence->connection) == 'atk4\dsql\Connection_Oracle') {
				/* case-insensitive function may only work with Oracle */
				$this->model->addCondition($this->model->expr("(lower([" . $field . "]) like lower('%" . str_replace('\'', '\'\'', $_GET['q']) . "%'))"));
			} elseif(get_class($this->model->persistence->connection) == 'atk4\dsql\Connection') {
				$this->model->addCondition($title_field, 'like', '*'.$_GET['q'].'*');
			} else {
				$this->model->addCondition($title_field, 'like', '%'.$_GET['q'].'%');
			}
        }
    }

As you can see it checks the type of connection and for Oracle databases it converts everything to lowercase in the comparison.

Thanks for your code. I updated it for PgSql but it stays case sensitive for searching.
Then I have renamed “vendor/atk4/ui/src/FormField/AutoComplete.php” but the CRUD continue to work without issue => AutoComplete.php is not used for CRUD Search in Agile Toolkit 1.6.5.
I don’t know where the search in CRUD is implemented.

Oops! Sorry. I don’t know why I was thinking about the AutoComplete when clearly we were discussing the CRUD search. :slight_smile: Here is the snippet of code I modified inside the addQuickSearch function inside of grid.php:

    if ($q = $this->stickyGet('_q')) {
        $cond = [];
        foreach ($fields as $field) {
			// $cond[] = [$field, 'like', '%'.$q.'%'];
			/* case-insensitive "lower" function may only work with Oracle */
			$cond[] = [$this->model->expr("(lower([" . $field . "]) like lower('%" . $q . "%'))")];
        }
        $this->model->addCondition($cond);
    }

Unlike with AutoComplete I haven’t (yet) tailored it to handle databases other than Oracle.

1 Like

Super ! It works fine, also with PgSql. I updated ;<) your comments inside function addQuickSearch in Grid.php:

    if ($q = $this->stickyGet('_q')) {
        $cond = [];
        foreach ($fields as $field) {
            // $cond[] = [$field, 'like', '%'.$q.'%'];      // Original Code
            /* case-insensitive "lower" function may only work with Oracle or PgSql */
            $cond[] = [$this->model->expr("(lower([" . $field . "]) like lower('%" . $q . "%'))")];

            /* Case insensitive search, may only works with PgSql */
            // $cond[] = [$this->model->expr("([" . $field . "] ~* '" . $q . "')")];
        }
        $this->model->addCondition($cond);
    }

In MySQL case-sensitivity depends on column/field collation (as given by the collation suffix _ci or _cs).
It would affect the comparison results.
Isn’t this the case in Oracle?

I think only since Oracle 12.2. We still use 11 for some databases.