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

brightbai created an issue. See original summary.

dawehner’s picture

What 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/export

cilefen’s picture

Title: Views Entity Reference, Error Info: The website encountered an unexpected error. Please try again later. » IntegrityConstraintViolationException using an entity reference in a view
joe_carvajal’s picture

Same 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:

  • PHP 7.0.3
  • Drupal 8.0.3
joe_carvajal’s picture

StatusFileSize
new7.45 KB
joe_carvajal’s picture

Steps 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.

joe_carvajal’s picture

Version: 8.0.3 » 8.1.x-dev
joe_carvajal’s picture

SQL 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.

joe_carvajal’s picture

Component: entity system » views.module
Status: Active » Needs review
StatusFileSize
new1.12 KB

This patch worked for me.
Mark the issue as needs review.

brightbai’s picture

Thank you joe_carvajal.
I reference "node ID" , not reference "taxonomy term"
export all of config.

joe_carvajal’s picture

Passed the tests, I think this patch should be added in 8.0.x and 8.1.x.
Can someone review this, please?

joe_carvajal’s picture

Thank 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.

brightbai’s picture

I'll review it. Thank you.

brightbai’s picture

StatusFileSize
new89 KB

I 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.

joe_carvajal’s picture

brightbai, 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!

brightbai’s picture

Thank you,I'll report a new issue.

brightbai’s picture

The second problem, I reported a new issue. here: https://www.drupal.org/node/2672266

joe_carvajal’s picture

Can 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!

cilefen’s picture

Version: 8.1.x-dev » 8.0.x-dev
Issue tags: -views, -Entity Reference

Bugs go in 8.0.x.

dawehner’s picture

Status: Needs review » Needs work
Issue tags: +Needs tests

Before moving forward, we should add tests, to ensure that this actually fixes things properly

lendude’s picture

As 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.

lendude’s picture

Title: IntegrityConstraintViolationException using an entity reference in a view » IntegrityConstraintViolationException using an entity reference in a view with a entity reference display
Assigned: joe_carvajal » Unassigned
StatusFileSize
new5.28 KB
new5.95 KB
new5.95 KB
new5.95 KB

Managed 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.

dawehner’s picture

Status: Needs review » Reviewed & tested by the community

Nice work, we have a proper test and the actual fix looks perfect.

catch’s picture

Status: Reviewed & tested by the community » Fixed
  1. +++ b/core/modules/views/src/Tests/Plugin/DisplayEntityReferenceTest.php
    @@ -129,6 +143,59 @@ public function testEntityReferenceDisplay() {
    +    // Test that the search still works with the ralated field.
    

    related.

  2. +++ b/core/modules/views/src/Tests/Plugin/DisplayEntityReferenceTest.php
    @@ -129,6 +143,59 @@ public function testEntityReferenceDisplay() {
    +    // Add ids to trigger validation.
    

    IDs

Fixed both on commit and pushed to all three 8.x branches, thanks!

  • catch committed 0ec00a5 on 8.2.x
    Issue #2669590 by Lendude, joe_carvajal:...

  • catch committed 89ba381 on 8.1.x
    Issue #2669590 by Lendude, joe_carvajal:...

  • catch committed 6af13e3 on 8.0.x
    Issue #2669590 by Lendude, joe_carvajal:...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.