Taxonomy term "Representative node" views with filters and sorts don't work

Try adding it as a filter, but add the nid as the only view field.

Update to 7.x-3.x-dev and the error will be gone.

The representative view should return just an ID I think. Instead of using a pager of 10 items, limit the output to 1 element.

Same problem here, not using representative view though, as I don't need complex criteria. Tried both 3.3 and 3.x-dev.

The error disappears if I set the sort criteria to 'nid' instead of the content field I wanted to use in the first place.

This funtionality would be awesome. Is there work done on it already ?

For what it's worth, here is a dump of the resulting query. As expected by the error message, some tokens are not replaced.

SELECT taxonomy_term_data.description AS taxonomy_term_data_description, taxonomy_term_data.format AS taxonomy_term_data_format, node_taxonomy_term_data.nid AS node_taxonomy_term_data_nid, taxonomy_term_data.name AS taxonomy_term_data_name, taxonomy_term_data.vid AS taxonomy_term_data_vid, taxonomy_term_data.tid AS tid, taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name, 'node' AS field_data_field_temps_node_entity_type
FROM 
{taxonomy_term_data} taxonomy_term_data
INNER JOIN {node} node_taxonomy_term_data ON (SELECT node_bookmarks.nid AS nid_bookmarks
FROM 
{node} node_bookmarks
LEFT JOIN {taxonomy_index} taxonomy_index_bookmarks ON node_bookmarks.nid = taxonomy_index_bookmarks.nid
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_node_bookmarks ON taxonomy_index_bookmarks.tid = taxonomy_term_data_node_bookmarks.tid
LEFT JOIN {field_data_field_temps} field_data_field_temps_bookmarks ON node_bookmarks.nid = field_data_field_temps_bookmarks.entity_id AND (field_data_field_temps_bookmarks.entity_type = :views_join_condition_0 AND field_data_field_temps_bookmarks.deleted = :views_join_condition_1)
WHERE (( (taxonomy_term_data_node_bookmarks.tid = taxonomy_term_data.tid ) ))
ORDER BY field_data_field_temps_bookmarks.field_temps_value ASC
LIMIT 1 OFFSET 0) = node_taxonomy_term_data.nid
LEFT JOIN {taxonomy_vocabulary} taxonomy_vocabulary ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
WHERE (( (taxonomy_vocabulary.machine_name IN  ('parties')) ))
ORDER BY taxonomy_term_data_name ASC
LIMIT 10 OFFSET 0

Re-qualifying the issue and updating the relevant version.

Just in case it rings a bell in some dev's head, here is the query that gets ouput with latest dev :

SELECT taxonomy_term_data.description AS taxonomy_term_data_description, taxonomy_term_data.format AS taxonomy_term_data_format, node_taxonomy_term_data.nid AS node_taxonomy_term_data_nid, taxonomy_term_data.name AS taxonomy_term_data_name, taxonomy_term_data.vid AS taxonomy_term_data_vid, taxonomy_term_data.tid AS tid, taxonomy_vocabulary.machine_name AS taxonomy_vocabulary_machine_name
FROM 
{taxonomy_term_data} taxonomy_term_data
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
LEFT JOIN {field_data_field_temps} field_data_field_tempsINNER ON nodeINNER.nid = field_data_field_tempsINNER.entity_id AND (field_data_field_tempsINNER.entity_type = :views_join_condition_0 AND field_data_field_tempsINNER.deleted = :views_join_condition_1)
WHERE (( (taxonomy_term_data_nodeINNER.tid = taxonomy_term_data.tid ) ))
ORDER BY field_data_field_tempsINNER.field_temps_value ASC
LIMIT 1 OFFSET 0) = node_taxonomy_term_data.nid
LEFT JOIN {taxonomy_vocabulary} taxonomy_vocabulary ON taxonomy_term_data.vid = taxonomy_vocabulary.vid
WHERE (( (taxonomy_vocabulary.machine_name IN  ('parties')) ))
ORDER BY taxonomy_term_data_name ASC
LIMIT 10 OFFSET 0

Something is obviously wrong with some string replacement, so perhaps the problem will jump to the eyes of somebody more versed in Views dev than me ?

Why is this obviously wrong? The inner string is added there by intention.

Maybe this is fixed by #1293980: Groupwise Max Representative View issue
There are multiple issues about that: http://drupal.org/project/issues/views?text=representative it would be cool if you could check them out and see whether some of them fixes your problem.

Obviously, as in : "The 'INNER' strings here and there are obviously breaking the query, and obviously come from a messed up string replacement".

I checked the various issues you indicated before posting, but didn't find anything that seemed relevant to my case. I'll take another tour and update.

For the meanwhile, as noted by dawehner (http://drupal.org/node/1312194#comment-5706918), an alternative solution is to use the views_field_view module.

I'm getting the same error message (SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens).

It's not the INNER strings that are breaking the query, it's the unbound variables (which in my case look like :db_condition_placeholder_1 etc). I'm been scratching my head about this same issue for a while. I have a representative view that works if I add no filters, no contextual filters, and no relationships. I can start to add filters one by one, and they work, but adding more than one filter breaks everything.

Also, adding the taxonomy term depth filter with a depth of anything other than 0 breaks the view.

Is there a way to get representative node for all children of taxonomy term not just for the term?

For example, if we have a tree like this:

ROOT (nid1, nid2, nid3...)
- Term1 (nid4, nid5, nid6...)
--Term 2 (nid7, nid8, nid9...)
---Term 3 (nid10, nid11, nid12...)
---Term 4 (nid13, nid14, nid15...)
-Term 5
-Term 6
--Term 7
---Term 8
...

So for example when you want to get representative node for Term1 to get representative node from all Term1 + Term 2 + Term 3 + Term4 like nid4, nid5, nid6, nid7, nid8, nid9, nid10, nid11, nid12, nid13, nid14 or nid15 not just nid4, nid5, nid6???

I got the same error described in the OP and found out that checking "Generate subquery each time view is run." the error disappears and the view works.

However, that option's help text says "WARNING: seriously impairs performance", that makes me a bit uneasy. Will enabling the cache for this view work, or will it always force the view to be run to generate the subquery?

I'm not sure if this really hepled but I was having the same problem as original
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

What seems to me that helped was adding the field used as "Representative sort criteria" to views sorting. Can someone confirm this?

Confirm #17 works in my case.

#17 Did not work in my case.
#13 doesn't work for me either, because I need the unrendered, value of the fields.
I'm looking for a solution but I cannot find it. Is there none?

Using the views_field_view module instead of a representative node view (as suggested by druvision in #13) wasn't an option for us because we needed the raw value of nid for further rewriting and template manipulations. So I set out to make representative node views work with filters and sorting enabled.

It turned out that there were three major problems to the current views_handler_relationship_groupwise_max.inc code:

1. The query's order keys from $subquery->getOrderBy() weren't unaliased into actual field names with table prefixes, which broke the namespacing of the table names
2. Even though namespacing changes took place for the query conditions, those were never reflected in the actual SQL because the conditions weren't being recompiled after the changes
3. Subquery argument placeholders were never replaced with the argument values (the :db_condition_placeholder_1 problem)

The attached patch addresses all of the three problems. For replacing argument placeholders, I adapted code from views_ui_preview() - not sure how secure that approach is. Anyway, my representative node view with extra filters and sorts works now.

(To answer questions above, the representative node view should be a simple listing of nodes (with optionally filters and sorts applied), with just the nid field in the field listing. There's no need to add any contextual filters to the view.)

Thanks for working on that.

+++ b/handlers/views_handler_relationship_groupwise_max.incundefined
@@ -280,6 +276,10 @@ class views_handler_relationship_groupwise_max extends views_handler_relationshi
+    // Clone the query object to force recompilation of the underlying
+    // where and having objects on the next step.

No need to wrap this comment such early, there is at least place enough for the "where".

+++ b/handlers/views_handler_relationship_groupwise_max.incundefined
@@ -280,6 +276,10 @@ class views_handler_relationship_groupwise_max extends views_handler_relationshi
+    $subquery = clone($subquery);

I guess the proper syntax for that is $subquery = clone $subquery;

+++ b/handlers/views_handler_relationship_groupwise_max.incundefined
@@ -291,6 +291,19 @@ class views_handler_relationship_groupwise_max extends views_handler_relationshi
+    // Replace subquery argument placeholders.

If we file a patch against drupal core, it seems to make sense to have a helper method for that in dbtng, because this is also used directly in the preview code of the views UI.

Thanks for the comments. Comment wrapping and clone syntax fixed in the attached patch. Not sure how to approach the helper method in dbtng part.

This patch worked for me...

Hunk #1 FAILED at 195.
Hunk #2 FAILED at 269.
Hunk #3 FAILED at 280.
Hunk #4 FAILED at 291.

After manual patch:

field_data_field_gorod_sub_global.field_gorod_value = '**CORRELATED**'

This is filter value.

Other values are good.

all contextual filter values return '**CORRELATED**'

@divined: Are you patching against the latest Views 7.x-3.x-dev?

I apologise in advance for this long comment but I'm trying to resolve an issue I've been working on for a while now.

I have been having a problem related to this issue.

I am using panels to display content related to an organic group. On one panel I have several view panes that display taxonomy terms for content associated with the group. Each pane displays terms from a different vocabulary.

I wanted to limit the terms being displayed to only show those terms that a) had Content marked with that term, and b) that content also belonged to the group.

I thought I could achieve this by using the representative node feature. So, for a view pane listings terms for a vocabulary, I added a relationship for representative node with all the defaults.I then added a second relationship for the group membership based on the representative node. Finally I added a context for the group based on the group membership relationship and having the group ID passed through the panel context.

This worked well for vocabularies which only applied to a single content type. However, for vocabularies which applied to more than one content type it wasn't working. I thought maybe the answer was to use a representative view (Is this right? To limit the representative nodes to one content type only?). So I created a new view to display only the ID for one content type and with a sort, and used this as the representative view.

When I did this I got the error:

Notice: Undefined offset: 1 in views_handler_relationship_groupwise_max->left_query() (line 274 of .../sites/all/modules/views/handlers/views_handler_relationship_groupwise_max.inc).

So I updated the views module to the latest dev version and applied the patch at #22 (incidentally, I had to apply the patch manually as it didn't seem to work for me using cygwin).

This did make a difference but the view still wasn't showing all the terms that it should have been showing.

I guess my question is, can the representative view work in this way: to limit terms to select from just one content type? If it doesn't could anyone possibly suggest to me how I might be able to limit terms to only show when they have content associated with them and where that content also belongs to the group defined by the context, for vocabularies that apply to more than one content type?

At the moment, I'm not sure if I'm simply using this feature in the wrong way or if there is an actual bug :\

Okay, apologies, this is not really to do with the issue, but I managed to get the results I was looking for (#29) by using a different kind of view. I created a term view and added a relationship for "content with term", and a second relationship for "group membership" based on the first relationship. I then added in a filter for the content type, and a context filter for the group. I only added a field for the term and in the query settings made the query distinct. The view now only shows terms which relate to content of a specific type and that [the content] belong to a specific group. It works great :)

Hi @bmango, can you export your view please?
Thanks a lot in advance for your reply!
Greetings, Martijn

Hi @Summit, I'm uploading my view here. Let me know if you need anything else.

Since yesterday I've found that I've also been having problems with my views using representative node for taxonomy vocabularies that apply to only one content type. So have replaced these as well with the solution I worked out in #30.

Ignoring #29-32. The comments have nothing to do with the original issue. @bmango please open a separate issue for your question.

The patch in#22 applies cleanly to the current Views 3.x dev and corrects the described issue. Thank you @jamix!

I set #1590264: Unknown column 'taxonomy_term_data.tid' in 'where clause' as a duplicate to this issue. It's not exactly a duplicate, but the IMO ideal fix for it requires modification to the patch in #22 and they are quite related.

The issue is in a View with multiple displays the argument sub-querys are generated in the same PHP call incrementing the static placeholder counter resulting in SQL errors:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_data.tid1' in 'where clause'

The SQL column name shouldn't have the number 1 at the end. Further details in: https://drupal.org/node/1590264#comment-7855877

I've added the correction into jamix's patch. The original Views placeholder replacement code is removed. Jamix's subquery argument placeholder replacement foreach() gets a new elseif to change the **CORRELATED** string to the value of $this->definition['outer field'].

Relevant changes:

-    // Replace the placeholder with the outer, correlated field.
-    // Eg, change the placeholder ':users_uid' into the outer field 'users.uid'.
-    // We have to work directly with the SQL, because putting a name of a field
-    // into a SelectQuery that it does not recognize (because it's outer) just
-    // makes it treat it as a string.
-    $outer_placeholder = ':' . str_replace('.', '_', $this->definition['outer field']);
-    $subquery_sql = str_replace($outer_placeholder, $this->definition['outer field'], $subquery_sql);
+    // Replace subquery argument placeholders.
+    $quoted = $subquery->getArguments();
+    $connection = Database::getConnection();
+    foreach ($quoted as $key => $val) {
+
+      if (is_array($val)) {
+        $quoted[$key] = implode(', ', array_map(array($connection, 'quote'), $val));
+      }
+      // If the correlated placeholder has been located, replace it when the outer field name.
+      elseif ($val === '**CORRELATED**') {
+        $quoted[$key] = $this->definition['outer field'];
+      }
+      else {
+        $quoted[$key] = $connection->quote($val);
+      }
+    }
+    $subquery_sql = strtr($subquery_sql, $quoted);

Bah.. Bad wording in comment. Fixed.

#35: representative_views-1417090-35.patch queued for re-testing.

Thanks!

I was coming from the issue #1454588: Relationship: User: Representative node bug? and the patch at #36 solved it. Great stuff!

I just set #1454588: Relationship: User: Representative node bug? as a duplicate of this issue. Please RTBC if everything looks good. Jamix and I cannot since we've both worked on the patch directly.

Had this problem with a view based on user profile values and in need of advanced sorting of nodes through relationships - representative views. This patch seems to have solved the issue. I am very grateful, great work guys!

#35 Worked for me, Thanks!

#35 Worked, thank you so much!

Had the same problem.
#35 Worked for me as well.
Thank you.

I've applied patch #35.

Although I'm not getting any errors I'm not seeing corect results as well. I have a silmple view for my taxonomy terms and I'm trying to display an image for representative node of each term.
Some of the terms have their representative fetched and some of them not and I'm not even looking at the representative sorting right now!

Please what is the simplest setup this should work for? Maybe if I start from scratch something will work eventually :)

Patched worked here with the latest dev.

Thanks, eosrei! #35 works well, +1 for RTBC

For me it works perfect as well.
Thank you very much!
+1 to RTBC!

The patch at at #35 was not incorporated into the the 3.7 release.
But it has been incorporated into the 3.8 release and the issue has been resolved for me.
Thanks

The issue that I had was the same as the one @ Relationship: User: Representative node bug?

I still have the issue even with the lastest 7.x-3.8 release. Dev version 7.x-3.7+37-dev didn't fix error either.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'taxonomy_term_data.tid2' in 'where clause'

Patch #35 is the only thing that fixes it for me.

I confirm that this still exists in 7.x-3.7+37-dev and it requires patching with #35 to work

Does not work for me. I'm trying to list a "profile node" for each user.

1. I create a view with user name and user edit link as fields.
2. I add the "representative node" relationship with all default settings left untouched.
3. In the views preview area, the error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
4. Git latest dev of Views 7.x-3.x-dev. Apply patch #35. Delete relationship and add it again. Issue persists.

Note: This issue also exists in D8 core. I'm going to write a patch for D8, perhaps it will get this patch committed too.

I found a problem with my patch in #35 while writing the D8 core views patch for this issue. The following error is generated if the Representative View contains a Views Query Substitution, such as the current time for the content last updated date:

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_TIME***) ))) ORDER BY nodeINNER.created DESC LIMIT 1 OFFSET 0) ' at line 3

For reference, some example View Query Substitutions: https://api.drupal.org/api/views/views.api.php/function/hook_views_query...

The attached patch now includes a call to views_query_views_alter() to run the substitution.

I've re-applied this patch against 7.x-3.x dev and anecdotally it seems to still be working, though the testbot is stalled.

Patch in #35 works for me except when using a random sort (#2225969: View Taxonomy term "Representative node" with global random ).

It would be good to test and see if this same bug exists on D8, as we will need to fix it in core too in that case (and looking at all the prayerful comments in this handler, I wouldn't be surprised). A quick(er) way to test might be on http://simplytest.me/.

Edit: Just saw that it does in #52, sorry! Thanks for #2379423: Representative Node Views fails due to invalid SQL.

So something is wrong with the Views branch tests: https://www.drupal.org/node/38878/qa

I can confirm patch at #53 works with views 3.10.

@askibinski
Same here.
I can confirm patch at #53 works with views 3.10.

Patched views to solve this problem, and my site is working as it should, however

this query is running and timing out on Mysql server, after 24hrs on a site with no traffic, MYsql grinds to a halt and the site won't respond.

-- Connection Id: 11402
-- User: **********
-- Host: localhost
-- DB: **********
-- Command: Query
-- Time: 1509
-- State: statistics
SELECT COUNT(*) AS expression
FROM
(SELECT DISTINCT node.nid AS nid, node.title AS node_title, node.language AS node_language, 'node' AS field_data_field_image_node_entity_type, 'node' AS field_data_field_summary_node_entity_type, 1 AS expression
FROM
node node
LEFT JOIN (SELECT td.*, tn.nid AS nid
FROM
taxonomy_term_data td
LEFT JOIN taxonomy_vocabulary tv ON td.vid = tv.vid
LEFT JOIN taxonomy_index tn ON tn.tid = td.tid
WHERE (tv.machine_name IN ('applications', 'features', 'products', 'substrates')) AND (td.language IN ('en', 'und')) ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid
INNER JOIN taxonomy_index taxonomy_index_value_0 ON node.nid = taxonomy_index_value_0.nid AND taxonomy_index_value_0.tid = '52'
INNER JOIN taxonomy_index taxonomy_index_value_1 ON node.nid = taxonomy_index_value_1.nid AND taxonomy_index_value_1.tid = '4'
INNER JOIN taxonomy_index taxonomy_index_value_2 ON node.nid = taxonomy_index_value_2.nid AND taxonomy_index_value_2.tid = '41'
INNER JOIN taxonomy_index taxonomy_index_value_3 ON node.nid = taxonomy_index_value_3.nid AND taxonomy_index_value_3.tid = '38'
INNER JOIN taxonomy_index taxonomy_index_value_4 ON node.nid = taxonomy_index_value_4.nid AND taxonomy_index_value_4.tid = '33'
INNER JOIN taxonomy_index taxonomy_index_value_5 ON node.nid = taxonomy_index_value_5.nid AND taxonomy_index_value_5.tid = '7'
INNER JOIN taxonomy_index taxonomy_index_value_6 ON node.nid = taxonomy_index_value_6.nid AND taxonomy_index_value_6.tid = '5'
INNER JOIN taxonomy_index taxonomy_index_value_7 ON node.nid = taxonomy_index_value_7.nid AND taxonomy_index_value_7.tid = '6'
INNER JOIN taxonomy_index taxonomy_index_value_8 ON node.nid = taxonomy_index_value_8.nid AND taxonomy_index_value_8.tid = '77'
INNER JOIN taxonomy_index taxonomy_index_value_9 ON node.nid = taxonomy_index_value_9.nid AND taxonomy_index_value_9.tid = '123'
INNER JOIN taxonomy_index taxonomy_index_value_10 ON node.nid = taxonomy_index_value_10.nid AND taxonomy_index_value_10.tid = '36'
WHERE ((( (taxonomy_index_value_0.tid = '52') AND (taxonomy_index_value_1.tid = '4') AND (taxonomy_index_value_2.tid = '41') AND (taxonomy_index_value_3.tid = '38') AND (taxonomy_index_value_4.tid = '33') AND (taxonomy_index_value_5.tid = '7') AND (taxonomy_index_value_6.tid = '5') AND (taxonomy_index_value_7.tid = '6') AND (taxonomy_index_value_8.tid = '77') AND (taxonomy_index_value_9.tid = '123') AND (taxonomy_index_value_10.tid = '36') ))AND(( (node.status = '1') AND (node.type IN ('portfolio')) AND (node.language IN ('en')) )))) subquery

The patch in #53 didn't work for me.

The sort by fields of the representative view was unintentionally removed by the foreach on line 267. When the de-aliased fields are added to the $order array, they also get processed in the foreach. This fails since it can't find the table and field without the original alias, and then the unset function removes the de-aliased order fields.

I don't know why that didn't happen to askibinski and dadderley, but I guess it could be different versions of php that handles the foreach in different ways. But in any case it's not a good idea to mess with the array while inside the foreach.

I have made a new version of the patch in #53 that uses a temporary array instead and that works for me.

#63 seemed to clear it up for me so far.

Thank you form the patch #63 worked for me also.

Can confirm #63 worked for me too.

Does anyone know why is the testing in postponed status? Would be nice to have this into dev branch. Can we maybe poke the test to restart and maybe succesfully run?

Hi prinds,
Applied your patch to Views 7.11. It works very well for my use case.

Thank you

Patch in #63 worked here to fix a "represenative node" query generation problem for a users view.

I don't know why the test request was postponed, but based on your feedback, I suggest we say the bug is fixed with #63 and hopefully we can get this committed soon?.

Thanks for your feedback..

Can confirm #63 worked for me too.
I vote for fixed status too.

The patch in #63 worked for me as well. I re-rolled with a few minor coding standard fixes. Maybe this will successfully trigger the testbot.

#63 for Views 7.x-3.11 worked for me too.

New patch tests correctly with Drupal 7.39

The patch worked for me. Thank you.

I don't think the tests will run until the 7.x-3.x branch tests pass: https://qa.drupal.org/pifr/test/27332

#71 worked for me. Thanks to everyone involved.

#71 plus a cache clear also worked for me - thanks.

Attempting to trigger the new testbot now that all tests are passing on the branch.

We've recently switched our testing from the old qa.drupal.org to DrupalCI. Because of a bug in the new system, #2623840: Views (D7) patches not being tested, older patches must be re-uploaded. On re-uploading the patch, please set the status to "Needs Review" so that the test bot will add it to its queue.

If all tests pass, change the Status back to "Reviewed & tested by the community". We'll most likely commit the patch immediately without having to go through another round of peer review.

We apologize for the trouble, and appreciate your patience.

Here's the re-rolled patch from #71

Let's hope it goes through..

Patch in #82 works well for me. Thank you :)

The test passed and we've got positive response.. Please note, that the patch in #82 and #71 are identical, so I'd say this is tested.

Hopefully this can be committed soon.

I'm not sure of the right protocol here but would it be possible to combine the patch here with the patch supplied for the issue SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.uid1' in 'where clause'?

The patch in this issue and the other issue both apply to the same file: /handlers/views_handler_relationship_groupwise_max.inc, and so it is not possible to apply them both. The second patch is quite small and so I thought it would be easier to incorporate it in the patch here. I have had a look at the code and it seems to me it would be possible to combine them by replacing lines 67-69 (of this patch):

+  elseif ($val === '**CORRELATED**') {
+    $quoted[$key] = $this->definition['outer field'];
+  }

with

+  elseif ($val === '**CORRELATED**') {
+    if ($this->relationship) {
+      $outer_field = str_replace($this->definition['argument table'], $this->relationship, $this->definition['outer field']);
+	$quoted[$key] = $outer_field;
+    }
+    else {
+      $quoted[$key] = $this->definition['outer field'];
+    }
+  }

However, I'm not a coder and don't want to mess anything up. All credit to nvahalik for the code from his patch I used here.

The reason I ask is because I have a view using a representative node relationship which is itself based on another relationship and so both the issues are coming up.

Apologies if I should be raising this in a separate issue...

Please ignore my comment in #85. It is impractical to try and solve another issue here. Let's wait for this patch to be committed, and then the code can be adjusted in the other patch.

Hi, I would like to report the following issue with patch #82.

If the representative node is selected by means of a representative subquery view which applies a RANDOM sort, the resulting view fails with the following MySQL error:

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 'INNER. ASC LIMIT 1 OFFSET 0) = node_users.nid LEFT JOIN ...' at line 3

Reviewing the query, there is indeed a MySQL error in the query:

ORDER BY INNER. ASC

Setting issue back to 'Needs work' for the moment.

Switching back to RTBC, since the issue reported in #87 might be considered a separate issue by itself.

#82 solved the problem I had getting my new feature at https://www.drupal.org/node/2728673 to work. My patch there includes the code from #82, but I'll reroll it with only my changes if this one gets committed.

#82 works on the latest version of Views for me. Would be good to see it committed...

Confirming that #82 is working for me as well

Confirming #82 - great work thanks!

Patch #82 works with the current dev version here.

Confirming that #82 is working here (version 7.x-3.14).

Please add patch #82 to module dev. Thanks a lot in advance!
Greetings, Martijn

@gerson.analist
Same here.
Confirming that #82 is working here (version 7.x-3.14)

Same result. Patch #82 is working

Is this also a problem in Drupal 8? If so, we should open a ticket there before committing this.

Hi,

Patch #82 not working for the latest views (Views-7.x-3.16).

@colan I created #2379423: Representative Node Views fails due to invalid SQL for D8 2.5 years ago.

Thanks.

Dear community, the issue in 8.x has been not touched for 2 years. Does it mean there is some patch working for 8.x, or is it just that there is no obvious solution to the issue?

"Representative node" feature is a core feature, and it covers many various cases. What can we do to get a patch for 8.x, especially latest 8.4?

@x.meglio@gmail.com: This issue is for Drupal 7. I think you meant to post in this issue: #2379423: Representative Node Views fails due to invalid SQL.

I can also vouch for patch #82. Filtering via "Representative View" is now working for me :D

Here's the re-rolled patch from #82, now its compatible with commit 22622c95760dffdcda8d3b64b032298184eaa66c

Committed. Thanks everyone.

Looks like the inclusion of this patch (7.x-3.19) has caused a regression in the work done on #2295379: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.uid1' in 'where clause' (7.x-3.16). The patch in the latter ticket was committed March 29th and this patch was re-rolled 3 days later without what appears to be any additional checking to ensure that it would work. Can we get this patch reverted and re-rolled properly to not clobber the changes in #2295379: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.uid1' in 'where clause'?

Same here. Sorting not working (I want the latest nodes but only showing the oldest ones). Last D7 stable version so far.

Isn't it possible to reopen the issue? Thank you.

UPDATE: cloning the view did work. In case it may help.

I recommend starting a new issue. A lot of the times, commited (fixed) issues get overlooked, since they are already closed. If it has been committed, it generally warrants a new issue.