Multilingual Drupal/CiviCRM setup.
Content type with CiviCRM Event Entity reference field, reference type is Views.
Got an exception:
Exception in CiviCRM Events[civicrm_events]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'civicrm_event.title' in 'WHERE': SELECT "civicrm_event"."id" AS "id", "civicrm_event"."title_en_US" AS "civicrm_event_title"
FROM
"civicrm_event" "civicrm_event"
WHERE ((civicrm_event.created_id = :civicrm_event_created_id)) AND (("civicrm_event"."id" LIKE :db_condition_placeholder_0 ESCAPE '\\') OR ("civicrm_event"."title" LIKE :db_condition_placeholder_1 ESCAPE '\\')); Array
(
[:civicrm_event_created_id] => created_id
[:db_condition_placeholder_0] => %aa%
[:db_condition_placeholder_1] => %aa%
) Create a content type with CiviCRM Event entity reference, reference type Views. In views, add event ID, Title, Event Creator as autocomplete fields
Got it "fixed" by AI in the /civicrm_entity/src/Hook/QueryHooks.php file but since I don't quite understand what its code does, would be nice if someone more knowledgeable had a look.
| Comment | File | Size | Author |
|---|---|---|---|
| #3 | 3580224.patch | 2.44 KB | yurg |
Show commandsStart within a Git clone of the project using the version control instructions.
Or, if you do not have SSH keys set up on git.drupalcode.org:
changes
Comments
Comment #2
yurg commentedComment #3
yurg commentedComment #4
markusa commentedThanks for reporting and providing a patch. We'll review the patch and work on getting the fix into the module
Comment #5
sourav_paulComment #6
sourav_paulI've started working on it.
Comment #8
sourav_paul@Yurg Thanks for your patch.
Root Cause of the prolem is:
On a multilingual CiviCRM site, the event title is not stored in one generic place called title. It is stored in language-specific places, like title_en_US.
When someone used the autocomplete field, Drupal built a search that was basically:
look for a match in the event ID
or look for a match in the event title
The problem was that civicrm_entity only corrected the simple, obvious parts of that search. It did not go inside the nested “ID or title” search group that autocomplete creates. So the database was still asked to search title instead of title_en_US.
In plain terms: the system knew the translated title exists, but when autocomplete searched, it kept looking in the old/wrong drawer.
There was also a second problem: part of the code was changing a temporary copy of the search conditions instead of the real query, so even some fixes would not reliably stick in the final SQL. That logic lives in QueryHooks.php (line 41).
What We Fixed
We updated the code in QueryHooks.php (line 52) so it now:
updates the real query, not a copy
walks through the full search structure, including nested autocomplete groups
changes multilingual fields like civicrm_event.title to the correct language-specific version like civicrm_event.title_en_US
avoids adding the language suffix twice if it is already there
So now, when autocomplete searches on a multilingual site, it looks in the correct translated title field.
Thanks...
Comment #9
yurg commented@sourav_paul - right, this is more or less the same notes my AI make regarding the issue. Look forward to get it merged.
Comment #10
dsdeiz commentedYeah, I am able to reproduce this as well. Able to create a new PR at https://github.com/eileenmcnaughton/civicrm_entity/?
Comment #11
sourav_paulThanks @dsdeiz for acknowledge the issue.
Raising MR for github.
Comment #12
sourav_paul@dsdeiz Here is Github PR: https://github.com/eileenmcnaughton/civicrm_entity/pull/546
Thanks.
Comment #13
yurg commentedTested, works in drupal 11.3.6 and civicrm_entity 4.0.3. Thank you.
Comment #14
sourav_paulThanks @yurg for the response,
It would be helpful to users, If you merged & published a version with the fix.
Comment #16
markusa commentedThis is merged and released in https://www.drupal.org/project/civicrm_entity/releases/4.0.4