Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.The site uses the Search_API module to organize the search for users by the values of some fields of the entity.
In the Search_API configurations I add necessary custom fields from User entity to aggregated field with fulltext type.
In the view, I use indexed fields to display user fields.
Also, through the settings of the table format, I apply sorting by column.
If you go to the search page (without a search), then sorting by column works correctly.
If you go to the search page by search result and try to sort the search result by any of the columns, an error occurs (below, also attached by file).
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'field_name' in where clause is ambiguous: SELECT t.item_id AS item_id, t.score AS score, t_2.field_phone_number AS field_phone_number, CASE WHEN t.word LIKE :like_w1 THEN 1 ELSE 0 END AS w1 FROM {search_api_db_default_index_text_2} t LEFT OUTER JOIN {search_api_db_default_index} t_2 ON t.item_id = t_2.item_id WHERE (t.word LIKE :db_condition_placeholder_0 ESCAPE '\\') AND (field_name IN (:db_condition_placeholder_1)) GROUP BY item_id, score, w1, t_2.field_phone_number ORDER BY t_2.field_phone_number ASC LIMIT 51 OFFSET 0; Array ( [:db_condition_placeholder_0] => %tes% [:db_condition_placeholder_1] => aggregated_field [:like_w1] => %tes% )
If you add an additional filter criteria in the view (except Search: Fulltext search) then the same error occurs when you try to search or sort.
Why does this error occur and how can I fix it?
| Comment | File | Size | Author |
|---|---|---|---|
| #6 | 3068180-6--db_backend_field_name_column_conflicts.patch | 5.06 KB | drunken monkey |
| |||
| #4 | 3068180-4--db_backend_field_name_column_conflicts--tests_only.patch | 1.61 KB | drunken monkey |
| |||
| #2 | search_api.png | 22.99 KB | alexandr.k |
Comments
Comment #2
alexandr.k CreditAttribution: alexandr.k commentedComment #3
alexandr.k CreditAttribution: alexandr.k commentedComment #4
drunken monkeyUnless I’m very mistaken we never add a clause such as
t_2.field_phone_number AS field_phone_numberto our queries. Is it possible that some other module (contrib or custom) is interfering here?The rest looks like you might be indexing a field with ID
field_name? Is that correct. Seems that leads to a conflict in your case.However, I wrote a test for it (see attached), but it seems without your custom addition this test doesn’t currently fail. Still, please try whether the attached patch (not the tests-only one, of course) resolves your problem! While it doesn’t seem to be necessary, I don’t think it can hurt, either.
PS: The “Assigned” field is for whoever currently works on an issue. If you are not working on a patch, or on some other research that shouldn’t be duplicated, please don’t assign an issue to you. (See the handbook.)
Comment #6
drunken monkeyWell, turns out I was wrong, at least unless one takes a bit more care with the patch.
The attached revision should work.
Comment #7
drunken monkeyI might have managed to reproduce something like this while trying to reproduce #2916534: Search term occuring in multiple fields results in duplicate results in total_rows count.
The exception (in SQLite) was a bit different, though:
PDOException: SQLSTATE[HY000]: General error: 1 ambiguous column name: item_id.The offending column name was in a
GROUP BYclause in that case, but it was the same kind of problem. (Didn’t disappear with this patch, though – I missed thoseGROUP BYclauses.)So might be a good idea to commit this still, just in case.
However, first it would really be good to know whether it helps with your particular problem?
Comment #8
drunken monkeyAny chance you could try out the patch?
Comment #10
drunken monkey