Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-12753

Searching for contacts by date created

    Details

    • Type: Improvement
    • Status: Done/Fixed
    • Priority: Minor
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.3.3
    • Fix Version/s: 4.4.4
    • Component/s: None
    • Labels:
      None

      Description

      I noticed that there in 4.3.3 there is a created_date column in the contact table, but that the advanced search does not use it. Instead, if I filter contacts by added and "previous two months", the server starts crunching away on a very demanding query that uses the activity log tables and can take minutes to complete:

      SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_log ON (civicrm_log.entity_id = contact_a.id AND civicrm_log.entity_table = 'civicrm_contact') LEFT JOIN civicrm_contact contact_b_log ON (civicrm_log.modified_id = contact_b_log.id) WHERE (
      ( civicrm_log.modified_date >= '20130401000000' ) AND
      ( civicrm_log.modified_date <= '20130531235959' )
      AND civicrm_log.id IN ( select id from civicrm_log group by entity_id order by id ) ) AND (contact_a.is_deleted = 0) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc

      One obvious issue here is the nested query, which MySQL dislikes, but also, it should have just used created_date in the contacts table. Same for modified date.

      Also, note that when toggling between "Added" and "Modified", the caption is still "Modified by" and "Modified between". I would suggest adding controls for Created Date, Last Modified Date inside the "Basic Criteria" Section of the form.

        Attachments

          Activity

            People

            • Assignee:
              lobo Donald A. Lobo
              Reporter:
              rares Rares P
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 6 hours
                6h
                Remaining:
                Time Spent - 4 hours Remaining Estimate - 2 hours
                2h
                Logged:
                Time Spent - 4 hours Remaining Estimate - 2 hours
                4h