search with doctrine orm

[kermorgant]

Hello,

I've tried to setup a simple search using search-doctrine-orm-bundle, but there's something I have missed to get the conditions applied to the statement (so I get all records instead of only the one I ask for).

I've put everything into one action to start experimenting, and it looks like this :
https://gist.github.com/kermorgant/2a1396681ed74082614c50cff71ace5c

Could you please have a look and see what's missing ?

Regards,
Mikael

[sstok]

Hi, first of let me apologize for the horrible documentation.
I'm actually working on version 2.0 which will be much easier to use, better documented (no more separated documentation) and have support for non-localized input (like a REST service), you can follow #87 for more information.

So you may want to wait till v2.0 as there is no BC layer and a complete upgrade is required.
Or you can start experimenting once alpha1 (scheduled for the 2st of April) is released (Doctrine and Symfony integration is currently pending as things are still being changed).

For now, looking at the gist there a number of problems in usage.

When using RollerworksSearch with the Symfony Framework you need to install and enable the RollerworksSearchBundle and use the services (for the best experience).

Use $this->get('rollerworks_search.factory') to get the SearchFactory and the rollerworks_search.doctrine_orm.factory service for the DoctrineOrmFactory.

$whereBuilder->updateQuery(' AND ');

$whereClause = $whereBuilder->getWhereClause(' WHERE ');

You only need updateQuery or getWhereClause, updateQuery updates the Query statement with the SearchCondition (when not empty) and the recommended way of updating your Query.

$query->setHint($whereBuilder->getQueryHintName(), $whereBuilder->getQueryHintValue()());
This is already done by updateQuery.
http://rollerworks-search-doctrine-orm.readthedocs.io/en/latest/querying.html#generating-the-where-clause (this can actually be rewritten to give updateQuery as first example 🤔 )

[kermorgant]

Hello,

Thanks for your fast answer. Unfortunately, I won't have time to wait and would like to get this bundle working. I've looked around for pure php search solutions, and really liked what I read in the docs (I hope I don't have horrible taste :-)

Indeed, my gist was quite an ugly piece of code, I was planning to put it in shape after my test. I took your advice and cleaned it, but the problem is still there. I think I've missed something with the searchCondition logic or the way to glue it...

It now looks like this : https://gist.github.com/kermorgant/9af1d3c900fb2c08594fb3eb09ae0ec9

The var_dump at line 26 shows this : 'SELECT l FROM AppBundle\Entity\Livre AS l'

I you have an idea, I'd be glad to hear about it.
Regards

[sstok]

There is no mapping between the fields and the Entity properties.

This can be done with the setField method.

Or setting the model_class and model_property (deprecated and removed in 2.0).
https://github.com/rollerworks/search/blob/1.x/src/Extension/Core/Type/FieldType.php#L72-L73

Note that for processing a search request there is a SearchProcessor in the bundle but the documentation is incomplete (https://github.com/rollerworks/RollerworksSearchBundle/pull/15/files).

I've looked around for pure php search solutions, and really liked what I read in the docs.

I will see if I can fix-up some parts as I should be able to re-use those for v2.0 😃

[kermorgant]

Thanks again, that did the trick !
I also looked at the tests and saw there was a nice way to put it directly during the fieldset creation, like this

        $fieldset = $searchFactory->createFieldSetBuilder('test')
            ->add('id', 'integer', [], false, 'AppBundle\Entity\Livre', 'id')
            ->add('title', 'text', [], false, 'AppBundle\Entity\Livre', 'title')
            ->getFieldSet();

Sorry to ask again for help, but I tried pattern matching (with mariadb 10) and got this error :

An exception occurred while executing 'SELECT l0_.epub_file_name AS epub_file_name_0, l0_.updated_at AS updated_at_1, l0_.id AS id_2, l0_.title AS title_3, l0_.slug AS slug_4, l0_.description AS description_5, l0_.pdf_file_name AS pdf_file_name_6, l0_.pdf_num_pages AS pdf_num_pages_7, l0_.thumbnail_file_name AS thumbnail_file_name_8, l0_.sommaire AS sommaire_9, l0_.published_at AS published_at_10, l0_.category_id AS category_id_11 
FROM livre l0_ 
WHERE (((l0_.title LIKE 'Activi%' ESCAPE '\\\\' OR l0_.title LIKE '%conseil%' ESCAPE '\\\\')))':

SQLSTATE[HY000]: General error: 1210 Incorrect arguments to ESCAPE
500 Internal Server Error - DriverException
2 linked Exceptions: PDOException » PDOException »

Got it working for my setup by replacing line 142 in AbstractQueryPlatform.php by

    return $column.($patternMatch->isExclusive() ? ' NOT' : '')." LIKE $value ESCAPE '\'";

But this is not a solution, just debugging

Any idea to get that right ?

[kermorgant]

Would this be an acceptable fix ?

    $escape = $this->connection->quote(stripslashes("\\"));

[sstok]

stripslashes("\\") produces an empty string 😅 '\' I cannot believe that actually works (with MySQL) 😯 the SQL standard states that escaping should be done with ' (for the quotes).
But using backslashes has actually worked in the past 🤔

I will investigate this further 👍

[sstok]

I cannot reproduce this with MySQL, trying MariaDB now 😃

Well that's odd it's working with the DBAL driver but the ORM driver (which internally uses the same logic) fails to work 😐 it seems the backslash is double escaped.

[kermorgant]

Hi. Thanks for the fix, works fine now !

[sstok]

Great to hear that 👍 I will still add some tests in ORM to prevent regressions.