Problem/Motivation

For autocomplete, if one uses fields with multiple values in the associated view the duplicate results will overwrite each after the query which means that the number of results is less than expected. This can be fixed by using aggregation. However, this breaks validation:
In entityreference/views/entityreference_plugin_display.inc:59-62 the variable $id_field is made by joining the base table name and the alias returned by add_field(). However, when the GROUP BY statement is added by the view the query will contain:
node.nid AS nid_1, MIN(node.nid) AS nid
This means that $id_field will be "node.nid_1" which makes the query fail when $id_field is used in the WHERE clause created in line 97 (validation). Furthermore, $id_field cannot be an sql alias in the first place because it is used in a WHERE clause.

Proposed resolution

$id_field should be generated by joining the base table and the base field.

Remaining tasks

Does the patch have any side effects?

CommentFileSizeAuthor
#1 entityreference-fix_sql_error_in_validation_with_aggregation-2404647-1.patch994 bytesdaniel.lundsgaard.skovenborg
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

daniel.lundsgaard.skovenborg’s picture

daniel.lundsgaard.skovenborg CreditAttribution: daniel.lundsgaard.skovenborg commented
FileSize
entityreference-fix_sql_error_in_validation_with_aggregation-2404647-1.patch994 bytes

Here's a patch with the proposed solution.

Stolzenhain’s picture

Stolzenhain CreditAttribution: Stolzenhain commented

Holy cow! Your patch worked like a charm as I almost gave up on the topic.
I wonder why this issue isn't brought up more regularly and your solution proposal seems reasonable.

Then again, reading through the issue lists there seem to be serveral problems with relations, caching/aggregation and validation – not just for the Widget Selector. My next shot at the problem would have been trying out the token solution as mentioned here.

I was testing this against a user reference and can't really see a security-related issue with the approach; so this patch already looks pretty robust to begin with.
It enables amongst others the usage of counters in references which can greatly ease editor workflows.

minax.de’s picture

minax.de CreditAttribution: minax.de commented

As a workaround, it helps adding a "Content: Node ID" field to the views display and excluding it from display. The mentioned assignment of $id_field will then return node.nid and not raise a Column not found: 1054 Unknown column 'node.nid_1' in 'where clause' exception.