The Taxonomy and User Representative Node (GroupwiseMax) using a RepresentativeView does not work because the existing code generates SQLSTATE[42000]: Syntax error or access violation and SQLSTATE[HY093]: Invalid parameter number: no parameters were bound.
To reproduce:
- Install Drupal 8.
- Edit the "People" View.
- Add a Representative Node Relationship.
- Select content View as the Representative view.
- Click Apply (All Displays).
- You'll see the error below in the Preview section
Result:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '***CURRENT_USER*** AND ***CURRENT_USER*** <> 0 AND ***VIEW_OWN_UNPUBLISHED_NODES' at line 3: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {users_field_data} users_field_data LEFT JOIN {node_field_data} node_field_data_users_field_data ON (SELECT node_field_dataINNER.nid AS nidINNER FROM {node_field_data} node_field_dataINNER INNER JOIN {users_field_data} users_field_data_node_field_dataINNER ON "node_field_dataINNER".uid = "users_field_data_node_field_dataINNER".uid LEFT JOIN {users_field_data} users_field_data_node_field_data_1INNER ON "node_field_dataINNER".uid = "users_field_data_node_field_data_1INNER".uid WHERE (("users_field_data_node_field_data_1INNER".uid = users_field_data.uid )) AND (("node_field_dataINNER".status = "1 OR (node_field_dataINNER".uid = ***CURRENT_USER*** AND ***CURRENT_USER*** <> 0 AND ***VIEW_OWN_UNPUBLISHED_NODES*** = 1) OR ***BYPASS_NODE_ACCESS*** = 1)) ORDER BY node_field_dataINNER.changed DESC LIMIT 1 OFFSET 0) = node_field_data_users_field_data.nid WHERE (users_field_data.default_langcode = :db_condition_placeholder_0) AND (users_field_data.uid != :db_condition_placeholder_1)) subquery; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 0 )
Apply this patch, clear the cache, and refresh the View edit page to see it work. Add a node if you want to see it get data.
Tests are needed for this and it seems GroupwiseMax in general (without the Representative View.) Maybe additional discussion is needed? Maybe there is a better way to replace the **CORRELATED** placeholder? Maybe tests should be a different issue?
This issue also exists in Views for Drupal 7. I created the patch for D7 Views over a year ago here: https://www.drupal.org/node/1417090 #35 with a recent update in #53. The patch in #35 has ~10 RTBC's.
Comment | File | Size | Author |
---|---|---|---|
#66 | 2379423-66.patch | 5.69 KB | neclimdul |
#64 | 2379423-64.patch | 5.7 KB | neclimdul |
#61 | Before patch.png | 1.01 MB | nikhilraut |
#58 | interdiff_55-58.txt | 616 bytes | pooja saraah |
#58 | 2379423-58.patch | 1.1 KB | pooja saraah |
Issue fork drupal-2379423
Show commands
Start within a Git clone of the project using the version control instructions.
Or, if you do not have SSH keys set up on git.drupalcode.org:
Comments
Comment #1
13rac1 CreditAttribution: 13rac1 commentedComment #2
13rac1 CreditAttribution: 13rac1 commentedComment #3
xjmThanks @eosrei for the D8 patch!
Comment #4
xjmComment #5
dawehnerAfaik #1792848: Groupwise Max relationship of term ->node is broken used to be a duplicate of that.
Comment #6
mradcliffeThe query fails on PostgreSQL because ON statement table aliases aren't escaped so __INNER becomes "inner". This is going to be very difficult to fix because condition is a string so it would be very complex to create any sort of string replacement. I don't like the idea of doing that anywhere - in the driver or in the views module. Ideally condition should not be a string anymore, but I think it is way too late for a major API change from Drupal 7.
I looked at simply not using a case-sensitive alias in this case, but I was still getting an invalid FROM error in PostgreSQL for the following query.
I'm kind of at an impasse here. I don't think there's any good code that can fix the issue with join conditions without a significant API change, but there really is no other way to fix the issue.
Comment #7
jaredsmith CreditAttribution: jaredsmith commentedRight -- to get rid of that error, you have to do the JOIN on the
taxonomy_term_field_data
before referencing that table in the subquery. In other words, do the INNER JOIN ontaxonomy_term_field_data
before the LEFT JOIN onnode
. Unfortunately, I have no idea where to look to switch the order of those JOINs.Comment #8
mgiffordNeeds re-roll.
Comment #9
Anonymous (not verified) CreditAttribution: Anonymous commentedrenewal this patch for 8.03
Comment #10
mgiffordGo bots...
Comment #11
hctomI just gave this patch a try, but it unfortunately does not solve my issues, because I have a slightly different view setup. My term is a relationship as well, wich is then the relationship for the representative node.
This results in the following error message:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_field_data.tid' in 'where clause'
.So the field name is generated as
taxonomy_term_field_data.tid
, but it has to betaxonomy_term_field_data_node_field_data.tid
because of the second relationship.Steps to reproduce
term_node_tid
)tid_representative
) using the other relationshipSo I guess there are some escaping issues not using
$this->tableAlias
where necessary, as this variable already has the correct base table alias.EDIT:
Forgot to mention that, if i replace the wrong field name, my query works completely fine - with and without your patch.
Comment #12
hctom... and just another edit: Your patch definitely solves other SQL issues than mine ;) I had to extend the
GroupwiseMax
relationship plugin to add some more filters to the temporary view used for querying the represantitive node. Without your patch, my filters received values from the parent view, but with you patch everything works as expected for that case. So the only thing that should be fixed is the one mentioned above, that the field name is generated wrong.Comment #13
dawehnerIt is great that people actually care about this!
Just a really quick review, sorry for no feedback in such a long time.
Why do we ignore the option now?
I'm wondering whether we could replace **CORRELATED** in
views_query_views_alter()
Comment #18
x.meglio@gmail.com CreditAttribution: x.meglio@gmail.com commentedAs of today, is there any work-around for the issue? I'm experiencing it in 8.4 for a simple case: a view of taxonomy terms, with one representative item per term. SQL fails.
The only filter is the vocabulary type. The only relationship is Representative node.
Even though the issue is in Drupal core, last comment was 2 years ago. Is it because there is no obvious solution to the problem?
Comment #19
oresh CreditAttribution: oresh as a volunteer commentedComment #21
GaëlGHere's a reroll of #9 for 8.4, so that it can be referenced with composer. It still contains the problems mentioned since #9, but it's better than nothing and fixes the bug we had.
Comment #22
yogeshmpawarTriggering test bots.
Comment #23
pitpap CreditAttribution: pitpap commentedGo to Views find the view that causes this problem and copy it. When you are sure that you copied the view, delete the old view and place again the new copy wherever you want in your site.
Worked for me.
Comment #24
ckaotikThe problem mentioned in #18 is due to
conditionNamespace
assuming it's getting a single "foo.a = bar.b" condition. But if you're using a field of the node, this will actually be "node_field_dataINNER.nid = foo.entity_id AND node__field_dateINNER.deleted = '0'", which contains anAND
that's not handled.Furthermore, the prefixing assumes that the left part of the comparison is what needs to be namespaced. However, this is not always true, e.g. for
LEFT JOIN {taxonomy_index} taxonomy_indexINNER ON "node_field_dataINNER".nid = taxonomy_index.nid
. In this case, the namespace is applied to the wrong variable.This should fix the problem from #11/#18. Regardless, there is still the feedback from #13, tests to be written and the code style isn't quite up to standards either.
Comment #25
ckaotikWhoopsie, here's the updated patch.
Comment #27
GiorgosKpatch applies with following minor problems
worked for 8.3.7 as expected but does not work for 8.5.1
Comment #29
neclimdulThis plugin is pretty complicated, seems like a hard problem to solve.
This would generally be
\Drupal::database();
or at the least not fully qualified.But more importantly can we safely assume this is running on the same database as the default connection? Couldn't the quoting fail to match the database if its running on a different connection?
Indention is off by one and fails standards check.
string replacing into a query sets off warnings for me but I don't have a better solution. The complexity of what's going on here doesn't offer an obvious solution and is why this issue and that odd todo about the order by exist. At the very least we have to be sure our quoting is 100% right per my earlier comment.
Comment #30
RoSk0Patch #25 fixes the problem for me. Thank you very much!
Tested on Drupal 8.3.7, MariaDB 5.5.64.
Taxonomy term view with required "Representative node" relationship without "Representative view".
The error was:
Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Learning levels[learning_levels]: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_field_data.tid1' in 'where clause': SELECT taxonomy_term_field_data.weight AS taxonomy_term_field_data_weight, taxonomy_term_field_data.tid AS tid, node_field_data_taxonomy_term_field_data.nid AS node_field_data_taxonomy_term_field_data_nid FROM {taxonomy_term_field_data} taxonomy_term_field_data INNER JOIN {node_field_data} node_field_data_taxonomy_term_field_data ON (SELECT node_field_dataINNER.nid AS nidINNER FROM {node_field_data} node_field_dataINNER LEFT JOIN {taxonomy_index} taxonomy_indexINNER ON "node_field_dataINNER".nid = "taxonomy_indexINNER".nid LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node_field_dataINNER ON "taxonomy_indexINNER".tid = "taxonomy_term_field_data_node_field_dataINNER".tid WHERE ("taxonomy_term_field_data_node_field_dataINNER".tid = taxonomy_term_field_data.tid1 ) ORDER BY node_field_dataINNER.nid DESC LIMIT 1 OFFSET 0) = node_field_data_taxonomy_term_field_data.nid LEFT JOIN {taxonomy_term__field_cc_level} taxonomy_term__field_cc_level ON taxonomy_term_field_data.tid = taxonomy_term__field_cc_level.entity_id AND (taxonomy_term__field_cc_level.deleted = :views_join_condition_0 AND taxonomy_term__field_cc_level.langcode = taxonomy_term_field_data.langcode) WHERE (taxonomy_term_field_data.vid IN (:db_condition_placeholder_2)) AND (taxonomy_term__field_cc_level.field_cc_level_value <> :db_condition_placeholder_3) ORDER BY taxonomy_term_field_data_weight ASC LIMIT 11 OFFSET 0; Array ( [:db_condition_placeholder_2] => learning_level [:db_condition_placeholder_3] => 1 [:views_join_condition_0] => 0 ) in Drupal\views\Plugin\views\query\Sql->execute() (line 1488 of /vagrant/web/core/modules/views/src/Plugin/views/query/Sql.php).
Comment #31
knyshuk.vova CreditAttribution: knyshuk.vova at Internetdevels commentedI had the same issue on Drupal 8.8.1. The patch #25 fixes my error. Good job, @ckaotik , thank you.
The error was:
Comment #32
knyshuk.vova CreditAttribution: knyshuk.vova at Internetdevels commentedComment #33
alexpottWe still need tests here. The changes are complex and this plugin is obviously under tested.
Given that we have clear steps to reproduce the fail adding a test that fails on HEAD and passes with the patch should be possible.
Comment #34
narendra.rajwar27Comment #35
narendra.rajwar27Testing the issue with drupal 8.8.5. SInce patch #25 is already in core. But still adding relationship for representative node, got this error:
Here is Issue for sort criteria: right syntax to use near
'INNER. DESC LIMIT 1
While adding relationship for representative node it has field Representative sort criteria. But sort order clause is not providing table and field separately. Its just providing the table_name.field_name. So added a possible workaround here. since tables are aliased by appending subquery_namespace.
Comment #36
neclimdulNot any version of core I'm aware of.
The call to \Drupal\Core\Database\Database::getConnection(); is still pretty troubling to me.
Also alex's tests comment still hasn't been addressed.
Comment #37
narendra.rajwar27neclimdul my bad, it was due to abnormal termination of composer.
Refering the issue in comment #35, I updated the patch file. The Patch file additionally include the changes.
Comment #39
neclimdulno worries, just making sure we get the right patches :) moving target to d9.
Comment #40
narendra.rajwar27Patch applied to drupal 9.1.x-dev for Representative Node Views.
Comment #41
narendra.rajwar27Comment #42
daffie CreditAttribution: daffie commentedComment #43
datawench CreditAttribution: datawench commentedThank you, Narendra, for patch #40. It fixed this issue for me when updating a Drupal 8.7 site to 8.8.5.
It kills me that this issue has to be rerolled for every new version of core.
Comment #44
sharma.amitt16 CreditAttribution: sharma.amitt16 as a volunteer and at Srijan | A Material+ Company for Drupal India Association commentedThanks @narendra for your contribution. Patch #40 works for me. I tried to reproduce the issue as per the steps given in IS with latest code of 9.1.x branch. I am able to reproduce the issue.
Before patch applies I am getting below error:
After applying patch it works well and error is resolved.
Good to move to RTBC.
Comment #45
Demma10 CreditAttribution: Demma10 commentedI've installed the patch in comment #40 and am having the same problem that hctom had in comment #11. With the patch the representative node relationship works on views that use taxonomy term as the base table, however the relationship doesn't work for views that use node as the base table (where the taxonomy term itself is brought in via a relationship).
This is the error Views gives:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_field_data.tid' in 'where clause': SELECT COUNT(*) AS expression FROM (SELECT DISTINCT node__field_rs_release_date.field_rs_release_date_value AS node__field_rs_release_date_field_rs_release_date_value, node_field_data.nid AS nid, taxonomy_term_field_data_node__field_rs_product_link.tid AS taxonomy_term_field_data_node__field_rs_product_link_tid, node_field_data_taxonomy_term_field_data.nid AS node_field_data_taxonomy_term_field_data_nid, 1 AS expression FROM {node_field_data} node_field_data LEFT JOIN {node__field_rs_product_link} node__field_rs_product_link ON node_field_data.nid = node__field_rs_product_link.entity_id AND node__field_rs_product_link.deleted = :views_join_condition_0 LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node__field_rs_product_link ON node__field_rs_product_link.field_rs_product_link_target_id = taxonomy_term_field_data_node__field_rs_product_link.tid LEFT JOIN {node_field_data} node_field_data_taxonomy_term_field_data ON (SELECT node_field_dataINNER.nid AS nidINNER FROM {node_field_data} node_field_dataINNER LEFT JOIN {taxonomy_index} taxonomy_indexINNER ON "node_field_dataINNER".nid = "taxonomy_indexINNER".nid LEFT JOIN {taxonomy_term_field_data} taxonomy_term_field_data_node_field_dataINNER ON "taxonomy_indexINNER".tid = "taxonomy_term_field_data_node_field_dataINNER".tid WHERE ("taxonomy_term_field_data_node_field_dataINNER".tid = taxonomy_term_field_data.tid) ORDER BY nidINNER DESC LIMIT 1 OFFSET 0) = node_field_data_taxonomy_term_field_data.nid LEFT JOIN {node__field_rs_release_date} node__field_rs_release_date ON node_field_data.nid = node__field_rs_release_date.entity_id AND node__field_rs_release_date.deleted = :views_join_condition_1 WHERE (node_field_data.status = :db_condition_placeholder_0) AND (node_field_data.type IN (:db_condition_placeholder_1))) subquery; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => release [:views_join_condition_0] => 0 [:views_join_condition_1] => 0 )
Comment #47
Timmy_Cos CreditAttribution: Timmy_Cos as a volunteer commentedUnfortunately there appears to be an issue with the patch in #40 :(
After applying the patch custom sort fields would not apply and ordering was defaulting to node ids.
Looking at the changes these isset operations feel a bit off to me, should they be checking $temp_view->query->fields[$order_key] rather than just $order_key?
Comment #50
neclimdulconflicted with #3222769: [November 8, 2021] Replace all list (array destructuring) assignment to the array syntax
also fixing the database connection because its still not fixed and now its failing PHPCS.
Also, Timmy is right, that looks really weird. order_key has to be a string for the explode but then we try to check the values like an array? One of those cases where isset is hiding errors i think. I've reverted those changes and all the tests seem happy so lets see.
Comment #51
glynster CreditAttribution: glynster commented@neclimdul thanks so much for the updated patch, resolves the issue and applies cleanly for Drupal 9.3. RTBC + 1
Comment #52
Nathan Tsai CreditAttribution: Nathan Tsai commentedPatch #50 also worked for me on 9.3.0. Thanks, @neclimdul !
Comment #53
lucuhb CreditAttribution: lucuhb commentedPatch #50 doesn't seem to work for me on 9.3.6. I tried to have a list of all terms of a taxonomy (of name serie) order by terms last used in contents (so last used terms should be displayed first) .
Here is the configuration I tried on a taxonomy based view:
- add representative node in relationship, order by a date field (descendant) (field_date_publication)
- add new "Relationship : field_serie : Taxonomy term" with "representative node" relationship selected
- Order criteria : (Representative Node) Content : publication Date (desc), with "representative node" selected in relationship
=> views gives this error :
SQLSTATE[HY000]: General error: 4078 Illegal parameter data types row and int for operation '=': SELECT DISTINCT "taxonomy_term_field_data"."tid" AS "tid", "node_field_data_taxonomy_term_field_data"."nid" AS "node_field_data_taxonomy_term_field_data_nid", "taxonomy_term_field_data_node__field_serie"."tid" AS "taxonomy_term_field_data_node__field_serie_tid", DATE_FORMAT(node_field_data_taxonomy_term_field_data__node__field_date_publication.field_date_publication_value, '%Y%m%d') AS "node_field_data_taxonomy_term_field_data__node__field_date_p", 'liste_series:page_1' AS "view_name" FROM "drupal_taxonomy_term_field_data" "taxonomy_term_field_data" LEFT JOIN "drupal_node_field_data" "node_field_data_taxonomy_term_field_data" ON (SELECT "node_field_dataINNER"."nid" AS "nidINNER", ':default' AS "view_name" FROM "drupal_node_field_data" "node_field_dataINNER" LEFT JOIN "drupal_taxonomy_index" "taxonomy_indexINNER" ON "node_field_dataINNER".nid = "taxonomy_indexINNER".nid LEFT JOIN "drupal_taxonomy_term_field_data" "taxonomy_term_field_data_node_field_dataINNER" ON "taxonomy_indexINNER".tid = "taxonomy_term_field_data_node_field_dataINNER".tid WHERE ("taxonomy_term_field_data_node_field_dataINNER".tid = taxonomy_term_field_data.tid) ORDER BY "node_field_dataINNER"."created" DESC LIMIT 1 OFFSET 0) = node_field_data_taxonomy_term_field_data.nid LEFT JOIN "drupal_node__field_serie" "node_field_data_taxonomy_term_field_data__node__field_serie" ON node_field_data_taxonomy_term_field_data.nid = node_field_data_taxonomy_term_field_data__node__field_serie.entity_id AND node_field_data_taxonomy_term_field_data__node__field_serie.deleted = :views_join_condition_0 LEFT JOIN "drupal_taxonomy_term_field_data" "taxonomy_term_field_data_node__field_serie" ON node_field_data_taxonomy_term_field_data__node__field_serie.field_serie_target_id = taxonomy_term_field_data_node__field_serie.tid LEFT JOIN "drupal_node__field_date_publication" "node_field_data_taxonomy_term_field_data__node__field_date_publication" ON node_field_data_taxonomy_term_field_data.nid = node_field_data_taxonomy_term_field_data__node__field_date_publication.entity_id AND node_field_data_taxonomy_term_field_data__node__field_date_publication.deleted = :views_join_condition_1 AND (node_field_data_taxonomy_term_field_data__node__field_date_publication.langcode = node_field_data_taxonomy_term_field_data.langcode OR node_field_data_taxonomy_term_field_data__node__field_date_publication.bundle = :views_join_condition_3) WHERE ("taxonomy_term_field_data"."vid" IN (:db_condition_placeholder_4)) AND ("taxonomy_term_field_data"."status" = :db_condition_placeholder_5) ORDER BY "node_field_data_taxonomy_term_field_data__node__field_date_p" DESC; Array ( [:db_condition_placeholder_4] => serie [:db_condition_placeholder_5] => 1 [:views_join_condition_0] => 0 [:views_join_condition_1] => 0 [:views_join_condition_3] => article )
I have the same error when I remove the "Relationship : field_serie : Taxonomy term" relationship.
Comment #54
landure CreditAttribution: landure commentedAt the risk of being wrong: the issue for me came from the encasing done by "conditionNamespace" at line 324 of GroupwiseMax.php that is not compatible with MySQL/MariaDB. I've tried with this correction that worked for me:
I posted a patch file below for those willing to try this.
I hope that a Drupal developer can do this in a better manner. The escaping of tables names should not be done by this plugin code.
Comment #55
landure CreditAttribution: landure commentedComment #57
glynster CreditAttribution: glynster commentedRemoved
Comment #58
pooja saraah CreditAttribution: pooja saraah at Srijan | A Material+ Company for Drupal India Association commentedFixed failed commands on #55
Attached patch Against Drupal 9.5.x
Comment #59
pooja saraah CreditAttribution: pooja saraah at Srijan | A Material+ Company for Drupal India Association commentedComment #60
nikhilraut CreditAttribution: nikhilraut at QED42 for Drupal India Association commentedComment #61
nikhilraut CreditAttribution: nikhilraut at QED42 for Drupal India Association commentedAfter applying the patch 58 on drupal 9.5 same error is showing
Comment #62
nikhilraut CreditAttribution: nikhilraut at QED42 for Drupal India Association commentedComment #64
neclimdulreroll of 50 after #3268818: Fix class comment doc blocks in non tests 'Drupal.Commenting.DocComment.ShortSingleLine' which included a different modification to a CS doc fix also included in this issue. Just accepted the upstream change. Merge diff is just the top line of the GroupwiseMax.php class doc.
Still needs tests though... I'm sure there's some pretty straightforward method of creating the view from the IS and running the preview to see the failure. Don't have time at the moment though. Would really appreciate someone starting that test.
Comment #65
glynster CreditAttribution: glynster commented@neclimdul updated patch is working well for us:
ENV info:
Drupal: 9.4.8
PHP: 8.1.12
Comment #66
neclimdulblind 10.1 reroll. conflict was just a block in the GroupWiseMax and and it was breaking with the str_contains changes so the patch doesn't have an interdiff because the patch uses preg instead of the str_contains logic.