Postgresql and UUID issues

[allan-christian]

Hello all,
I never had any issues with Query Builder until today. All my models use UUID as the primary key (ID), that said the following code is throwing an error if I filter by dataset_id:

DataEntryListResource::collection(
    QueryBuilder::for(DataEntry::class)
        ->withCount('rows')
        ->with('dataset')
        ->allowedFilters([
            'user_id',
            'dataset_id',
            'type',
            'status',
            AllowedFilter::partial('author', 'author.name'),
            AllowedFilter::partial('dataset', 'dataset.name'),
        ])
        ->defaultSort('-updated_at')
        ->allowedSorts([
            'type',
            'status',
            'created_at',
            'updated_at',
            'rows_count',
            AllowedSort::custom('author', new AuthorNameSort()),
            AllowedSort::custom('dataset', new DatasetNameSort()),
        ])
        ->allowedIncludes(['rows', 'dataset', 'dataset.columns'])
        ->paginate()
        ->appends(request()->query())
);

This is the error:

[2021-01-25 18:12:07] local.ERROR: SQLSTATE[42883]: Undefined function: 7 ERROR:  function lower(uuid) does not exist
LINE 1: ...t count(*) as aggregate from "data_entries" where LOWER("dat...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts. (SQL: select count(*) as aggregate from "data_entries" where LOWER("dataset_id") LIKE %9292e844-1179-4f33-aa81-93529f41a182% and "data_entries"."deleted_at" is null) {"userId":"9292e94c-3a49-49f0-b4d5-f10c75888809","exception":"[object] (Illuminate\\Database\\QueryException(code: 42883): SQLSTATE[42883]: Undefined function: 7 ERROR:  function lower(uuid) does not exist
LINE 1: ...t count(*) as aggregate from \"data_entries\" where LOWER(\"dat...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts. (SQL: select count(*) as aggregate from \"data_entries\" where LOWER(\"dataset_id\") LIKE %9292e844-1179-4f33-aa81-93529f41a182% and \"data_entries\".\"deleted_at\" is null) at /mnt/c/Projects/analytical-results-db/vendor/laravel/framework/src/Illuminate/Database/Connection.php:671)
[stacktrace]

What is happening is that the LOWER("dataset_id") does not accept a UUID value. If I cast it to TEXT it works.

Is there any way to fix it?

Thank you all.

[allan-christian]

I did remember reading in the documentation something about exact filters, and boom. Done! This plugin is amazing!

Updated code with the solution:

DataEntryListResource::collection(
    QueryBuilder::for(DataEntry::class)
        ->withCount('rows')
        ->with('dataset')
        ->allowedFilters([
            AllowedFilter::exact('user_id'),
            AllowedFilter::exact('dataset_id'),
            'type',
            'status',
            AllowedFilter::partial('author', 'author.name'),
            AllowedFilter::partial('dataset', 'dataset.name'),
        ])
        ->defaultSort('-updated_at')
        ->allowedSorts([
            'type',
            'status',
            'created_at',
            'updated_at',
            'rows_count',
            AllowedSort::custom('author', new AuthorNameSort()),
            AllowedSort::custom('dataset', new DatasetNameSort()),
        ])
        ->allowedIncludes(['rows', 'dataset', 'dataset.columns'])
        ->paginate()
        ->appends(request()->query())
);