I was using the representative node feature to limit my View result to terms that only have content. I originally had this issue, updated to the dev. version, and it was working fine. After a period this feature stopped working. I am not able to fix the problem by changing settings.
My view has a contextual filter of a nid. It has two relationships. One is the taxonomy TIDs associated with the node, and the other is the representative node.
Full error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_data.tid' in 'where clause'
Specific version:
Drupal version : 7.14
version = "7.x-3.3+144-dev"
datestamp = "1337388799"
Full query string:
SELECT taxonomy_term_data_field_data_field_slks.name AS taxonomy_term_data_field_data_field_slks_na, taxonomy_term_data_field_data_field_slks.vid AS taxonomy_term_data_field_data_field_slks_vi, taxonomy_term_data_field_data_field_slks.tid AS taxonomy_term_data_field_data_field_slks_ti, taxonomy_term_data_field_data_field_slks__taxonomy_vocabulary.machine_name AS taxonomy_term_data_field_data_field_slks__t
FROM
{node} node
LEFT JOIN {field_data_field_slks} field_data_field_slks ON node.nid = field_data_field_slks.entity_id AND (field_data_field_slks.entity_type = 'node' AND field_data_field_slks.deleted = '0')
INNER JOIN {taxonomy_term_data} taxonomy_term_data_field_data_field_slks ON field_data_field_slks.field_slks_tid = taxonomy_term_data_field_data_field_slks.tid
INNER JOIN {node} node_taxonomy_term_data ON (SELECT nodeINNER.nid AS nidINNER
FROM
{node} nodeINNER
LEFT JOIN {taxonomy_index} taxonomy_indexINNER ON nodeINNER.nid = taxonomy_indexINNER.nid
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_nodeINNER ON taxonomy_indexINNER.tid = taxonomy_term_data_nodeINNER.tid
WHERE (( (taxonomy_term_data_nodeINNER.tid = taxonomy_term_data.tid ) ))
ORDER BY nodeINNER.title ASC
LIMIT 1 OFFSET 0) = node_taxonomy_term_data.nid
LEFT JOIN {taxonomy_vocabulary} taxonomy_term_data_field_data_field_slks__taxonomy_vocabulary ON taxonomy_term_data_field_data_field_slks.vid = taxonomy_term_data_field_data_field_slks__taxonomy_vocabulary.vid
WHERE (( (node.nid = '113342' ) )AND(( (node.status = '1') AND (node_taxonomy_term_data.type IN ('krames_staywell_article')) )))
ORDER BY taxonomy_term_data_field_data_field_slks_na ASC
LIMIT 5 OFFSET 0
The highlighted parts of the query are where the error is. Replacing the taxonomy_term_data with that alias gives the correct result.
A previous issue is located at http://drupal.org/node/1215468, but the "fix" was to say it was fixed in the dev. version (which I am using). If some enlightenment can be provided as to what the fix actually was, I'm more than happy to dig into the code myself. As it stands currently, I am not familiar enough with the inner working of Views to know what causes what.
Comment | File | Size | Author |
---|---|---|---|
#21 | view.txt | 15.33 KB | Lukas von Blarer |
Comments
Comment #1
steven.wichers CreditAttribution: steven.wichers commentedI tracked the issue to line 299 of views/handlers/views_handler_relationship_groupwise_max.inc.
$this->definition['outer field'] is the wrong value at the point of the str_replace.
Comment #2
trailrunner CreditAttribution: trailrunner commentedDid you actually find the solution to the problem at line 299? I'm also receiving a similar error and would like to get it fixed. Thanks,
- TRT
Comment #3
steven.wichers CreditAttribution: steven.wichers commentedNo, I wound up tweaking the logic behind the view (used a different term reference relationship) and the error was avoided.
Comment #4
capellicI am having the same issue. This is the error I get in the preview:
On the relationship modal, if I check "Generate subquery each time view is run" the preview works, but the view page does not work. Not only is this difference interesting and troubling, it's not as interesting and troubling as the fact that the view works fine in Dev but not in Production. It hasn't always been this way, in fact I had the production view working just last week.
I just cleared views cache. No change. Disabled views cache. No change.
The SQL generated by the view:
Update
I can run the query directly on the DB without a problem.
Comment #5
kmajzlik CreditAttribution: kmajzlik commentedsame here.
Caused by relationship "Representative node". Dev works well, production not (moved with Features).
But i comes only sometimes, on some displays.
Comment #6
ashii CreditAttribution: ashii commentedSame here,
Could anyone suggest me a solution please ????? it's really urgent thank you.!
Comment #7
roxtaz CreditAttribution: roxtaz commentedSubscribing this
This is SQL for Representative node relationship in Views 3 followed by the error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_data.tid' in 'where clause'.
Comment #8
wak CreditAttribution: wak commentedSubscribing.
Same problem here as described in #4.
Comment #9
Dubs CreditAttribution: Dubs commentedSame problem here - I hope there's an answer soon...
Comment #10
Dubs CreditAttribution: Dubs commentedI have a workaround which involved the Views Field View. Grab this module and create a view to return one item and pass the term ID as an argument. This works the same, although I'm sure there is a larger performance hit doing things this way.
Comment #11
vibrasphere CreditAttribution: vibrasphere commentedGot exact error message like OP, I just removed Views (dev version) and replaced it back with 7.x-3.5 and flushed caches and works fine. 7.19 here.
Comment #12
vibrasphere CreditAttribution: vibrasphere commentedLooks like I was wrong. Like I posted my method above it worked, but it works until you run update.php to update any unrelated modules to views, it crashes again and gives me:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_data.tid' in 'where clause'
But again, I deleted Views directory and unpacked the latest dev version (not the recommended one) and flushed the caches and it all works again without any errors.
Since I am not a programmer, but a designer - all that is a mystery to me.
Comment #13
heatherwoz CreditAttribution: heatherwoz commentedI was having this problem too. One view was having trouble pulling the representative node. I had the relevant term field hidden from display on some content types. I enabled token display and made sure the field was displayed as token on all types. The error message went away and my view is working now. Seems like views shouldn't be reliant on the field display settings, but maybe it is. Or maybe this was just coincidence - can anyone reproduce?
Edit: I can confirm that in my case, enabling the relevant field in token display solved the issue, without even touching the view.
Comment #14
behemothhh CreditAttribution: behemothhh commentedHello everybody,
I'm experiencing the same nasty issue. Still no clues on a stable workaround?
Comment #15
edak CreditAttribution: edak commentedDid anyone find a solution? I'm having the same error!
Comment #16
andrewfasano CreditAttribution: andrewfasano commentedI was having the same issue as well. Heatherwoz's suggestion to change the field to display as a token fixed the error.
Comment #17
t7 CreditAttribution: t7 commentedHi, i dont know if it would impact other part of your work, but this solution will omit that error:
Advanced -> Contextual filters
When the filter value is NOT available
Content: Nid -> Provide default value -> Type -> Content ID from URL
When the filter value IS available or a default is provided
Specify validation criteria -> Validator Content Content types -> Image (Your image content type)
Comment #18
FAAREIA CreditAttribution: FAAREIA commentedI'm not having this issue anymore in updated views 3.7 and drupal 7.22
Please confirm.
Comment #19
FAAREIA CreditAttribution: FAAREIA commentedI'm in a production site with views 3.7 and drupal 7.22 and this bugs disappeared. Please re-open if you still have this issue with updated views and core.
Comment #21
Lukas von BlarerI am still having this issue. I have multiple displays using different fields from the representative node relationship. When clearing cache it seems to quite randomly work and break again. Sometimes a part of the displays do work and others don't in various combinations.
I attached an export of my view.
Comment #22
Lukas von BlarerUpdating from 7.x-3.7 to latest dev solved the issue for me.
Comment #23
Lukas von BlarerI posted to early... Still getting the error.
Comment #24
Lukas von BlarerAs stated in #1 this code replaces the placeholder:
But my query looks like this before:
But $this->definition['outer field'] is always this:
:taxonomy_term_data_tid
So, this number is being left over. I did not really understand any logic behind this number. On my view it differs from display to display. Where could this number be coming from? I guess I will have to switch over to a node based view for now...
Comment #25
Elin Yordanov CreditAttribution: Elin Yordanov commentedChecking this option has resolved my problem:
"Generate subquery each time view is run. "
But it is indicated that this option seriously impairs performance.
You can find this option on the configure relationship menu.
Edit: I also entered a namespace for the subquery.
Comment #26
Lukas von BlarerI tried both options without success.
Comment #27
Elin Yordanov CreditAttribution: Elin Yordanov commentedI was also too early to post. The problem occurred again after a few days. But at first time it was gone. So this can be a point to track down the problem itself.
Comment #28
XaviP CreditAttribution: XaviP commentedSame error here: (Drupal 7.22 - Views 7.x-3.7)
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_data.tid1' in 'where clause'
Query:
I have two displays in the same view: the first is correctly running with "taxonomy_term_data.tid", but the second one not with "taxonomy_term_data.tid1"
I need two displays: one for thumbnails and one with slider, both connected with flexslider.
Has anyone find a solution? Thank you.
Comment #29
XaviP CreditAttribution: XaviP commentedI've tried to separate both displays in separate views, with the same result.
I've tried to make the slider view with two views and module views in views (child view with content and context filter (content:tag), parent view with taxonomy terms, and a contextual filter in views in views field [!name] This works with any format (unsorted list, grid, etc...) but not with flexslider format (doesn't recognize the images in the views in views field). Maybe this is usefull for someone who don't use flexslider format view.
Any idea how to repare it? Thankyou.
Comment #30
shrop CreditAttribution: shrop commentedI am having the error:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_data.tid1' in 'where clause'
. I have two views using tid_representative. One works fine and the other has the error.Comment #31
mnapier CreditAttribution: mnapier commentedI've got the same problem using the representative node relationship. I have noticed if I clear the cache (admin menu flush all caches) while on the views edit page then the view starts working, although after a while it stops and I have to go into the edit screen and flush the caches again. I don't know why that works.
It'd be great to have a real fix, clearing the cache every time I notice the problem isn't really a great solution :)
Here's an export of the view incase that helps.
Core is 7.22 and views is 7.x-3.7
Comment #32
mnapier CreditAttribution: mnapier commentedThis definitely is not a permanent fix but I did a quick query alter on the view to remove the extra digit from the query. It works for me until a proper fix comes out.
Comment #33
shrop CreditAttribution: shrop commentedVery clever!
Comment #34
Elin Yordanov CreditAttribution: Elin Yordanov commentedThe workaround on #32 works like charm. Thanks a lot mnapier, you saved my day.
Comment #35
XaviP CreditAttribution: XaviP commentedWow! That works for me! Thank you, mnapier!
Comment #36
13rac1 CreditAttribution: 13rac1 commentedI am getting this error and #1417090: Taxonomy term "Representative node" views with filters and sorts don't work in the same View. Might be helpful for debugging.
Comment #37
13rac1 CreditAttribution: 13rac1 commentedI went down the rabbit hole to find this incorrect placeholder. The path:
The placeholder is created in plugins/views_plugin_query_default.inc
Details: https://api.drupal.org/api/views/plugins%21views_plugin_query_default.in...
The issue is that $placeholders is a static. Every new placeholder, after the first, with the same name is going to have a number at the end of it.
The placeholder will not be incremented if the subqueries are built in separate PHP calls since they are cached. When the Drupal cache is cleared, all displayed will are rebuilt at the same time, so the placeholder is incremented. That's the reason I didn't encounter this issue until I cleared the Drupal cache. "Generate subquery each time view is run" fixes the problem in Preview Only, because only one subquery is generated per PHP call.
Patch shortly.
Comment #38
13rac1 CreditAttribution: 13rac1 commentedOk. The fix for this is heavily related to and uses/modifies code from #1417090: Taxonomy term "Representative node" views with filters and sorts don't work. I am setting this issue to duplicate, because I've fixed(hopefully!) this issue is there. The patch makes the placeholder's name is irrelevant, it is always replaced with the correct value. The patch is in: https://drupal.org/node/1417090#comment-7856511
Comment #39
13rac1 CreditAttribution: 13rac1 commentedPlease review and RTBC the patch in #1417090: Taxonomy term "Representative node" views with filters and sorts don't work. Thanks!
Comment #39.0
13rac1 CreditAttribution: 13rac1 commentedHighlighted first part of mismatch
Comment #40
marfi CreditAttribution: marfi as a volunteer commentedHi, I'm having the same issue.
Exaclty the same behaviour, sometimes it works sometimes not, If I clear cache stops working.
Doesn't matter if views are in different pages or in the same page.
Drupal version: 7.50
View version: 7.x-3.14
Is it possible that for some reason there is a software regression?
Thanks