Filter: User posted or commented does not work

This is definitive related. The code has to changed like in the argument handler.

    $subselect->condition('uid', $this->value, $this->operator);

The problem is this line from my perspective. Placeholders have problem when they are part of the subquery.

Here is a patch for this issue.

Seems to be somehow now the same.

As always the request is there but no will to test the patch. That's sad.

Commited to git.
It's hosted on https://github.com/ksenzee/views3ui until the new ui is in.

Reopening. I closed #1235724: user posted or commented filter doesn't seem to work as a duplicate of this issue.

Steps to reproduce:

1. git clone --branch 7.x http://git.drupal.org/project/drupal.git
2. git clone --branch 7.x-3.x http://git.drupal.org/project/views.git
3. Visit /admin/structure/views/add
4. Add filter "Content: User posted or commented"

In other words, add the following to any view.

/* Filter criterion: Content: User posted or commented */
$handler->display->display_options['filters']['uid_touch']['id'] = 'uid_touch';
$handler->display->display_options['filters']['uid_touch']['table'] = 'node';
$handler->display->display_options['filters']['uid_touch']['field'] = 'uid_touch';
$handler->display->display_options['filters']['uid_touch']['value'] = array(
  0 => '1',
);

Example of the the sql as a result of the above committed patch

SELECT node.title AS node_title, node.nid AS nid
FROM {node} node
WHERE ((( (node.uid IN  ('1')) OR (0 < (SELECT COUNT(*) AS expression
FROM (SELECT 1 AS expression
FROM {comments} c
WHERE  (uid in Array) AND (nid = node.nid) ) subquery)) OR (0 < SELECT COUNT(*) AS expression
FROM (SELECT 1 AS expression
FROM {comments} c
WHERE  (uid in Array) AND (nid = node.nid) ) subquery) )))

As can be seen in the SQL above "WHERE (uid in Array) " is the problem. Unfortunately, I'm not sure if this pertains to #1112854: Subqueries use wrong arguments or not. Perhaps dereine can provide some input on this.

sub

I'm getting this error (see attached), and strange timestamp in a dsm. Patch doesn't seem to fix it.

Error seems to stem from (views_handler_filter_comment_user_uid.inc):

$condition = db_or()
      ->condition("$this->table_alias.uid", $this->value, $this->operator)
      ->where("0 < (" . (string) $subselect . ")")
      ->condition(0, $subselect, '<');

    $this->query->add_where(0, $condition);

Here's the bad query generated:

SELECT FROM {node} node WHERE (( (node.status = :db_condition_placeholder_0) AND( (node.uid IN (:db_condition_placeholder_1)) OR (0 < (SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {comments} c WHERE (uid in Array) AND (nid = node.nid) ) subquery)) OR (0 < SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {comments} c WHERE (uid in Array) AND (nid = node.nid) ) subquery) )))

Looks like it's assuming $this->value is a string/int, but it's an array.

I'm thinking that

$subselect->where("uid $this->operator $this->value");

Should be

$subselect->condition("uid", $this->value, $this->operator);

I'm not getting any errors but it seems the filter stops working as soon as a user comments on something.

The filter seems to work fine as long as a user has no comments but as soon as they comment on something the filter breaks and shows all nodes regardless of who created them or commented on them.

I'm using the latest Dev and Drupal 7.8 with the default Tracker view.

I also tried on a fresh install.

Making the change from #11, brings us a new sql 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 'SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM comments c WHE' at line 1

Another issue, is that the table that stores comments is "comment", not "comments".

Subscribing.

Looking forward to seeing a fix for this. My whole front page depends on it. I'm not a programmer, but if I can help test anything, let me know. Thanks.

@couturier, can you test this patch?

Rather than a subquery inside a condition on the original query, this patch adds a join of the comment table on the base table. The SQL (without any other filters) looks like this

SELECT n.nid FROM node n 
LEFT JOIN comment c ON n.nid = c.nid
WHERE n.uid in (:uids)
OR c.uid in (:uids)

Essentially we're saying that if the comment table has any rows with an nid field value the same as an nid in the node table, then include the nodes in the result set (but don't ignore any nodes without comments because of the left join) on the condition that those selected comments were written by the users specified in the set :uids or if the nodes were authored by the users in the set :uids. I hope that sentence makes sense!

Thanks to @soyarma for walking through the SQL with me and explaining what a LEFT JOIN is!

+++ b/modules/comment/views_handler_filter_comment_user_uid.incundefined
@@ -1,24 +1,25 @@
+    $join = new views_join;

Should be new views_join().

+++ b/modules/comment/views_handler_filter_comment_user_uid.incundefined
@@ -1,24 +1,25 @@
+    $join->construct('comment',$this->table_alias,'nid','nid', array(),'LEFT OUTER');

Needs spaces after quotes.

+++ b/modules/comment/views_handler_filter_comment_user_uid.incundefined
@@ -1,24 +1,25 @@
+      ->condition("comment.uid", $this->value, $this->operator);

Without interpolation, no real need for the double quotes. Should be single quotes.

Otherwise, looks pretty good.

Minor clarification on @tim.plunkett's comment:

+++ b/modules/comment/views_handler_filter_comment_user_uid.incundefined
@@ -1,24 +1,25 @@
+    $join->construct('comment',$this->table_alias,'nid','nid', array(),'LEFT OUTER');

This needs spaces after the commas, not the quotes.

Thanks @tim.plunkett

Updated with the corrections.

My test sites are all down currently due to a hosting issue. This has happened only twice in a year and a half due to switching sites over to a completely new server, and I'm waiting on tech support, so that's the only delay. I'll report back on the testing as soon as I can, unless anyone else gets to doing it before I'm able. Thanks jessebeach and tim.plunkett!

My hosting is still in transition, but I managed to slip in and apply the patch to one of the sites for testing. Works perfectly now! I'm new, so not sure what to do next. Do we do something special to report this fix for the next Views version release?

Oh that's good news! I'll test the patch myself later to confirm it.

You don't have to do anything beyond this, but it might speedup the process to go into the #drupal-contribute IRC channel to ping merlinofchaos (or another maintainer). If I find time this week I'll do it myself.

Okay rc3 is out which has fixed this specific part. Sadly i couldn't response on that issue.

In general this patch would have not worked because it would have joined the comment table, so creates duplicates.

Very nice