Crud: Ajax search failing when using PostgreSQL

Created on 16 Jan 2017 · 14Comments · Source: Laravel-Backpack/CRUD

Our Laravel app is using PostgreSQL v9.6 for it's database because of our need of geo functions.
When trying to use the search feature with ajax tables we are getting the following error

Next Illuminate\Database\QueryException: SQLSTATE[42883]: Undefined function: 7 ERROR:  operator does not exist: integer ~~ unknown
LINE 1: ... or "email" like $3 or "password" like $4 or "id" like $5) a...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. (SQL: select count(*) as aggregate from "users" where ("first_name" like %marc% or "last_name" like %marc% or "email" like %marc% or "password" like %marc% or "id" like %marc%) and "users"."deleted_at" is null) in <SNIP>/vendor/laravel/framework/src/Illuminate/Database/Connection.php:770

In https://github.com/Laravel-Backpack/CRUD/blob/master/src/app/Http/Controllers/CrudFeatures/AjaxTable.php#L23 it is explicitly adding the primary key as a field to search, but in all of our tables the primary key is an integer which cannot have the LIKE operator used on it.

Is there a simple way of changing this to either not search the primary key or use = instead?

Bug

Source

marcroberts

👍2

All 14 comments

I'm having the same issue as you :(

This appears to be a workaround to get the ID in the response from EloquentDataTable.

I'd assume if there was a fix it would have to be there - here, more specifically - to check if the LIKE operator is available for the current column.

macecchi on 24 Jan 2017

I found a _really_ hacky workaround - to define the LIKE operator that is missing in PostgreSQL yourself.

CREATE OR REPLACE FUNCTION public.int_like(leftop integer, rightop text)
 RETURNS BOOLEAN
 LANGUAGE SQL
AS $function$
SELECT $1::text LIKE $2;
$function$;

CREATE OPERATOR PUBLIC.~~ (LEFTARG=integer, RIGHTARG=text, PROCEDURE=int_like);

(The LIKE operator is actually an "alias" to the ~~ operator in PostgreSQL, which is the one you want to define.)

Credits: https://www.postgresql.org/message-id/flat/CAEkCx9GGQbPLAdJd%2BagdLqv0mYYJ9VjpZ_cKHgJX0t8MmAWA1w%40mail.gmail.com#CAEkCx9GGQbPLAdJd [email protected]

macecchi on 24 Jan 2017

Hmm... What if you define a search() method on the controller in question, with the exact same content as this method, but without the highlighted line? Does that fix it for you? Does the search still work?

tabacitu on 24 Jan 2017

@tabacitu Then the search works but the buttons don't, as the ID is needed for the edit/delete buttons

macecchi on 25 Jan 2017

Anybody found a solution in the meantime?

Cheers!

tabacitu on 4 Apr 2017

😕2

@tabacitu I ended up going with the hacky solution. Here's the migration I created for it.
It will only run if the database driver is pgsql. Maybe that could be added to the CRUD installation?

<?php

use Illuminate\Database\Migrations\Migration;

class CreateIntegerLikeOperator extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        if (DB::connection()->getDriverName() != 'pgsql') return;

        DB::connection()->getPdo()->exec('
            CREATE OR REPLACE FUNCTION public.int_like(leftop integer, rightop text)
             RETURNS BOOLEAN
             LANGUAGE SQL
            AS $function$
            SELECT $1::text LIKE $2;
            $function$;

            CREATE OPERATOR public.~~ (LEFTARG=integer, RIGHTARG=text, PROCEDURE=int_like);
        ');
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        if (DB::connection()->getDriverName() != 'pgsql') return;

        DB::connection()->getPdo()->exec('
            DROP OPERATOR IF EXISTS public.~~ (integer, text);
            DROP FUNCTION IF EXISTS public.int_like(integer, text);
        ');
    }
}

macecchi on 22 Apr 2017

👍1

Just as an idea... searching by primary key is useful, to find... erm... element with primary key = x !

I mean, most of the time if ypou want to search by primary key, you want one single result, the ID of the row, so maybe a compromise solution would be to modify the ->merge($this->crud->model->getKeyName()) search to generate a OR 'primary_key' = 'X' instead of OR 'primary_key' LIKE '%X%' just for this case, only the primary key.

That would:

Fix this problem
Ensure faster searchs by primary key

The only downside is it would not find "199" if you search primary key "19", but I really think that's a corner case

MarcosBL on 22 Apr 2017

Hi guys,

I think I've recently solved the issue, but I need your help to confirm :-)

I've rewritten the entire search() method and the AjaxDataTables functionality on the upgrade branch, and I expect to launch everything as Backpack 3.3 by the end of the week.

It now no longer searches the primary key because, as @MarcosBL pointed out, it doesn't really make sense. It only searches the columns you define. Plus, when you define a column you now have the ability to define a custom search logic, as a closure, or tell CRUD to not make that column searchable at all (searchLogic = false):

        $this->crud->addColumn([
                                'name' => 'slug_or_title',
                                'label' => 'Title',
                                'searchLogic' => function ($query, $column, $searchTerm) {
                                    $query->orWhere('title', 'like', '%'.$searchTerm.'%');
                                }
                            ]);

I think this fixes this issue AND provides a way to customize, in case the issue arises with other columns (integer, etc).

What do you guys think?

To use the upgrade branch you can specify the version "dev-upgrade" for Backpack\CRUD in your composer.json file, then run composer update.

tabacitu on 25 Oct 2017

Hi @tabacitu!

Thanks for putting your effort into that. Your solution looks awesome! The way of providing a custom search implementation for each column is really useful.

I won't have the time to test the new branch so soon, but when I do it I'll reach out with feedback. :D

macecchi on 25 Oct 2017

CREATE OR REPLACE FUNCTION public.int_like(leftop integer, rightop text)
 RETURNS BOOLEAN
 LANGUAGE SQL
AS $function$
SELECT $1::text LIKE $2;
$function$;

CREATE OPERATOR PUBLIC.~~ (LEFTARG=integer, RIGHTARG=text, PROCEDURE=int_like);

CREATE OR REPLACE FUNCTION public.numeric_like(leftop numeric, rightop text)
 RETURNS BOOLEAN
 LANGUAGE SQL
AS $function$
SELECT $1::text LIKE $2;
$function$;

CREATE OPERATOR public.~~ (LEFTARG=numeric, RIGHTARG=text, PROCEDURE=numeric_like);