Cannot be used with PostgreSQL

[EdouardCourty]

Describe the bug
Hi, when using TypesenseBundle with a PostgreSQL database, it is impossible to use the query method from CollectionFinderInterface, that is supposed to return hydrated Doctrine Entities.
The bundle uses the ORDER BY FIELD directive, which is specific to MySQL.

To Reproduce
Steps to reproduce the behavior:

1. Start a Symfony 6.3 project, with a PostgreSQL database, and TypesenseBundle enabled
2. Configure the bundle properly to persist an entity (like a topic with an author and a content)
3. Persist at least one entity
4. Use the query method

Expected behavior
Should return the hydrated objects instead of crashing (from the SQL server).

Screenshots

Desktop (please complete the following information):

• OS: Ubuntu 22.04 LTS
• Docker

Additional context

• PostgreSQL version: postgres:14.2 (postgres:14.2-alpine docker image)
• Typesense version: 0.25.0 (typesense/typesense:0.25.0 docker image)

[EdouardCourty]

I suggest to add a configuration key 'database', which accepts either 'mysql' or 'postgresql' (or gets it from the Doctrine configuration automatically), to then customize the request according to which engine is used.

[EdouardCourty]

Also, could you not use Doctrine to query the database? It would be much simpler!

[npotier]

Hello @EdouardCourty thank you for reporting this issue.

This is indeed a problem, I will have a look at it this week.

[npotier]

Quick update on this subject

As you can see if you read the code, the hydrate() function load entities from the database, based on the results given by Typesense.

In order to be sure that the hydrated collection is in the same order than the given result by typesense (eg : ids (3,1,6)), we add a SORT instruction with FIELD in our request.

We use it in a native SQL request. it could be done using the beberlei/DoctrineExtensions, to use Doctrine Query Builder, but after some research it appears that FIELD instruction is not available in PostGreSQL.

So, I don't see how to get a sorted collection with PostGreSQL that respects the Typesense order.

I think we should refactor the code in order to sort the entities in PHP.

[EdouardCourty]

Hi, thanks for your reply :)

For now I'm using the RawQuery method and I deserialize the documents myself.
Also I had a few commands idea that could make the bundle even easier to use.

I would be more than happy to contribute to the project!