Search error when using Postgres (LIKE cannot be used on integers)

[ThomasNucleus]

This error occurs when you try using the search with a Postgres database:

Error: SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: integer ~~ unknown LINE 1: ..." FROM securities Securities WHERE (Securities.id like $1 OR... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

You can't use LIKE for an integer on Postgres

To fix, cast the field to text

In DataTablesComponent.php _addCondition function

Change $condition = ["{$column } LIKE" => $right]; to $condition = ["{$column}::text LIKE" => $right];

Will this cause any unintended issues for other database systems?

My pull request: #47

[ThomasNucleus]

I have checked out the devel branch to see if this is fixed.

I now get the following error on the devel branch: "Cannot convert value to bool"

DataTablesComponent.php, line 226

// -- retrieve filtered count
  | $this->_viewVars['recordsFiltered'] = $data->count();

Seems to happen when we have a query with text search parameters on boolean fields.

Here I type the text "Test" into the search

    'params' => [
        ':c0' => [
            'value' => 'Test',
            'type' => 'integer',
            'placeholder' => 'c0'
        ],
        ':c1' => [
            'value' => '%Test%',
            'type' => 'string',
            'placeholder' => 'c1'
        ],
        ':c2' => [
            'value' => 'Test',
            'type' => 'boolean',
            'placeholder' => 'c2'
        ]
    ],

Then if I search "1", no problems occur.

[ypnos-web]

So if you have a column that displays booleans, I assume text search does not really make sense on that column anyways. I would set the searchable flag on that column to false. (using the new Columns interface you can do it with ->notSearchable().

[ThomasNucleus]

Also another error comes up with timestamp fields. "LIKE" cannot be used for timestamps, and if you change it to an "=" there will still be issues because the date search format always needs to be valid.

https://stackoverflow.com/questions/4799615/postgresql-select-data-with-a-like-on-timestamp-field

What's the best course of action? Should we just cast ::text to problematic fields for postgres users?

edit: Just saw your previous reply. Making the boolean fields not searchable is workable. But for timestamp fields they ideally should be searchable, eg I can just type in 2018 to get the records with a 2018 timestamp

[ypnos-web]

I agree that we presently have no good solution for date fields with PostgreSQL. In general we lack the ability to do substring comparisons also for other fields, e.g. numbers.

It would be great if we could extend the current functionality in devel for this, whereas only in the case of "LIKE" the appropriate casting (::text) is automatically done. However I believe we need to make this PostgreSQL-exclusive.

Would you be interested in rebasing your pull request on the devel branch and include a test for the database adapter? That would be great!

[ypnos-web]

This is now in 2.0 and upwards releases.