Posted by Scyther on September 10, 2011 at 2:47pm
| Project: | References |
| Version: | 7.x-2.x-dev |
| Component: | Code: node_reference |
| Category: | bug report |
| Priority: | major |
| Assigned: | Unassigned |
| Status: | closed (fixed) |
Issue Summary
Today I updated the views module from a dev version from maybe 5 or 6 september to the latest dev version from 10 september.
Then I got this type of message from all my views that has a node_reference relationship
'Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column \'field_data_field_noderef_frontpage_image.nid\' in \'on clause\''
in views_plugin_query_default->execute() (line 1386 of /drupal/dev-7/sites/all/modules/views/plugins/views_plugin_query_default.inc).The view has a node_reference relationship that is the 'field_noderef_frontpage_image'.
If you need any more info, just ask.
Comments
#1
An export which allows to reproduce the issue would be nice.
#2
Below is the View that created the Exception above and ut uses the views_nivo_slider and is a views content pane display.
I have tried with a page and other displays and different formats and it still gives this error. Only thing that works is if I remove the relationship, so it seems that it is the problem.
Using latest dev version of Views, CTools and References.
<?php
$view = new view;
$view->name = 'frontpage_slideshow';
$view->description = 'Slideshow for articles that has a frontpage image set';
$view->tag = 'frontpage';
$view->base_table = 'node';
$view->human_name = 'Frontpage Slideshow';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'time';
$handler->display->display_options['cache']['results_lifespan'] = '518400';
$handler->display->display_options['cache']['output_lifespan'] = '-1';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['query_comment'] = FALSE;
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '5';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'views_nivo_slider';
$handler->display->display_options['style_options']['imagefield_preset'] = 'frontpage_image';
$handler->display->display_options['style_options']['effect'] = array(
'fade' => 'fade',
);
$handler->display->display_options['style_options']['pauseTime'] = '5000';
$handler->display->display_options['style_options']['controlNavThumbsField'] = 'field_image';
$handler->display->display_options['style_options']['controlNavThumbsIC'] = 'thumbnail';
$handler->display->display_options['style_options']['keyboardNav'] = 'false';
$handler->display->display_options['style_options']['pauseOnHover'] = 'false';
$handler->display->display_options['row_plugin'] = 'nivo_sliderfields';
$handler->display->display_options['row_options']['image_field'] = 'field_image';
$handler->display->display_options['row_options']['title_field'] = 'title';
/* Relationship: Content: Frontpage image (field_noderef_frontpage_image) */
$handler->display->display_options['relationships']['field_noderef_frontpage_image_nid']['id'] = 'field_noderef_frontpage_image_nid';
$handler->display->display_options['relationships']['field_noderef_frontpage_image_nid']['table'] = 'field_data_field_noderef_frontpage_image';
$handler->display->display_options['relationships']['field_noderef_frontpage_image_nid']['field'] = 'field_noderef_frontpage_image_nid';
$handler->display->display_options['relationships']['field_noderef_frontpage_image_nid']['required'] = 0;
$handler->display->display_options['relationships']['field_noderef_frontpage_image_nid']['delta'] = '-1';
/* Field: Content: 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']['label'] = '';
$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']['word_boundary'] = 0;
$handler->display->display_options['fields']['title']['alter']['ellipsis'] = 0;
$handler->display->display_options['fields']['title']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['title']['alter']['trim'] = 0;
$handler->display->display_options['fields']['title']['alter']['html'] = 0;
$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: Content: Image */
$handler->display->display_options['fields']['field_image']['id'] = 'field_image';
$handler->display->display_options['fields']['field_image']['table'] = 'field_data_field_image';
$handler->display->display_options['fields']['field_image']['field'] = 'field_image';
$handler->display->display_options['fields']['field_image']['relationship'] = 'field_noderef_frontpage_image_nid';
$handler->display->display_options['fields']['field_image']['label'] = '';
$handler->display->display_options['fields']['field_image']['alter']['alter_text'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['make_link'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['absolute'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['external'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['replace_spaces'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['trim_whitespace'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['nl2br'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['word_boundary'] = 1;
$handler->display->display_options['fields']['field_image']['alter']['ellipsis'] = 1;
$handler->display->display_options['fields']['field_image']['alter']['strip_tags'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['trim'] = 0;
$handler->display->display_options['fields']['field_image']['alter']['html'] = 0;
$handler->display->display_options['fields']['field_image']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_image']['element_default_classes'] = 1;
$handler->display->display_options['fields']['field_image']['hide_empty'] = 0;
$handler->display->display_options['fields']['field_image']['empty_zero'] = 0;
$handler->display->display_options['fields']['field_image']['hide_alter_empty'] = 0;
$handler->display->display_options['fields']['field_image']['click_sort_column'] = 'fid';
$handler->display->display_options['fields']['field_image']['settings'] = array(
'image_style' => 'frontpage_image',
'image_link' => '',
);
$handler->display->display_options['fields']['field_image']['field_api_classes'] = 0;
/* Sort criterion: Content: Post date */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filter criterion: Content: 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;
$handler->display->display_options['filters']['status']['group'] = 0;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: 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(
'article' => 'article',
);
/* Filter criterion: Content: Frontpage image (field_noderef_frontpage_image) */
$handler->display->display_options['filters']['field_noderef_frontpage_image_nid']['id'] = 'field_noderef_frontpage_image_nid';
$handler->display->display_options['filters']['field_noderef_frontpage_image_nid']['table'] = 'field_data_field_noderef_frontpage_image';
$handler->display->display_options['filters']['field_noderef_frontpage_image_nid']['field'] = 'field_noderef_frontpage_image_nid';
$handler->display->display_options['filters']['field_noderef_frontpage_image_nid']['operator'] = 'not empty';
/* Display: Frontpage Slideshow */
$handler = $view->new_display('panel_pane', 'Frontpage Slideshow', 'panel_pane_1');
$handler->display->display_options['display_description'] = 'Slideshow for the frontpage to display 10 articles that has frontpage image set';
$handler->display->display_options['pane_title'] = 'Frontpage slideshow';
?>
#3
Same here.
On node page: Debug: 'Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column \'field_data_field_owner_company.nid\' in \'on clause\'' in views_plugin_query_default->execute() (line 1386 of C:\wamp\www\test01\sites\all\modules\views\plugins\views_plugin_query_default.inc).
and on views page: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_supplier_company.nid' in 'on clause'
#4
Problem started with this commit http://drupalcode.org/project/views.git/commit/2df60e80e3741dadf6d7b2d1c... from issue #1248454: Relationships should respect real field keys
#5
I have the same problem, my relationships in views produce an error on every page (even for anonymous users), upgrading to major!
<?phpSELECT node_field_data_field_main_ref_comp.nid AS node_field_data_field_main_ref_comp_nid, node.title AS node_title, node.nid AS nid, node.language AS node_language, 'node' AS field_data_field_bedrijf_logo_node_entity_type, 'node' AS field_data_body_node_entity_type, RAND() AS random_field
FROM
{node} node
LEFT JOIN {field_data_field_main_ref_comp} field_data_field_main_ref_comp ON node.nid = field_data_field_main_ref_comp.entity_id AND (field_data_field_main_ref_comp.entity_type = 'node' AND field_data_field_main_ref_comp.deleted = '0')
LEFT JOIN {node} node_field_data_field_main_ref_comp ON field_data_field_main_ref_comp.nid = node_field_data_field_main_ref_comp.nid
LEFT JOIN {field_data_field_main_category} field_data_field_main_category ON node.nid = field_data_field_main_category.entity_id AND (field_data_field_main_category.entity_type = 'node' AND field_data_field_main_category.deleted = '0')
LEFT JOIN {field_data_field_activity_stopdate} field_data_field_activity_stopdate ON node.nid = field_data_field_activity_stopdate.entity_id AND (field_data_field_activity_stopdate.entity_type = 'node' AND field_data_field_activity_stopdate.deleted = '0')
WHERE (( (node.type IN ('activiteit')) AND (node.status = '1') AND (field_data_field_main_category.field_main_category_value IN ('2')) AND (DATE_FORMAT(STR_TO_DATE(field_data_field_activity_stopdate.field_activity_stopdate_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2011-09-12') ))
ORDER BY random_field ASC
?>
error in views: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'field_data_field_main_ref_comp.nid' in 'on clause'
error on pages containing my block from this view:
<?php
Debug:
'Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column \'field_data_field_main_ref_comp.nid\' in \'on clause\''
in views_plugin_query_default->execute() (line 1386 of /var/www/leeghwater/sites/all/modules/views/plugins/views_plugin_query_default.inc).
?>
#6
When I try to add a relation and then filter on it I also get "No valid values found on filter: Field: Field_used_for_relationship."
Greetings Wappie
#7
It's kind of funny that noone created an issue on the references module because this seems to bug in this module.
4 $data[$table][$id_column]['relationship'] = array(1045 'handler' => 'references_handler_relationship',
1046 'base' => 'node',
1047 'field' => 'nid',
1048 'base field' => 'nid',
1049 'label' => $field['field_name'],
1050 'field_name' => $field['field_name'],
1051 );
See 'field' this field DOES NOT exist in the $table, it should be probably 'entity_id'.
yes the error is caused by the update of views, but it was just luck that it worked before.
#8
Yes that seems to be the problem dereine.
Patch to fix the problem for both node_reference and user_reference.
#9
Scyther,
Applied your patch everything works now!!! Thanx! Any chance you can submit this patch to the references project so it gets into their next release?
Thanks Again!
#10
+++ b/user_reference/user_reference.moduleundefined@@ -873,7 +873,7 @@ function user_reference_field_views_data($field) {
+ 'entity_id' => 'uid',
I'm a bit confused, because i never saw this key. Are you sure it is needed here? Did you tryed to drop it?
#11
I got the same error message for a user_reference relationship as of a node_reference relationship so that why I changed 'field' to 'entity_id' in user_reference.module too.
But after some testing what you said about droping that key and value from that array. It seems to work as well.
Here is a new patch to test.
My views skills is far from yours dereine when it comes to views, so you know this best.
#12
This patch looks perfect!
#13
The patch in #11 fixed my issue. Thanks!
Michelle
#14
Whoops, crosspost.
Michelle
#15
Works for me.
#16
Confirming the patch in #11 works. Thanks.
#17
+1 from me too. Can this be applied soon? Thanks :)
#18
Having same problem here, columns 'node_changed_day' and 'node_created_day' (and smaller and bigger time units as well) as order clauses. Trying to create a descending newsfeed.
#19
Seems to be working, no more error message after clearing caches.
#20
I can confirm the bug. Based on http://drupalcode.org/project/views.git/commitdiff/2df60e80e3741dadf6d7b... this patch looks like it might be the right fix, but I need to poke around and see.
#21
I think this is the right patch to work with the current dev version of Views. I don't know what happens if this patch is used with the latest official release of Views, which doesn't have that change. It would be nice if someone could confirm if that works or not. If not, I guess we can add a note on the project page that you need the latest dev version of Views until that change gets into an official release.
#22
I had the same problem, patch in #11 solved it.
#23
I can confirm that applying this patch causes no problems when working with Views 7.x-3.0-rc1 (reference fields in views work as before), and fixes the error when working with the latest Views code.
I also wanted to stick up a version of it with --no-prefix (to work with old drush make).
#24
The last submitted patch, column_not_found_1054_unknown_column-1275096-23.patch, failed testing.
#25
subscribe
#26
Patch in #11 seems to be the one to go with.
#27
#23: column_not_found_1054_unknown_column-1275096-23.patch queued for re-testing.
#28
The last submitted patch, column_not_found_1054_unknown_column-1275096-23.patch, failed testing.
#29
The problem is that the patch is for patch -p0
#30
subscribe.
#31
For some reason I couldn't get that patch to apply. I couldn't see what was wrong with it, but I made another one just in case.
#32
There is no different between the patch in #29 and #31.
#33
Can my problem be related?
every time then i run cron, i have a lot of pages in admin/reports/dblog with the next errors:
Notice: Undefined offset: 1 in DatabaseStatementBase->fetchAllKeyed() (line 2178 of /home/u4586/domains/dev.yogalife.by/includes/database/database.inc).
Notice: Undefined index: uid in DatabaseStatementBase->fetchAllKeyed() (line 2178 of /home/u4586/domains/dev.yogalife.by/includes/database/database.inc).
SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'uid' at row 1
#34
Kervi, no your problem does not seem to be related to this issue.
#35
sorry, thank u.
#36
subscribing. The patch in #31 worked for me.
#37
This has been committed. Thanks everyone!
#38
Great! Thanks
#39
Automatically closed -- issue fixed for 2 weeks with no activity.
#40
I am having this isuue which seems to be rekated:
<?php'Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column \'field_data_field_pic.field_pic_width\' in \'field list\''
?>
in views_plugin_query_default->execute() (line 1380 of /home/clients/websites/w_tikke/public_html/tikke/sites/all/modules/contrib/views/plugins/views_plugin_query_default.inc).
And above:
<?phpNotice: Undefined property: stdClass::$field_pic_width in field_sql_storage_field_storage_load() (line 360 of /home/clients/websites/w_tikke/public_html/tikke/modules/field/modules/field_sql_storage/field_sql_storage.module).
?>
#41
After upgrading Drupal core from 7.7 to 7.9 (I don't know if it's related), when visiting a view with Field view mode and a Relationship, I get the error too:
<?php'Exception: SQLSTATE[42S22]: Column not found: 1054 Unknown column \'field_data_field_sp_genus.field_sp_genus\' in \'field list\''
in views_plugin_query_default->execute() (line 1386 of /opt/lampp/htdocs/drupal/sites/all/modules/views/plugins/views_plugin_query_default.inc).
?>
The header, exposed filters and the pager display well, but the table doesn't. The error only shows when the Fields view mode is chosen. If I change the Table display for Unformatted list, with Teaser view mode, it works. If I switch to Fields view mode, it doesn't work.
It occurs weither or not I put a Node Reference field, weither or not I add a Relationship to the view.
I have the last versions of References (dev) and Views (dev).
There are some related issues in the References module queue: http://drupal.org/node/1347642, and in the Views module queue: http://drupal.org/node/1344350 and http://drupal.org/node/1248454.
#42
Did you cleared your cache before reporing the issue? After the update to references dev you need to clear the cache at least once.
#43
Yes, I cleared it twice!
So today, digging around more, it appears that the error is caused by the Field group views module. It took me a little time to find it out, as I wasn't expected this module could modify in some way my view.
Thanks for your work dereine, and sorry for the inconvenience.
#44
I'm very new to Drupal--I've only been working with it for a few weeks. I'm running into this error, as well.
I'm not sure how to apply the patch is my problem. I visited drupal.org/patch and looked at a number of other posts regarding applying patches, but I have to admit, I wasn't able to wrap my head around applying the patch. At this point, I have the patch file saved to the root views folder, but that is as far as I am. I wonder if someone might be able to instruct me on where to go from here.
Thanks!
Adam
#45
Well if you can't apply the patch use the dev version or wait until the next release comes out.
#46
Updated to the dev version (latest git commit) and the problem's gone for me.
@boomboom3000 the patch is for the References module, not Views. As for how to apply it, well, a Google search for something like "applying patches drupal" should find plenty of resources for you.
#47
I have been receiving this error in connection with term references, which I understand are handled in Drupal core, not the References module. I have updated Views and References to their respective dev versions, to no effect (the error continues to appear). Does anyone have any further insights they can share?
#48
Subscribe
#49
Regarding the patch in #31, should it perhaps be:
- 'field' => 'nid',+ 'field' => $field['field_name'].'_nid',
Instead of:
- 'field' => 'nid',and also:
- 'field' => 'uid',+ 'field' => $field['field_name'].'_uid',
#50
Hi, I am new to Drupal and wanted to enquire how and where do I install this patch?
Thank you
#51
Shanejose, this is explained in detail in the documentation.
#52
The patch did not fix the issue for me; however #49 did the trick.
#53
Patch for node_reference.module according to #49
#54
Let's feed it to the testbot.
#55
The last submitted patch, node_reference.module-1323253445.patch, failed testing.
#56
The patch from #53 was rolled against 7.x-2.0-beta3. I rerolled it against 7.x-2.x.
#57
I attach a patch according to #49.
#58
Disregard patches #53 and #56, they are incomplete.
#59
Patch in #57 is complete and pass tests.
#60
With all due appreciation for the patches provided (and I am sure they have resolved most of the reported problems), I am still experiencing what appears to be a much broader issue. I am receiving the same error - but not just for node references - in fact, this error is randomly arising when I seek to add Views fields involving term references and even text fields. I applied the patch in #57 to see if it might help in some respect; it did not (and I have cleared cache). Does anyone have any suggestions? This is a major problem impeding my use of Views in many respects. I would really appreciate some help! Thanks.
#61
For me the patch #57 worked well on the latest dev-version for d7. THANKS !!!
#62
..
#63
I used the dev version from october 10th with the patch from comment #57 and it works. thanksI have to come back on my statement. It works if a node has been reference, if there is no reference on a node to a node I get the following error:
Warning: array_flip(): Can only flip STRING and INTEGER values! in DrupalDefaultEntityController->load() (line 178 of /includes/entity.inc).Warning: array_flip(): Can only flip STRING and INTEGER values! in DrupalDefaultEntityController->cacheGet() (line 354 of /includes/entity.inc).
Notice: Undefined index: in views_plugin_row_node_view->render() (line 99 of /sites/all/modules/views/modules/node/views_plugin_row_node_view.inc).
Notice: Undefined property: stdClass::$type in _node_extract_type() (line 370 of /modules/node/node.module).
Notice: Undefined property: stdClass::$nid in node_build_content() (line 1359 of /modules/node/node.module).
EntityMalformedException: Missing bundle property on entity of type node. in entity_extract_ids() (line 7405 of /includes/common.inc).
I use a contextual filter on my view to fetch the teaser of nodes that are referenced un my current node.
The above error has been solved by setting the relationship in my view to "require this relationship". Now it works like a charm. The patch was good!
#64
Upgraded to Drupal 7.9 and had this issue, resolved it by applying this patch.
#65
#57 Fixed the issue for me as well. This is ready for maintainers, I think.
#66
+++ b/node_reference/node_reference.moduleundefined
@@ -1044,6 +1044,7 @@ function node_reference_field_views_data($field) {
+ 'field' => $field['field_name'].'_nid',
+++ b/user_reference/user_reference.moduleundefined
@@ -873,6 +873,7 @@ function user_reference_field_views_data($field) {
+ 'field' => $field['field_name'].'_uid',
Actually $id_column should already contain the right database column, so there is no need to specify field here.
#67
So 'field' => $id_column should fix it as well.
#68
As per dereine's #67, committed the attached patch.
Truely sorry for letting this drop by for so long.
I'll try to go round the issue queue and roll a new release in the next couple days.
#69
Also, added a "new maintainer needed" announcement on the project page. It's quite clear now that fgm and I are not putting up (both locked on D6 projects, I'm afraid...)
I'll still try to roll a release asap, though.
#70
Howdy,
I keep running into this issue, and keep finding this page to fix the issue. Thanks for the fix! Can we apply the patch? With a smiley face on top? :D
Thanks for your efforts!
Best regards,
Chris
EDIT: Whoa! It looks like the train has already left the station. I updated to the latest dev version and life is nice. Thanks.
#71
The 7x.x-2.x-dev (21-dec) hasn't got this problem .
Thanks for your efforts!
#72
Latest dev version seems to have cleared this up for me as well. Note: problem wasn't resolved until I cleared caches.
#73
Latest Dev version as of today seems to have broken this again.
#74
@pmflav: there were no recent changes in this area.
Try clearing your caches ?
#75
I confirm existance of this error on 7.x-2.0 from 2011-12-22. Cleared cache a few times and error still appears. I am using Views 7.x-3.0.
This is my query:
SELECT node.title AS node_title, node.nid AS nid, node.created AS node_created, 'node' AS field_data_body_node_entity_type, 'node' AS field_data_field_question_author_node_entity_type, 'node' AS field_data_field_question_source_node_entity_typeFROM
{node} node
LEFT JOIN {field_data_field_professional} field_data_field_professional ON node.nid = field_data_field_professional.entity_id AND (field_data_field_professional.entity_type = 'node' AND field_data_field_professional.deleted = '0')
LEFT JOIN {node} node_field_data_field_professional ON field_data_field_professional.nid = node_field_data_field_professional.nid
LEFT JOIN {field_data_field_answer} field_data_field_answer ON node.nid = field_data_field_answer.entity_id AND (field_data_field_answer.entity_type = 'node' AND field_data_field_answer.deleted = '0')
WHERE (( (node.type IN ('pro_advice')) AND (node.status = '1') AND (field_data_field_answer.field_answer_value IS NULL ) ))
ORDER BY node_created DESC
and this is my error:
Exported view is attached in .txt.
#76
I got this error today with 7.x-2.0-beta3
#67 and #68 can fix it.
#77
The 2.0 release contains #68.
@slashsrm : that's surprising - Maybe try recreating the view ?
#78
@yched: It was my mistake. I had another instance of references in site's folder, but I upgraded just the one in 'all' folder.
Sorry for that.
#79
@slashsrm: OK, thanks for reporting back !
#80
Automatically closed -- issue fixed for 2 weeks with no activity.
#81
Related issue (in Location module...): http://drupal.org/node/1423540