Environment: Drupal 4.6, Apache 1.3.x, PHP 4.3.x, MySQL 4
cvs header: $Id: workspace.module,v 1.16 2005/02/23 15:32:18 jvandyk Exp $
I do not have node permissions module installed.

When I reached 24 nodes, the count returned by: 

$count_sql = 'SELECT COUNT(n.nid) + COUNT(c.cid) FROM '. implode(', ', $node_from) .' '. implode(' ', $node_join) .' LEFT JOIN {comments} AS c ON c.uid = '. db_escape_string($user->uid). ' WHERE '. implode(' ', $node_where);

... claimed I had 88 nodes! This is incorrect. Several test nodes later the result was higher, 29 vs. 102 I think. Also incorrect. I am not very SQL-smart, so after four hours of getting nowhere trying to make it work, I gave up and wrote a work-around that uses a UNION to get counts from the node and comments tables (more or less) independently. I justify using UNION because MySQL 4 is already a requirement of this module. Please see the attached patch.

CommentFileSizeAuthor
workspace.4-6.sql_count-workaround.patch4 KBTravis

Comments

jvandyk’s picture

jvandyk commented

If this can be done more cleanly (i.e., as a modification to a query rather than with a hack) I will apply it.

Péter Cseke’s picture

Péter Cseke commented

How about changing:

  if ($comments_enabled) {
    $select = '';
    if ($nodeperm_role_enabled) {
      // need to add placeholders for columns to avoid misaligning our union clause
      $select = ", '' as grant_update, '' as grant_delete";
    }
    $comment_sql = 'SELECT c.nid AS cnid, c.uid, "" AS type, c.cid, c.subject, c.status, c.timestamp, c.pid, 0 '. $select .' FROM {node} n, {comments} AS c LEFT JOIN {node_comment_statistics} s ON n.nid = s.nid WHERE c.uid = '. db_escape_string($user->uid);
    $sql .= ' UNION '. $comment_sql;
    $count_sql = 'SELECT COUNT(n.nid) + COUNT(c.cid) FROM '. implode(', ', $node_from) .' '. implode(' ', $node_join) .' LEFT JOIN {comments} AS c ON c.uid = '. db_escape_string($user->uid). ' WHERE '. implode(' ', $node_where);
  }

into

  if ($comments_enabled) {
    $select = '';
    if ($nodeperm_role_enabled) {
      // need to add placeholders for columns to avoid misaligning our union clause
      $select = ", '' as grant_update, '' as grant_delete";
    }
    $comment_sql = 'SELECT c.nid AS cnid, c.uid, "" AS type, c.cid, c.subject, c.status, c.timestamp, c.pid, 0 '. $select .' FROM {node} n, {comments} AS c LEFT JOIN {node_comment_statistics} s ON n.nid = s.nid WHERE c.uid = '. db_escape_string($user->uid);
    $sql .= ' UNION '. $comment_sql;
    $count_sql = 'SELECT COUNT(n.nid) + (SELECT COUNT(c.cid) FROM {comments} c WHERE c.uid='.db_escape_string($user->uid).') FROM '. implode (', ', $node_from) .' '. implode(' ', $node_join) .' WHERE '. implode(' ', $node_where);
  }

Only the $count_sql=... line is changed. This works for me, and it should work with nodeperm_role as well.

frank ralf’s picture

frank ralf commented
Assigned: Unassigned » frank ralf
Status: Needs review » Closed (won't fix)

Pertains to an outdated version. Please create new issue if problem persists.

TIA
Frank