Views Node/Node/Media relationship [#1063208]

I have a CT Attraction with a Nodereference field ( field_attraction_video ) , and a CT Video with Multimedia asset field ( field_video , type of asset is YouTube video ).

I'm trying to display all videos from an Attraction, in a Block. The Video nodes are created with YouTube videos associated with them, and then in an Attraction, I reference those Video nodes through the Nodereference field.

I've added a Filter Node Published & Node Type Attraction, and a Argument Node ID from URL. Also have 2 relationships. "Fields: field_attraction_video - nid" and "File Usage: File" ( which uses the first relationship ).

Upon trying it out, I get an error Unknown column \'file_usage.type\' in \'on clause\''. Upon further inspection of the Query, I noticed that an alias is not used in my SQL.

The generated SQL :
SELECT node_field_data_field_attraction_video.nid AS node_field_data_field_attraction_video_nid, node_field_data_field_attraction_video.title AS node_field_data_field_attraction_video_title, node_field_data_field_attraction_video.language AS node_field_data_field_attraction_video_language, file_managed_file_usage.filename AS file_managed_file_usage_filename, 'node' AS field_data_field_video_node_entity_type
FROM
node node
LEFT JOIN field_data_field_attraction_video field_data_field_attraction_video ON node.nid = field_data_field_attraction_video.entity_id AND (field_data_field_attraction_video.entity_type = 'node' AND field_data_field_attraction_video.deleted = 0)
LEFT JOIN node node_field_data_field_attraction_video ON field_data_field_attraction_video.field_attraction_video_nid = node_field_data_field_attraction_video.nid
LEFT JOIN file_usage node_field_data_field_attraction_video__file_usage ON node_field_data_field_attraction_video.nid = node_field_data_field_attraction_video__file_usage.id AND file_usage.type = 'node'
INNER JOIN file_managed file_managed_file_usage ON node_field_data_field_attraction_video__file_usage.fid = file_managed_file_usage.fid
WHERE (( (node.status = '1') AND (node.type IN ('attraction')) AND (node.language IN ('en')) AND (node.nid = '12') ))AND (node.language='en')

The - I assume - correct SQL :
..
LEFT JOIN file_usage node_field_data_field_attraction_video__file_usage ON node_field_data_field_attraction_video.nid = node_field_data_field_attraction_video__file_usage.id AND node_field_data_field_attraction_video__file_usage.type = 'node'
..

The second SQL works, and returns exactly what I need. I don't really know where that file_usage.type condition comes from.

Media 7.x-1.0-beta3
Views 7.x-3.x-dev
References 7.x-2.x-dev

My exported View

$view = new view;
$view->name = 'video';
$view->description = '';
$view->tag = '';
$view->base_table = 'node';
$view->human_name = 'video';
$view->core = 7;
$view->api_version = '3.0-alpha1';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Defaults */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'none';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: Fields: field_attraction_video (field_attraction_video) - nid */
$handler->display->display_options['relationships']['field_attraction_video_nid']['id'] = 'field_attraction_video_nid';
$handler->display->display_options['relationships']['field_attraction_video_nid']['table'] = 'field_data_field_attraction_video';
$handler->display->display_options['relationships']['field_attraction_video_nid']['field'] = 'field_attraction_video_nid';
$handler->display->display_options['relationships']['field_attraction_video_nid']['required'] = 0;
/* Relationship: File Usage: File */
$handler->display->display_options['relationships']['node_to_file']['id'] = 'node_to_file';
$handler->display->display_options['relationships']['node_to_file']['table'] = 'file_usage';
$handler->display->display_options['relationships']['node_to_file']['field'] = 'node_to_file';
$handler->display->display_options['relationships']['node_to_file']['relationship'] = 'field_attraction_video_nid';
$handler->display->display_options['relationships']['node_to_file']['required'] = 1;
/* Field: Fields: field_video */
$handler->display->display_options['fields']['entity_id']['id'] = 'entity_id';
$handler->display->display_options['fields']['entity_id']['table'] = 'field_data_field_video';
$handler->display->display_options['fields']['entity_id']['field'] = 'entity_id';
$handler->display->display_options['fields']['entity_id']['relationship'] = 'field_attraction_video_nid';
$handler->display->display_options['fields']['entity_id']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['entity_id']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['entity_id']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['entity_id']['alter']['trim'] = 0;
$handler->display->display_options['fields']['entity_id']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['entity_id']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['entity_id']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['entity_id']['alter']['html'] = 0;
$handler->display->display_options['fields']['entity_id']['element_label_colon'] = 1;
$handler->display->display_options['fields']['entity_id']['element_default_classes'] = 1;
$handler->display->display_options['fields']['entity_id']['hide_empty'] = 0;
$handler->display->display_options['fields']['entity_id']['empty_zero'] = 0;
$handler->display->display_options['fields']['entity_id']['click_sort_column'] = 'fid';
$handler->display->display_options['fields']['entity_id']['type'] = 'media_original';
/* Field: Node: Title */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['relationship'] = 'field_attraction_video_nid';
$handler->display->display_options['fields']['title']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['title']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['title']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$handler->display->display_options['fields']['title']['element_label_colon'] = 1;
$handler->display->display_options['fields']['title']['element_default_classes'] = 1;
$handler->display->display_options['fields']['title']['hide_empty'] = 0;
$handler->display->display_options['fields']['title']['empty_zero'] = 0;
$handler->display->display_options['fields']['title']['link_to_node'] = 1;
/* Field: File: Name */
$handler->display->display_options['fields']['filename_1']['id'] = 'filename_1';
$handler->display->display_options['fields']['filename_1']['table'] = 'file_managed';
$handler->display->display_options['fields']['filename_1']['field'] = 'filename';
$handler->display->display_options['fields']['filename_1']['relationship'] = 'node_to_file';
$handler->display->display_options['fields']['filename_1']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['filename_1']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['filename_1']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['filename_1']['alter']['trim'] = 0;
$handler->display->display_options['fields']['filename_1']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['filename_1']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['filename_1']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['filename_1']['alter']['html'] = 0;
$handler->display->display_options['fields']['filename_1']['element_label_colon'] = 1;
$handler->display->display_options['fields']['filename_1']['element_default_classes'] = 1;
$handler->display->display_options['fields']['filename_1']['hide_empty'] = 0;
$handler->display->display_options['fields']['filename_1']['empty_zero'] = 0;
$handler->display->display_options['fields']['filename_1']['link_to_file'] = 0;
/* Argument: Node: Nid */
$handler->display->display_options['arguments']['nid']['id'] = 'nid';
$handler->display->display_options['arguments']['nid']['table'] = 'node';
$handler->display->display_options['arguments']['nid']['field'] = 'nid';
$handler->display->display_options['arguments']['nid']['default_action'] = 'default';
$handler->display->display_options['arguments']['nid']['style_plugin'] = 'default_summary';
$handler->display->display_options['arguments']['nid']['default_argument_type'] = 'node';
$handler->display->display_options['arguments']['nid']['break_phrase'] = 0;
$handler->display->display_options['arguments']['nid']['not'] = 0;
/* Filter: Node: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = '1';
/* Filter: Node: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
'attraction' => 'attraction',
);
/* Filter: Node translation: Language */
$handler->display->display_options['filters']['language']['id'] = 'language';
$handler->display->display_options['filters']['language']['table'] = 'node';
$handler->display->display_options['filters']['language']['field'] = 'language';
$handler->display->display_options['filters']['language']['value'] = array(
'***CURRENT_LANGUAGE***' => '***CURRENT_LANGUAGE***',
);

/* Display: Attraction Videos */
$handler = $view->new_display('block', 'Attraction Videos', 'video_attraction');
$translatables['video'] = array(
t('Defaults'),
t('more'),
t('Apply'),
t('Reset'),
t('Sort By'),
t('Asc'),
t('Desc'),
t('field_attraction_video'),
t('File'),
t('field_video'),
t('Title'),
t('Name'),
t('All'),
t('Attraction Videos'),
);

Comment	File	Size	Author
#15	file_usage_relationship-1063208-15.patch	527 bytes	mjanouch
#10	file_usage_relationship-1063208-10.patch	546 bytes	WorldFallz
#2	1063208-file_usage-join-table.patch	2.9 KB	dawehner

Comments

Comment #1

JacobSingh commented 16 February 2011 at 13:36

Project:	D7 Media	» Views (for Drupal 7)
Version:	7.x-1.0-beta3	» 7.x-3.x-dev

Hi,

This is interesting to see someone doing something so complex w/ Media. I don't think, looking at the query that this is a media bug. IT /could/ be, but I think you'll have to debug how the view is constructed. Media doesn't have any views support built in, so unlikely a problem in the media code. If you figure out what it is though, I'd be interested to know.

Best,
Jacob

Comment #2

dawehner

German

commented 16 February 2011 at 14:05

Status:

Active

» Needs review

Status	File	Size
new	1063208-file_usage-join-table.patch	2.9 KB

Can you try out this patch?

Comment #3

vodde83 commented 16 February 2011 at 14:17

Thx dereine. The patch does indeed resolve my issue !

Comment #4

dawehner

German

commented 16 February 2011 at 14:37

Status:

Needs review

» Fixed

Thanks for testing this patch

Comment #5

2 March 2011 at 14:41

Status:

Fixed

» Closed (fixed)

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

Comment #6

WorldFallz commented 22 November 2011 at 12:54

Status:

Closed (fixed)

» Active

I'm trying to update the views integration code for the donwload_count module and I'm getting this exact same error for the exact same reason (the query isn't using the file_usage.type alias).

Here's the query I'm getting:

SELECT download_count.dcid AS dcid
FROM 
{download_count} download_count
LEFT JOIN {file_managed} file_managed_download_count ON download_count.fid = file_managed_download_count.fid
LEFT JOIN {file_usage} file_managed_download_count__file_usage ON file_managed_download_count.fid = file_managed_download_count__file_usage.fid
LEFT JOIN {node} node_file_usage ON file_managed_download_count__file_usage.id = node_file_usage.nid AND file_usage.type = 'node'

So far I'm only providing a relationship for the file_managed table (I chain the file_usage relationship on top of that in the view that produces the error):

  // fid
  $data['download_count']['fid'] = array(
    'title' => t('File'),
    'help' => t('The file information of the downloaded file.'),
    'relationship' => array(
      'base' => 'file_managed',
      'base field' => 'fid',
      'handler' => 'views_handler_relationship',
      'label' => t('File'),
    ),
  );

I have no clue where to begin to debug (views or my views.inc)-- any pointers would be greatfully appreciated.

Comment #7

WorldFallz commented 23 November 2011 at 18:12

making a similar change to the file_usage relationship:

changing system.views.inc line 255 from:

'extra' => array(array('table' => 'file_usage', 'field' => 'type', 'operator' => '=', 'value' => 'node')),

'extra' => array(array('field' => 'type', 'value' => 'node')),

Fixes the error message, but then the related node info (ie title, post date, etc) doesn't show. I'm not sure where else to troubleshoot this.

Comment #8

dawehner

German

commented 15 December 2011 at 22:48

This seems to make sense, please provide a patch...

Comment #9

dawehner

German

commented 15 December 2011 at 22:52

This seems to make sense, please provide a patch...

Comment #10

WorldFallz commented 16 December 2011 at 12:17

Status:

Active

» Needs work

Status	File	Size
new	file_usage_relationship-1063208-10.patch	546 bytes

Here's a patch, but as I said, though the error is gone, the relationship still doesn't work. I've spent a lot of time trying to track this down, but I just can't figure out why.

Comment #11

WorldFallz commented 16 December 2011 at 19:47

Status:

Needs work

» Needs review

ooops... lets see what testbot has to say.

Comment #12

dawehner

German

commented 17 December 2011 at 03:02

Mh this doesn't really work as there are many parts of views not tested, and the testbot is not automatically added to each patch.

Comment #13

dawehner

German

commented 16 January 2012 at 19:33

Status:

Needs review

» Fixed

This change looks perfect! Committed to 7.x-3.x

Comment #14

30 January 2012 at 19:40

Status:

Fixed

» Closed (fixed)

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

Comment #15

mjanouch commented 9 February 2012 at 09:42

Status:

Closed (fixed)

» Active

Status	File	Size
new	file_usage_relationship-1063208-15.patch	527 bytes

It doesn't work correctly with the patch above. It produces query like this:

SELECT file_managed.filename AS file_managed_filename, file_managed.uri AS file_managed_uri
FROM 
{file_managed} file_managed
LEFT JOIN {file_usage} file_usage ON file_managed.fid = file_usage.fid
LEFT JOIN {node} node_file_usage ON file_usage.id = node_file_usage.nid AND node_file_usage.type = 'node'
WHERE (( (node_file_usage.nid = '499' ) ))
LIMIT 10 OFFSET 0

Which is wrong in the second join as node_file_usage.type = 'node' (that is node.type = 'node') doesn't make sense and the result is always empty. It should be the type column in file_usage table.

Here is correction patch.

Comment #16

dawehner

German

commented 9 February 2012 at 09:48

Status:

Active

» Closed (duplicate)

This looks the same as #1409454: File Usage: Content relationship broken

Views Node/Node/Media relationship

Comments

Comment #1

Comment #2

Comment #3

Comment #4

Comment #5

Comment #6

Comment #7

Comment #8

Comment #9

Comment #10

Comment #11

Comment #12

Comment #13

Comment #14

Comment #15

Comment #16

News items

Our community

Documentation

Drupal code base

Governance of community