Search in a listing doesn't find values in select field with multiple values

[ofourny]

Search in a listing doesn't find values in select field with multiple values

Details

Question	Answer
Relevant Bolt Version	4.1.13
Install type	Composer install
BC Break	yes/no
PHP version	7.4.15
Web server	Apache

Reproduction

Bug summary

Search on a select field with multiple: true doesn't find results in multi valued content.

Specifics

I setup a content type named "events" with a field "eventtypes" with multiple: true, if I select several values in my field "eventtypes", the search don't show the results containing my value if it is not in the first position.

The "eventtypes" field I'm setting for my "event" content type:

I should have two results (two events containing the "festival" value):

But I only get one :

Query produced by the search :

SELECT content FROM Bolt\Entity\Content content LEFT JOIN content.fields fields_eventtype LEFT JOIN fields_eventtype.translations translations_eventtype WHERE content.contentType = :ct0 AND ((JSON_EXTRACT(translations_eventtype.value, '$[0]') LIKE :eventtype_1 AND fields_eventtype.parent IS NULL AND fields_eventtype.name = :field_eventtype) AND content.status = :status_1) ORDER BY content.publishedAt DESC
ct0: spectacles
eventtype_1: %festivals%
status_1: published
field_eventtype: eventtype

Steps to reproduce

1. Set up a content type with multiple: true and several values in it.
   eventtype: type: select values: [ none, concerts, festivals, spectacles, sport] group: Contenu multiple: true
2. Create two content of your content type, one with only the value you want to find (example : festivals) another with multiple values (example: concerts, festivals, sport)
3. Setup a search in your listing
   {% setcontent results = 'spectacles' where { 'eventtype' : '%festivals%' } printquery %}`

Expected result

Both contents containing the value "festivals"

Actual result

Only contents containing "festivals" selected alone, of in first position.

[ofourny]

Reason of the behavior

JSON_EXTRACT(translations_eventtype.value, '$[0]') expect the match/result will be in first position while it might not be when the value is an array.

instead it should be something like in the screenshot below this for it to work, but JSON_UNQUOTE and JSON_SEARCH seems to not be compatible with doctrine :

Might be hard to come up with a simple fix as wrapJsonFunction() in src/Doctrine/JsonHelper.php is a generic function used in several places.

[stale]

This issue has been automatically marked as stale because it has not had recent activity. Maybe this issue has been fixed in a recent release, or perhaps it is not affecting a lot of people?
It will be closed if no further activity occurs, because we like to keep the issue queue concise and actual.
If you think this issue is still relevant, please let us know. Especially if you’d like to help resolve the issue, either by helping us pinpointing the cause of a bug, or in implementing a fix or new feature.