CiviCRM Community Forums (archive)

Can you replicate this on http://sandbox.joomla.civicrm.org

Kurund

Just wanted to bump this - any ideas for what could be causing the count disparity? I was thinking it could be because I've made some changes in the MySQL database - namely deleting records of group removals, because the glitch that causes removed groups to show up on a data export was causing serious problems. But I'm not sure where the count number is coming from, so I don't know where to look to fix it.

That sounds like it could be the cause - we do have a lot of group activity, but because I've deleted all of the subscription history related to removals, there's really no way for us to check the numbers and see if there's a correlation.

I couldn't find a fix for this but it seems to work on the sandbox yet on 3.4.6 / 4.0.6 codebase it didn't make sense that it would ever work correctly.

In the case I looked into the join table is memberships but there are many that would produce the same symptoms

The search I was seeing for counting contacts with memberhships of more than one type was

SELECT count(*)  FROM civicrm_contact contact_a
LEFT JOIN civicrm_membership ON civicrm_membership.contact_id = contact_a.id  LEFT JOIN civicrm_contribution_recur ccr ON ( civicrm_membership.contribution_recur_id = ccr.id ) WHERE   civicrm_membership.membership_type_id IN ('27','26','29','34','14','30','32','31','35')  AND civicrm_membership.is_test = 0 )  AND (contact_a.is_deleted = 0)

There is no distinct or Group By and some contacts have more than one membership so the count will always be wrong.

By comparison the actual retrieval query ended in GROUP BY contact_a.id & hence returned the correct amount.

So, the COUNT is always wrong & the amount exported is correct.

I searched in JIRA & in the code for a fix that may have taken place but didn't find it - however, I * think* this must have been fixed for 4.1.

Either that or the issue is affected by the use of ACLs on the site. However I was testing with a user with 'view all contacts'

I will attach the backtrace on the 2 queries in case it's of use.

Any idea of the JIRA - I couldn't find it.

Am also investigating a complaint of this in 3.4.7

I hit another permutation on this - I found that the left join on our ACL hook (multisite module) was causing the extra records.

I'm not sure what the 4.1 fixes affecting this are but what I saw looking at the code is that the advanced search query is quite expensive (depending on the criteria) & it is run 4 times:

1) Get the Pager
2) Get the count
3) Get the contacts to show
4) Get all eligible contact ids & stash in the prevnext cache table.

It seems that it would make sense to do #4 first & use it to reduce the load of the other 3 queries. There would need to be some tweaks on the index of the table.

In the iterim I have applied the following to make the count correct on our site - it gets the count of the cached items.


Index: sites/all/modules/civicrm/CRM/Core/Selector/Controller.php
===================================================================
--- sites/all/modules/civicrm/CRM/Core/Selector/Controller.php (revision 1627)
+++ sites/all/modules/civicrm/CRM/Core/Selector/Controller.php (working copy)
@@ -341,7 +341,11 @@
             if ($this->_output & self::SESSION) {
                 $this->_store->set( "{$this->_prefix}columnHeaders", $columnHeaders );
                 $this->_store->set( "{$this->_prefix}rows"         , $rows          );
-                $this->_store->set( "{$this->_prefix}rowCount"     , $this->_total  );
+                $this->_store->set( "{$this->_prefix}rowCount"     ,
+                             CRM_Core_DAO::singleValueQuery("SELECT COUNT(*) FROM
+                             civicrm_prevnext_cache
+                             WHERE cacheKey = 'civicrm search {$this->_object->_formValues['qfKey']}'")  );
+
                 $this->_store->set( "{$this->_prefix}rowsEmpty"    , $rowsEmpty     );
                 $this->_store->set( "{$this->_prefix}qill"         , $qill          );
                 $this->_store->set( "{$this->_prefix}summary"      , $summary       );