case-insensitive datagridFilter with postgreSQL

[Stefan Paschke]

Hi

I am working an a project with the SonataAdmin (using SonataORMAdmin) and SonataUser bundles on top of a postgreSQL database.
Unlike MySQL, LIKE queries in postgreSQL are case-sensitive, and so is the doctrine_orm_string filter, resulting in a Filter form where a search in the username field, for example, for the term 'admin' will not return the same as a search for 'Admin'.

What is the best way to deal with this probem?

To my surprise, I could not find an option for making the doctrine_orm_string case-insensitive - please correct me if I missed something here. I can, however, see two solutions:

- use a doctrine_orm_callback filter and write a callback function that lowercases the search value and LIKEs it to the lowercase db field (or, in my case, the usernameCanonical)

- write a custom doctrine_orm_string_case_insensitive filter that takes care of this logic and inject it in the container.

Does anyone have another, easier solution, or any experiences with any of the above? Thanks for sharing!


best regards

Stefan Paschke

[David Buchmann]

i am stuck on the same question. did somebody find a solution for this?

thanks,david

[Thomas Rabaix]

What should be the correct solution ?

MySQL seems to use the collation definition to create a case sensitive or insensitive so this will impact the LIKE clause.

PGSQL seems to be case sensitive by default and required the use to ILIKE clause.

I guess, it will be possible to add more options to the StringFilter: https://github.com/sonata-project/SonataDoctrineORMAdminBundle/blob/master/Filter/StringFilter.php#L38-L40

--
You received this message because you are subscribed to the Google Groups "sonata-users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sonata-users...@googlegroups.com.

To post to this group, send email to sonata...@googlegroups.com.

Visit this group at http://groups.google.com/group/sonata-users.
For more options, visit https://groups.google.com/d/optout.

--
Thomas Rabaix
http://rabaix.net | http://sonata-project.org

[David Buchmann]

the "best" solution seems to be to do

WHERE LOWER(column) LIKE LOWER('%filtertext%')

having an index on LOWER(column) postgres should pick this up.

i will try to replace the service sonata.admin.orm.filter.type.string
with a class extending the StringFilter - i assume this is how the
StringFilter is loaded?

once i got something working, i can attempt a pull request.

thanks,david

> - write a custom doctrine_orm_string_case___insensitive filter

> that takes care of this logic and inject it in the container.
>
> Does anyone have another, easier solution, or any experiences
> with any of the above? Thanks for sharing!
>
>
> best regards
>
> Stefan Paschke
>
> --
> You received this message because you are subscribed to the Google
> Groups "sonata-users" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to sonata-users...@googlegroups.com

> <mailto:sonata-users...@googlegroups.com>.

>
> To post to this group, send email to sonata...@googlegroups.com

> <mailto:sonata...@googlegroups.com>.

> Visit this group at http://groups.google.com/group/sonata-users.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
>
> --
> Thomas Rabaix
> http://rabaix.net | http://sonata-project.org
>

> --
> You received this message because you are subscribed to the Google
> Groups "sonata-users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sonata-users...@googlegroups.com

> <mailto:sonata-users...@googlegroups.com>.

> To post to this group, send email to sonata...@googlegroups.com

> <mailto:sonata...@googlegroups.com>.

> Visit this group at http://groups.google.com/group/sonata-users.
> For more options, visit https://groups.google.com/d/optout.

--

Liip AG // Agile Web Development // T +41 26 422 25 11
CH-1700 Fribourg // PGP 0xA581808B // www.liip.ch

[David Buchmann]

for now, we do this:
https://gist.github.com/dbu/9524776#file-caseinsensitivestringfilter-php-L40

i see 2 things we could do:

doctrine_orm_filter_types.xml add an option to configure the default
behaviour
sonata.admin.orm.filter.type.string

additionally, we could add an option to the filter to set it per case
(who knows, maybe i have one field where i really want case sensitive
filtering).

btw, one can achieve the opposite, make mysql case sensitive for likes
with "column COLLATE utf8_bin LIKE :value" (resp = or NOT LIKE)


not sure how this is best modeled. if we don't care about BC, i would
say postgres should by default start to compare case insensitive. but
this would be very surprising for existing applications that rely on the
behaviour.

having 2 more ChoiceType options sounds like a bad idea too, it will
overkill for the admin user most of the time.

so what about adding the default and per-filter option to overwrite the
behaviour to explicitly case sensitive or explicitly case insensitive?
we would then need to look into the doctrine driver to determine what we
need to do about it. (and what about other db than postgres + mysql? i
don't know them so i would throw an exception that this is not supported
if an explicit option is set)

cheers,david

Am 10.03.2014 12:11, schrieb Thomas Rabaix:

> - write a custom doctrine_orm_string_case___insensitive filter

> that takes care of this logic and inject it in the container.
>
> Does anyone have another, easier solution, or any experiences
> with any of the above? Thanks for sharing!
>
>
> best regards
>
> Stefan Paschke
>
> --
> You received this message because you are subscribed to the Google
> Groups "sonata-users" group.
> To unsubscribe from this group and stop receiving emails from it,
> send an email to sonata-users...@googlegroups.com

> <mailto:sonata-users...@googlegroups.com>.

>
> To post to this group, send email to sonata...@googlegroups.com

> <mailto:sonata...@googlegroups.com>.

> Visit this group at http://groups.google.com/group/sonata-users.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
>
> --
> Thomas Rabaix
> http://rabaix.net | http://sonata-project.org
>

> --
> You received this message because you are subscribed to the Google
> Groups "sonata-users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sonata-users...@googlegroups.com

> <mailto:sonata-users...@googlegroups.com>.

> To post to this group, send email to sonata...@googlegroups.com

> <mailto:sonata...@googlegroups.com>.

> Visit this group at http://groups.google.com/group/sonata-users.
> For more options, visit https://groups.google.com/d/optout.

--

[David Buchmann]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

created an issue for this.

https://github.com/sonata-project/SonataDoctrineORMAdminBundle/issues/334

- --

Liip AG // Agile Web Development // T +41 26 422 25 11
CH-1700 Fribourg // PGP 0xA581808B // www.liip.ch

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJTNBx2AAoJED/JtliXIA4s0JgIAIK+W/rK5c/x9xstM6BmN+ku
AdEJBXqXeuXywrBjcKzCPxBkx3/fHYr/u688TK85FdEY8GIeTZHV6qDjF5s/8Mps
OLoB5OoADFvjMRlGYU5aQ0KuGnNhtSFjqFUCf3/lD7HL0mzKpSTnJEMdBJdLaATe
Rr8z5tIsAq1qVjass70/55dVE+y9upCBQfAyypwW96iXhO/tVNacWPgMaH3Te28q
qIu69Ru08e/j8SGnSmo8kcxKTmI9SvsSG1NlL4fJC6+ESci8JcFzak+8raE7pyl/
MHWO3lL5PkFoVQfj3OwEyndGAi+CgfAlSYMmqah7fkCD++LbP0/qYhSBu+U/Nqo=
=z5WB
-----END PGP SIGNATURE-----