hello,

when trying to add node: body to my fields list i receive the following error:

user warning: Column 'nid' in field list is ambiguous query: SELECT nid, node.title AS node_title, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE node.type in ('video') LIMIT 0, 10 in /modules/views/includes/view.inc on line 723.

i also receive the same error when attempting add cck fields. currently i am displaying a thumbnail provided by the flashvideo module and the node title, these display properly with no errors. the export of my view is included below. any help someone could provide in solving this would be greatly appreciated- i've been disabling modules for hours assuming that anther module must be the culprit, but have had no luck in resolving the error.

$view = new view;
$view->name = 'Screening_Room';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'nid' => array(
    'label' => '',
    'flashvideo_field' => 'thumbnail',
    'exclude' => 0,
    'id' => 'nid',
    'table' => 'flashvideo',
    'field' => 'nid',
    'relationship' => 'none',
  ),
  'title' => array(
    'label' => 'Title',
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
  'body_1' => array(
    'label' => '',
    'exclude' => 0,
    'id' => 'body_1',
    'table' => 'node_revisions',
    'field' => 'body',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'video' => 'video',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('style_plugin', 'grid');
$handler->override_option('style_options', array(
  'columns' => '4',
  'alignment' => 'horizontal',
));
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('path', 'films');
$handler->override_option('menu', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
  'name' => 'navigation',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'weight' => 0,
));

Comments

nimroda1’s picture

nimroda1 commented

Getting the same error when trying to add taxonomy filter/field to a view

strangeluck’s picture

strangeluck commented

if i set the number of items to display to no limit the error changes slightly to:

user warning: Column 'nid' in field list is ambiguous query: SELECT nid, node.title AS node_title, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE node.type in ('video') in /usr/web/www.nyfa.com/nyfa/screening-room/modules/views/includes/view.inc on line 727.

lgeralds’s picture

lgeralds commented

Here's quick, dirty, nasty fix to includes/views.inc:

676 $replacements = module_invoke_all('views_query_substitutions', $this);
677 $query = str_replace(array_keys($replacements), $replacements, $query);
678 $query = ereg_replace( ' nid', ' node.nid', $query );
679
680 $count_query = 'SELECT COUNT(*) FROM (' . str_replace(array_keys($replacements), $replacements, $count_query) . ') count_alias';
681 $count_query = ereg_replace( ' nid', ' node.nid', $count_query );

Note: doesn't work with DISTINCT.

strangeluck’s picture

strangeluck commented

attempted your fix but upon attempting to add the node body again i receive the following error:

user warning: Column 'nid' in field list is ambiguous query: SELECT nid, node.title AS node_title, node_revisions.body AS node_revisions_body, node_revisions.format AS node_revisions_format FROM node node LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE node.type in ('video') in /usr/web/www.nyfa.com/nyfa/screening-room/modules/views/includes/view.inc on line 729.

lgeralds’s picture

lgeralds commented

Looks like the same error. As if the regex wasn't happening.

My fix might not have been clear. Basically it's just two lines that change "SELECT nid" to "SELECT node.nid"

The first line goes after line 677. It begins "$query = str_replace(array_keys(".
$query = ereg_replace( ' nid', ' node.nid', $query );

The second goes after that begins "$count_query = 'SELECT COUNT(".
$count_query = ereg_replace( ' nid', ' node.nid', $count_query );

strangeluck’s picture

strangeluck commented

you sir, are a hero. your fix was clear and works perfectly, it was simply user error on my part (an inability to copy/paste properly) which prevented it from working prior to this point. many thanks.

merlinofchaos’s picture

merlinofchaos commented
Project: Views (for Drupal 7) » FlashVideo
Version: 6.x-2.2 » 6.x-1.x-dev
Component: node data » Code

This is a bug in the Flash Video module.

In Flash Video:

  $data['flashvideo']['table']['join']['node'] = array(
    'left_table' => 'files',
    'left_field' => 'fid',
    'field' => 'fid',
    'extra' => '(files.filemime <> image/jpeg) AND (files.filemime <> image/png) AND (files.filemime <> image/gif)'
 );

The above does not work because there is no inherent link from 'files' to 'node' in Drupal 6. That probably should be:

  $data['flashvideo']['table']['join']['files] = array(
    'left_table' => 'files',
    'left_field' => 'fid',
    'field' => 'fid',
    'extra' => '(files.filemime <> image/jpeg) AND (files.filemime <> image/png) AND (files.filemime <> image/gif)'
 );

That would also require flash video to provide a relationship for whatever mechanism it's using to link its files to the node table.

drewish’s picture

drewish commented

subscribing...

travist’s picture

travist commented

Thanks for the info Earl.

I will commit this change this weekend. It is good to get advise from Yoda every now and then? .... :)

attheshow’s picture

attheshow commented
Status: Active » Fixed

I had another fix to put in, so I just did this one simultaneously. The fix will be available in the 6.x-1.x-dev release.

attheshow’s picture

attheshow commented
Assigned: Unassigned » attheshow
travist’s picture

travist commented

Thanks again Mark!

attheshow’s picture

attheshow commented

Sure. No problem! :)

internets’s picture

internets commented

This does not appear to be fixed...

I'm using the latest dev versions of flashvideo, views, and cck I get 

user warning: Column 'nid' in field list is ambiguous query: SELECT nid, node.title AS node_title, node_data_field_short_video_description.field_short_video_description_value AS node_data_field_short_video_description_field_short_video_description_value, node.type AS node_type, node.vid AS node_vid FROM node node LEFT JOIN content_type_video node_data_field_short_video_description ON node.vid = node_data_field_short_video_description.vid WHERE (node.status <> 0) AND (node.type in ('video')) LIMIT 0, 3 in /home/example/public_html/sites/all/modules/views/includes/view.inc on line 729

The fix from#5 lgeralds - modifying view.inc did work for me but I need to use the "Distinct" option within views so then I get errors on a different view which uses distinct.

attheshow’s picture

attheshow commented
Assigned: attheshow » Unassigned
Status: Fixed » Needs work

Hmmm... I don't know how to fix this and the change that Earl mentions seems to stop FlashVideo's Views intergration from working altogether, so I'm going to revert back to what we had previously until there is a clear fix for this issue.

rafaelcr’s picture

rafaelcr commented

How can I apply the rough patch mentioned on comment #5 to the new flashvideo_views module structure? (there's now no includes/views.inc to modify and I can't find the query line anywhere)

I found out the error happens only when using the view with a present argument... if no argument is provided, the view works fine.

strangeluck’s picture

strangeluck commented

you're looking for /modules/views/includes/views.inc

attheshow’s picture

attheshow commented
Status: Needs work » Fixed

Just committed fix for this in the development version. Please note that you now need to use the "Distinct: Yes" basic Views setting when using FlashVideo Views.

Since the node id is in the flashvideo table more than once for most FlashVideo nodes, that is the reason we're now seeing the duplicates.

Note: If you've created a view and you're now seeing duplicates, repetitions, or more than one thumbnail or video, this is because you don't have the "Distinct: Yes" setting turned on.

Also, if anyone can figure out a better fix for this that doesn't require the use of the Distinct setting, please post a patch in the issue queue. I know we'll all appreciate it!

oliverpolden’s picture

oliverpolden commented

subscribing...
Any idea when this fix will be in a stable version?

attheshow’s picture

attheshow commented

Probably in the next two weeks. We're trying to get some help updates and a new streaming module also ready in the 1.5 release.

attheshow’s picture

attheshow commented
Assigned: Unassigned » attheshow

Status: Fixed » Closed (fixed)

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