Contrib.social

Views query alter for fields stored in shared tables and accessed through a relationship is not working correctly

Open on Drupal.org →
Created on 28 October 2023, over 1 year ago
Updated 29 May 2024, 8 months ago

Problem/Motivation

The root case for this problem is the same as 🐛 Views query alter for fields stored in dedicated tables is not working properly Fixed , where we added the possibility of using a left_formula instead of left_field in views joins.

Steps to reproduce

This example will use nodes and media entities:
1. Using the standard profile, enable the Media and the Content Translation modules
2. Add a second language to the site, and ensure that both nodes and media entities are translatable
3. Add a media reference field to the Article content type
4. Create a view of Content, add a relationship using the media reference field
5. Add a filter on a base field of the Media entity type, for example Authored by and ensure that it's using the media field relationship
6. Run the view inside a workspace and observe the SQL error coming from the last LEFT JOIN of the views query:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'media_field_revision.langcode' in 'on clause'

LEFT JOIN {media_field_revision} "media_field_data_node__field_media__media_field_revision" ON COALESCE(media_field_data_node__field_media__workspace_association.target_entity_revision_id, media_field_data_node__field_media.vid) = media_field_data_node__field_media__media_field_revision.vid AND (media_field_revision.langcode = media_field_data_node__field_media.langcode)

The second condition of the join (AND (media_field_revision.langcode = media_field_data_node__field_media.langcode)) is using the base table name for the left table (media_field_revision), instead of prefixing it with the relationship name (it should be media_field_data_node__field_media__media_field_revision).

Proposed resolution

Use the left_formula join configuration setting, which allows us to specify the left_table of the join, just like we did in 🐛 Views query alter for fields stored in dedicated tables is not working properly Fixed .

Remaining tasks

Review, write tests.

User interface changes

Nope.

API changes

Nope.

Data model changes

Nope.

Release notes snippet

Nope.

🐛 Bug report
Status

Fixed

Version

10.3 ✨

Component
Workspaces  →

Last updated about 18 hours ago

No maintainer
Created by

🇷🇴Romania amateescu

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Merge Requests

Comments & Activities

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024