Contrib.social

Latest revision views filter causes 'Base table or view not found'

Open on Drupal.org →
Created on 21 April 2022, over 2 years ago
Updated 4 April 2024, 10 months ago

Problem/Motivation

The latest revision views filter produces invalid SQL queries in some cases.

Steps to reproduce

  1. Create a view targeting paragraphs
  2. Add a reverse entity relationship with a node paragraphs field
  3. Add a latest revision filter to that relationship

Example raw SQL query, produced from the view as described above:

SELECT
	paragraph__field_day.field_day_value AS paragraph__field_day_field_day_value,
	paragraphs_item_field_data.id AS id,
	paragraphs_item_field_data.langcode AS paragraphs_item_field_data_langcode,
	field_content_paragraphs_item_field_data.nid AS field_content_paragraphs_item_field_data_nid
FROM
	paragraphs_item_field_data paragraphs_item_field_data
	LEFT JOIN node__field_content node__field_content ON paragraphs_item_field_data.id = node__field_content.field_content_target_id
		AND node__field_content.deleted = '0'
	LEFT JOIN node_field_data field_content_paragraphs_item_field_data ON node__field_content.entity_id = field_content_paragraphs_item_field_data.nid
	# Below it goes wrong, since field_content_paragraphs_item_field_data is a table alias
	LEFT JOIN field_content_paragraphs_item_field_data field_content_paragraphs_item_field_data__field_content_paragraphs_item_field_data ON field_content_paragraphs_item_field_data.nid = field_content_paragraphs_item_field_data__field_content_paragraphs_item_field_data.nid
		AND field_content_paragraphs_item_field_data__field_content_paragraphs_item_field_data.vid > field_content_paragraphs_item_field_data.vid
	LEFT JOIN paragraph__field_day paragraph__field_day ON paragraphs_item_field_data.id = paragraph__field_day.entity_id
		AND(paragraph__field_day.deleted = '0'
			AND paragraph__field_day.langcode = paragraphs_item_field_data.langcode)
WHERE (paragraphs_item_field_data.parent_id = '1868')
AND((paragraphs_item_field_data.status = '1')
AND(paragraphs_item_field_data.type IN('festival_day'))
AND(field_content_paragraphs_item_field_data__field_content_paragraphs_item_field_data.nid IS NULL))
ORDER BY
	paragraph__field_day_field_day_value ASC

Proposed resolution

Use the table name instead of the alias when joining.

🐛 Bug report
Status

Needs work

Version

11.0 🔥

Component
Views  →

Last updated about 8 hours ago

Created by

🇧🇪Belgium dieterholvoet Brussels

Live updates comments and jobs are added and updated live.
  • Needs tests

    The change is currently missing an automated test that fails when run with the original code, and succeeds when the bug has been fixed.

Sign in to follow issues

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

  • Comment over 1 year ago →
    🇳🇱Netherlands ricovandevin

    Thanks for the initial work on this. In our case $relationship->table gives us the base table of the view, which is not what we need. So I'm afraid that the MR is not yet good enough to serve as a generic fix for the issue.

  • Comment over 1 year ago →
    🇳🇱Netherlands ricovandevin

    For us $relationship->definition['base'] seems to do the trick. Not sure (yet) whether that is a generic solution though.

  • Comment 10 months ago →
    🇳🇱Netherlands seanB Netherlands

    Attached is an updated patch for Drupal 10.2.

  • First commit to issue fork.
contrib.social Blog FAQ Discussions
Production build 0.71.5 2024