I built an entity reference in the "views"(see picture 01), then added a "Reference Content fields" in the "content types"(see picture 02). When using the entity reference in the "add content" (see picture 03), I get the error message: "The website encountered an unexpected error. Please try again later.(see picture 04)".
It is normal when using the same method in Drpal 7.41, but wrong in D8 (see picture 04).
I tried the same one in the following D8 versions,(8.0.0, 8.0.2, 8.0.3, 8.1.x-dev) which are all wrong.
I don't know either is my method wrong, or D8 have BUG?
server information:
Local platform:windows server 2008 r2 + xampp-win32-5.6.12-0-VC11(Apache/2.4.16 (Win32) OpenSSL/1.0.1p PHP/5.6.12, MySQL/5.6.26)
remote: justhost Website Hosting (Apache, PHP/5.6.17, MySQL/5.5.42-37.1-log)
--------------------------------------------------------------------
Error information:
The website encountered an unexpected error. Please try again later.
Drupal\Core\Database\IntegrityConstraintViolationException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'nid' in where clause is ambiguous: SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid FROM {node_field_data} node_field_data INNER JOIN {taxonomy_index} taxonomy_index ON node_field_data.nid = taxonomy_index.nid WHERE (( (taxonomy_index.tid = :taxonomy_index_tid) AND (nid IN (:db_condition_placeholder_0)) )AND(( (node_field_data.status = :db_condition_placeholder_1) AND (node_field_data.type IN (:db_condition_placeholder_2)) ))) ORDER BY node_field_data_created DESC; Array ( [:taxonomy_index_tid] => 18 [:db_condition_placeholder_0] => 17 [:db_condition_placeholder_1] => 1 [:db_condition_placeholder_2] => shop ) in Drupal\Core\Database\Connection->handleQueryException() (line 673 of core\lib\Drupal\Core\Database\Connection.php).
Drupal\Core\Database\Statement->execute(Array, Array)
Drupal\Core\Database\Connection->query('SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid
FROM
{node_field_data} node_field_data
INNER JOIN {taxonomy_index} taxonomy_index ON node_field_data.nid = taxonomy_index.nid
WHERE (( (taxonomy_index.tid = :taxonomy_index_tid) AND (nid IN (:db_condition_placeholder_0)) )AND(( (node_field_data.status = :db_condition_placeholder_1) AND (node_field_data.type IN (:db_condition_placeholder_2)) )))
ORDER BY node_field_data_created DESC', Array, Array)
Drupal\Core\Database\Driver\mysql\Connection->query('SELECT node_field_data.created AS node_field_data_created, node_field_data.nid AS nid
FROM
{node_field_data} node_field_data
INNER JOIN {taxonomy_index} taxonomy_index ON node_field_data.nid = taxonomy_index.nid
WHERE (( (taxonomy_index.tid = :taxonomy_index_tid) AND (nid IN (:db_condition_placeholder_0)) )AND(( (node_field_data.status = :db_condition_placeholder_1) AND (node_field_data.type IN (:db_condition_placeholder_2)) )))
ORDER BY node_field_data_created DESC', Array, Array)
Drupal\Core\Database\Query\Select->execute()
Drupal\views\Plugin\views\query\Sql->execute(Object)
Drupal\views\ViewExecutable->execute('entity_reference_1')
Drupal\views\ViewExecutable->render('entity_reference_1')
Drupal\views\Plugin\views\display\EntityReference->execute()
Drupal\views\ViewExecutable->executeDisplay('entity_reference_1', Array)
Drupal\views\Plugin\EntityReferenceSelection\ViewsSelection->validateReferenceableEntities(Array)
Drupal\Core\Entity\Plugin\Validation\Constraint\ValidReferenceConstraintValidator->validate(Object, Object)
Drupal\Core\TypedData\Validation\RecursiveContextualValidator->validateConstraints(Object, '0000000027fa6be3000000005ce56a05', Array)
Drupal\Core\TypedData\Validation\RecursiveContextualValidator->validateNode(Object)
Drupal\Core\TypedData\Validation\RecursiveContextualValidator->validateNode(Object, NULL, 1)
Drupal\Core\TypedData\Validation\RecursiveContextualValidator->validate(Object, NULL, NULL)
Drupal\Core\TypedData\Validation\RecursiveValidator->validate(Object)
Drupal\Core\TypedData\TypedData->validate()
Drupal\Core\Entity\ContentEntityBase->validate()
Drupal\Core\Entity\ContentEntityForm->validateForm(Array, Object)
call_user_func_array(Array, Array)
Drupal\Core\Form\FormValidator->executeValidateHandlers(Array, Object)
Drupal\Core\Form\FormValidator->doValidateForm(Array, Object, 'node_plans_form')
Drupal\Core\Form\FormValidator->validateForm('node_plans_form', Array, Object)
Drupal\Core\Form\FormBuilder->processForm('node_plans_form', Array, Object)
Drupal\Core\Form\FormBuilder->buildForm(Object, Object)
Drupal\Core\Entity\EntityFormBuilder->getForm(Object)
Drupal\node\Controller\NodeController->add(Object)
call_user_func_array(Array, Array)
Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}()
Drupal\Core\Render\Renderer->executeInRenderContext(Object, Object)
Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->wrapControllerExecutionInRenderContext(Array, Array)
Drupal\Core\EventSubscriber\EarlyRenderingControllerWrapperSubscriber->Drupal\Core\EventSubscriber\{closure}()
call_user_func_array(Object, Array)
Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object, 1)
Symfony\Component\HttpKernel\HttpKernel->handle(Object, 1, 1)
Drupal\Core\StackMiddleware\Session->handle(Object, 1, 1)
Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object, 1, 1)
Drupal\page_cache\StackMiddleware\PageCache->pass(Object, 1, 1)
Drupal\page_cache\StackMiddleware\PageCache->handle(Object, 1, 1)
Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object, 1, 1)
Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object, 1, 1)
Stack\StackedHttpKernel->handle(Object, 1, 1)
Drupal\Core\DrupalKernel->handle(Object)
Comments
Comment #2
dawehnerWhat would be really nice is, when you could attach an export of your configured view. This would make it much easier to reproduce the issue.
You can export a single configuration using
/admin/config/development/configuration/single/exportComment #3
cilefen commentedComment #4
joe_carvajalSame problem here with a taxonomy reference in my case.
The error is Uncaught PHP Exception Drupal\\Core\\Database\\IntegrityConstraintViolationException: "SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'tid' in where clause is ambiguous...I added two taxonomy reference fields, and both have a view display to filter its content.
Here are the versions that can affect to this issue:
Comment #5
joe_carvajalComment #6
joe_carvajalSteps to reproduce in a fresh Drupal 8.1.x installation:
1) Create a new vocabulary "test" and add a parent term and at least a child term.
2) Create a new view of taxonomy terms. Create an "entity reference" display and add "Taxonomy term: name" as search field in view format settings. Add the filter "Parent term > 0".
3) In "Basic page" content type, add a "taxonomy term" reference field that allow unlimited values.
4) In "Add Basic page" node form, fill the term reference field and push "Add another item" button or save the content to reproduce the bug.
Comment #7
joe_carvajalComment #8
joe_carvajalSQL error comes for the views query:
SELECT taxonomy_term_field_data.tid AS tid FROM {taxonomy_term_field_data} taxonomy_term_field_data LEFT JOIN {taxonomy_term_hierarchy} taxonomy_term_hierarchy ON taxonomy_term_field_data.tid = taxonomy_term_hierarchy.tid WHERE (( (tid IN (:db_condition_placeholder_0)) )AND(( (taxonomy_term_hierarchy.parent > :db_condition_placeholder_1) )));
Both in brightbai query and mine, there's a where clause without table alias in it. This causes ambiguity when the query joins another table that has a field with the same name.
Looking for the bug in core, I think the problem is in query() function in Entity Reference display plugin: "core/modules/views/src/Plugin/views/display/EntityReference". I'm trying to solve it by adding the table reference.
brightbai, I'm working on it, so I get the issue assignment.
Comment #9
joe_carvajalThis patch worked for me.
Mark the issue as needs review.
Comment #10
brightbai commentedThank you joe_carvajal.
I reference "node ID" , not reference "taxonomy term"
export all of config.
Comment #11
joe_carvajalPassed the tests, I think this patch should be added in 8.0.x and 8.1.x.
Can someone review this, please?
Comment #12
joe_carvajalThank you for reporting this, brightbai.
It's the same problem, both are entity references.
I think the patch in #9 should fix your problem too.
Comment #13
brightbai commentedI'll review it. Thank you.
Comment #14
brightbai commentedI reviewed your patch, I'm glad the most serious problem has been solved. Thanks a lot.
There are 2 problems. Please see the attached pictures(01,02,03). The red box and grey box. The red box is ok now, but not the grey box.
Issues:
1.The red box. shows: The website encountered an unexpected error. Please try again later.(fixed)
2.The grey box.
a.) I added two fields in "views---Entity Reference", "Content: Image" and "Content: Title"(see picture 01), which can be displayed normally in views Preview. You can see the Image and Title. (See the left corner of picture 01)
b.) In "Plans" content type, I added the refered "Entity Reference", but only displayed Title, no Image. (see picture 02)
c.) In "Add Plans" node form , only displayed Title, no Image. (see picture 03)
d.) Attached a picture of drupal 7.41,there are two fields can be displayed normally.
Comment #15
joe_carvajalbrightbai, I understand the second problem, but I think it's a separate issue (probably with 'minor' priority, because it doesn't "break" the site). Can you report this as a new issue? I'll follow it and try to help if I can.
Thank you for the review!
Comment #16
brightbai commentedThank you,I'll report a new issue.
Comment #17
brightbai commentedThe second problem, I reported a new issue. here: https://www.drupal.org/node/2672266
Comment #18
joe_carvajalCan anyone review this, please?
Issue summary can be a bit tricky, but the problem it isn't.
Views generate an ambiguous query when we use an entity reference display. This happens because of lack of table reference in where clause.
Steps to reproduce are in comment #6, and a patch in comment #9.
Thank you!
Comment #19
cilefen commentedBugs go in 8.0.x.
Comment #20
dawehnerBefore moving forward, we should add tests, to ensure that this actually fixes things properly
Comment #21
lendudeAs far as I can tell this will fail with any relationship not made to the same base table. So even one not created by an entity reference.
My steps to reproduce:
- Create a View with Content
- Add a relationship to the Author
- Add a field with the Author relationship (username, uid, whatever..)
- Add a Entity reference display
- Set the related field as search field
- Use this Entity reference display in an entity reference field
- Search
- Fail
This doesn't give exactly the same error as the one described in the issue summary, but I think this both comes from the same issue. The fix in #9 didn't fix this, so I'm curious if this fix does help for the other issues. If not, we may need to combine #9 and add more tests.
Ok, so, fix and test.
Comment #23
lendudeManaged to reproduce the error in the issue summary using the steps in #9. #21 didn't fix that so we indeed need to combine the patches to fix all types of relationships.
So new patch and more tests and simplified the tests I had.
Comment #24
dawehnerNice work, we have a proper test and the actual fix looks perfect.
Comment #25
catchrelated.
IDs
Fixed both on commit and pushed to all three 8.x branches, thanks!