PostgreSQL search issues about filament HOT 30 CLOSED

Maybe it's easier to just change the operators according to the DB driver I think, for example:

$operators = [
  'mysql' => 'LIKE',
  'pgsql' => 'ILIKE',
];

/** @var string $dbConnection */
$dbConnection = config('database.default');

$query->where('field', $operators[$dbConnection] ?? 'LIKE', '%' . $searchValue . '%');

I was looking through Laravel DB drivers APIs to achieve this in a smarter way, but there's nothing related to a "like" operator based on the driver neither in Grammar class unfortunately (cc @danharrin )

I think there's an extra bit of complexity here because the connection being used for queries might change based on the model, so it would need to use the model's connection instead of the default one.

from filament.

@danharrin @ryangjchandler I am working these issue and will make a pr soon

from filament.

It seems a bit dirty, but what a solution like this?

SELECT * FROM table WHERE LOWER(CONCAT(position, '')) like '%55%'; // position is an int in this case

It forces any field to cast into a string by performing a concat with nothing and it works with both Postgres and MySQL.

Before this solution, I was trying to take advantage of CAST, but I couldn't find a cast type that works for both:

select * from demo where lower(CAST(position as CHAR)) like '%55%' // Search works in MySQL but not in Postgres (no errors)
select * from demo where lower(CAST(position as VARCHAR)) like '%55%' // Valid in MySQL but not in Postgres
select * from demo where lower(CAST(position as TEXT)) like '%55%' // Valid in Postgres but not in MySQL
select * from demo where lower(CAST(position as BINARY)) like '%55%' // Valid in MySQL but not in Postgres

Anyway I agree with EQuimper that having the ability to override the default query would be a nice feature tho, like ->searchable(fn ($query) => $query->where(...));

from filament.

@EQuimper I have checked and everything seems ok as expected

from filament.

Yeah, like on the query should handle results case insensitively?

from filament.

@danharrin Only with MySQL / MariaDB - Postgres is case-sensitive if I remember correctly.

from filament.

@danharrin I'm using postgresql also. Not sure if this change something

from filament.

Postgres is case-sensitive when querying @EQuimper.

from filament.

@ryangjchandler AFAIK ilike is the case insensitive version for Postgres, but this is not compatible with MySQL. Do you think we should detect the DB driver a switch appropriately?

from filament.

@ryangjchandler Yeah I mean I do understand it and did see in the code the like search you do. But why this is happening in my case. In postgres I use iLike for those issue.

from filament.

@danharrin Yeah, that could work. It's a little bit annoying but would fix the issue and it's only going to be on place really.

from filament.

@ryangjchandler maybe a helper function to return the correct operator based on the driver?

from filament.

@ryangjchandler maybe a helper function to return the correct operator based on the driver?

Yeah, that's one way for sure.

from filament.

I'm pretty sure this is used in the tables package too so will decide depending on the complexity.

from filament.

@danharrin An alternative solution would be doing a where('LOWER(column_name)') and then doing an mb_strtolower() on the search itself.

from filament.

Would there be a considerable performance impact using that with MySQL?

from filament.

@danharrin I guess no.

from filament.

Would there be a considerable performance impact using that with MySQL?

Not that I can think of. This is what Spatie's search package does when you want to search based on a partial match, like the original issue.

It does mean you need to use a whereRaw instead of where, but as long as the bindings are done correctly it should be fine.

from filament.

@danharrin @EQuimper @ryangjchandler Please check these PR.

#78

from filament.

Fixed in #78.

from filament.

@danharrin I think something else come from the fix at #78 Now when I search with an integer I get this error

SQLSTATE[42883]: Undefined function: 7 ERROR: function lower(bigint) does not exist LINE 1: select count(*) as aggregate from "users" where (LOWER(id) L... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. (SQL: select count(*) as aggregate from "users" where (LOWER(id) LIKE %2%) or (LOWER(email) LIKE %2%) or (LOWER(username) LIKE %2%))

Remember this is on postgres

from filament.

Maybe it's easier to just change the operators according to the DB driver I think, for example:

$operators = [
  'mysql' => 'LIKE',
  'pgsql' => 'ILIKE',
];

/** @var string $dbConnection */
$dbConnection = config('database.default');

$query->where('field', $operators[$dbConnection] ?? 'LIKE', '%' . $searchValue . '%');

I was looking through Laravel DB drivers APIs to achieve this in a smarter way, but there's nothing related to a "like" operator based on the driver neither in Grammar class unfortunately (cc @danharrin )

from filament.

I think there's an extra bit of complexity here because the connection being used for queries might change based on the model, so it would need to use the model's connection instead of the default one.

Uh you're right, didn't think about that. The main issue is that the connection name on the model is protected, adding a trait to only set up a getConnectionName() may be a bit odd?

from filament.

What do you think about adding an options to the searchable method where we can override default value ? This way you let the user manage it from model to model etc ?

from filament.

We can use $record->getConnection()->getDriverName() to get the driver name BTW.

from filament.

We can use $record->getConnection()->getDriverName() to get the driver name BTW.

That's good. I mean, right now there are two possible solutions:

1. A driver-aware syntax "map" (through getDriverName())
2. A SQL-only solution valid for both Postgres and MySQL with CONCAT (Idk about performance here)

I think the main pros of the solutions are:

• Driver-aware: can ship support for many drivers out-of-the-box; can do more optimization based on the driver (?)
• Concat: avoid more business logic

Sooo... It's up to you guys 😄

from filament.

@EQuimper @danilopolani I've opened #168, please check if it fixes the issue.

from filament.

Closed by #168.

from filament.

Hello, it seems this was never ported to Filament 3 - hence always requiring the forceSearchCaseInsensitive() method to be called in order to search for queries where the results contain upper case characters. Any plans to port it?

from filament.

The docs explain why forceSearchCaseInsensitive() exists. Whats your issue with it?

from filament.