I am using domain 2.0-rc9 as well as domain_adv 1.0-rc1. On many occasions I am getting duplicate nodes. It is so prevalent that I won't try to describe each situation. But, for example, if I create nodes which have access on multiple sites, or site wide access, the problem shows up.

The reason (I believe) is obvious. The LEFT JOIN's present in the access queries are causing multiple rows to turn up whenever multiple affiliates are assigned to nodes. This also happens on other modules (such as og) which use JOINs to detect access situations.

One solution is to replace the JOIN with a subselect. For example, if you replace domain_adv_db_rewrite_sql with the following, the access logic is the same, but additional rows will never be introduced into the result query:

function domain_adv_db_rewrite_sql($query, $primary_table, $primary_field, $args) {
  switch ($primary_field) {
    case 'nid':
      $global_or = '';
      if ($GLOBALS['_domain']['domain_id']) {
        $global_or = ' OR (dasub.realm = "domain_site" AND dasub.gid = 0)';
      }
      $return = array();
      $return['where'] = '((SELECT COUNT(*) FROM domain_access dasub WHERE ('. 
        $primary_table . '.nid = dasub.nid AND ((dasub.realm = "domain_id" AND dasub.gid = '. 
        $GLOBALS['_domain']['domain_id'] .')' . $global_or . '))) > 0)';
      return $return;
      break;
  }
}

I believe this is pretty foolproof. However, it depends upon using a database which supports subselects, as MySQL 5+ does. So, I'm not making any claim that this is a perfect solution for the module.

Many people have tried to solve these problems by introducing DISTINCT queries, or turning on "Distinct" features in the views they create. But, when there are a lot of nodes, this can really create egregiously long query times. For example, in some of my views, the presence of duplicates creates 8 times more records than there should be (the combination of joins for og + domain are multiplicative).

I'd be curious about your assessment about this.

Comments

cardentey’s picture

cardentey CreditAttribution: cardentey commented

me too.

Skirr’s picture

Skirr CreditAttribution: Skirr commented

Have same problem. Is it possible to make a patch?

garywiz’s picture

garywiz CreditAttribution: garywiz commented

I stopped using domain_adv shortly after I wrote that post. It turned out that the combination of organic groups, domains, etc. was causing many problems of this nature elsewhere and so many duplicates because of multiple entries in the node access table. I hear this is being fixed in D7 core, but am not sure. This is really a fundamental flaw in the way node access queries are formulated and used.

In the mean time, I made a simple change to node.module (GASP, I know), that completely fixed the problem in every single case on our site. It's not good to hack core, so I would be hesitant to produce such a patch. But, the change is really simple and converts the code in node_db_rewrite_sql() so that it uses subselects instead of joins. I was desperate and needed a solution and just had to be pragmatic.

From my modified node.module...

/**
 * Implementation of hook_db_rewrite_sql
 */
function node_db_rewrite_sql($query, $primary_table, $primary_field) {
  if ($primary_field == 'nid' && !node_access_view_all_nodes()) {
    $wsql = _node_access_where_sql();
    if ($wsql) {
      $return['where'] = '((SELECT COUNT(*) FROM node_access na WHERE '.$primary_table.
      '.nid = na.nid AND ('. $wsql .')) > 0)';
    }
    return $return;
  }
}
t.lan’s picture

t.lan CreditAttribution: t.lan commented

Hi garywiz,

I'm running in the same problems now.

Do you have any update on this topic?

Is your modification from #3 still succesfully running?

Regards, Thomas

garywiz’s picture

garywiz CreditAttribution: garywiz commented

Yes, this has been working fine for us after many weeks of development and testing and we have a very complex site involving multiple domains, og, domain_taxonomy. Again, the patch is a core change, so be warned.

t.lan’s picture

t.lan CreditAttribution: t.lan commented

Seeing no other chance to fix our problems I will have to use this. And would like to thank you for making your fix public!

I added an Issue in Drupal core with the hope to have some feedback from the core maintainers. http://drupal.org/node/651526

Regards, Thomas

joeredhat-at-yahoo.com’s picture

joeredhat-at-yahoo.com CreditAttribution: joeredhat-at-yahoo.com commented

Thank you garywiz for writing this patch. It's working well for me in production, and I agree that it's a much more straightforward solution than the band-aid fixes proposed at http://drupal.org/node/284392.

garywiz’s picture

garywiz CreditAttribution: garywiz commented

I have created a separate issue with a formal patch at #681760: Try to improve performance and eliminate duplicates caused by node_access table joins. Please continue any discussion or comments about node.module patches there.

This thread started out as a separate discussion of a duplicate problem introduced by JOIN queries in domain_adv and the domain module in general. The new patch solves both problems along with many others.

D2ev’s picture

D2ev CreditAttribution: D2ev commented

hook_views_pre_render can help. i have used it eliminate node have different nid but have duplicate user id. But I want to filter the userid. we can manipulate this $view->result and can create our own views result set.

function harvest_views_pre_render(&$view) {
 $prevuid = array();
 if ($view->name == 'blog') {
  if ($view->current_display == 'page_2') {
   foreach ($view->result as $row) {
    if(!in_array($row->users_uid,$prevuid)) {
     $new_view_result[] = $row;
    }
    $prevuid[] = $row->users_uid;
   }
   $view->result = $new_view_result;
  }
 }
}