Crud: Table search not working properly

[marleybobby]

I switched from V3.2 to V3.3 and it's awesome that ajax search is enabled by default and that relations are working as expected.

I have a huge table with more than 10k+ movies and another table with more than 2k+ series.
The problem now is that the tablesearch mostly won't return any result.

Database structure:
movie_id | title | other columns.

If I now search for example for: Trick'r Treat oder just Trick I don't get any result. The same occurs for many other movies like Lucy, Project X etc. but some are working like: Matrix.
The same behaviour occurs for the series table.

I don't know if this matters but i have multiple 1-n relationships like:

$this->crud->addColumn([
            // 1-n relationship
            'label' => "Author", // Table column heading
            'type' => "select",
            'name' => 'id', // the column that contains the ID of that connected entity;
            'entity' => 'user', // the method that defines the relationship in your Model
            'attribute' => "name", // foreign key attribute that is shown to user
            'model' => "App\Models\User", // foreign key model
        ]);

Before the update it worked perfect. Why am I getting mostly no results anymore?

[tabacitu]

Hi @marleybobby ,

The search behaviour would be different if you were using non-AJAX datatables before, yes. But if you were using AJAX tables before, the results should be pretty much the same. The default rule is simple: WHERE column_name LIKE %query%. Both for normal, 1-n and n-n relationships. Take a look here to better understand how the logic is applied.

I'm afraid with a bug report so vague there's not much I can do for you. Also, I wasn't able to replicate your problem on my data set - search works as expected over here. So I'm going to close the issue, as it doesn't look like a bug in the core to me.

How about if you replace the searchLogic with your own, for the column you're not satisfed with the results? How would that logic look so that you get the results you want?

Cheers!

[marleybobby]

I took a look at the applySearchLogicForColumn Code and it helped me thanks. Because I have my own column the type didn't match with the default ones.

[tabacitu]

Damn. You're right, @marleybobby , thanks. The documentation wasn't very explicit that custom columns need their own search logic. I included that in the docs now. Thanks, cheers!

[ziming]

I wish search logic by default is case insensitive. Or add a section in documentation on how to make it case insensitive

Or provide an easy option to swap between case sensitive and case insensitive search

[tabacitu]

Hmm... good question @ziming . All default searchLogics are case-insensitive. If you don't specify any searchLogic it will do $query->orWhere($column['name'], 'like', '%'.$searchTerm.'%'); for most fields, which does not care about upper/lower case.

I'm afraid we can't apply case-insensitivity to what developers write, their custom search. It's their code, their business how they do the search. We shouldn't, and _can't_ mess with that.

Cheers!

[tswonke]

I didn't need such a behavior yet. As I understand it, this is primarily a database thing rather then code. So I would suggest to use a case sensitive collation on your database instead.

[ziming]

I don't think so @tabacitu. I did a $query->orWhere($column['name'], 'like', '%'.$searchTerm.'%'); too but the search is still case sensitive.

Secondly, @tswonke depending on how much authority you have, you may not be allowed to change collation on the database in the organization you work for too.

So there got to be a better way to force case insensitive or case sensitive search.

[tswonke]

IMO you don't need authority for that. Just adapt your searchLogic to apply COLLATE and choose one that fits to your needs.

SQL-Example:

SELECT * FROM users WHERE name like '%CaseSensitive%' COLLATE utf8_bin;

[ziming]

Ah nice. Let me try some time this week and get back to you (does eloquent as a collate method?). Still, I wish backpack/CRUD provides an easy way to do this though. @tabacitu

[tswonke]

Maybe this will help you:

https://laravel.com/docs/5.6/database#read-and-write-connections

[ziming]

Hmm, would that cause newer table migrations to create tables with that collation as well?

[tswonke]

I suppose so.

In your case I would suggest to define a second connection with the collation you need and only use it for certain models and/or certain db-requests.

[ziming]

How about backpack by default behind the scene run LOWER() on the search string & column while searching for (MySQL) and do ILIKE if it is PostgreSQL?

[tabacitu]

@ziming I don't think that's going to happen anytime soon, sorry. Not having access to change collation is rare for a developer. I don't think we should bloat the interface logic, for those very few users who can't do this at the right layer of their stack. Right now, the search works _as expected_. It follows whatever you set the database collation to. If we change it, it would no longer be _intuitive_, it would be _opinionated_. A change for the worse, imo. It would be good for your use case, sure. But it would be bad for 99% other use cases. Sorry.

[ziming]

Okay sure

think spatie api query builder does LOWER(...) for it's filter but that's them

Thanks for the reply :)

[brynnb]

Just as a note - the Heroku Postgres extension doesn't allow developers to change the default collation, though it can still be done on a per-field basis in migrations. Still not sure this warrants further development as @ziming suggested but Heroku is a pretty popular platform, and their default Postgres extension is probably a popular extension.

More detail: https://stackoverflow.com/questions/12334986/set-locale-on-heroku-postgres