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

Core error when trying to do a search on activity assigned to AND relationship

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 3.3.5
    • Fix Version/s: 3.4.alpha
    • Component/s: Core CiviCRM
    • Labels:
      None

      Description

      To replicate on sandbox

      1) Do an advanced search with criteria
      activity_assigned_to - choose a valid contact (currently demo@example.org)
      relationship spouse of (choose a valid contact (currently Jones, Jennifer)

      Array
      (
      [callback] => Array
      (
      [0] => CRM_Core_Error
      [1] => handle
      )

      [code] => a515ac9c2796ca0e23adbe92c68fc9fc
      [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
      [mode] => 16
      [debug_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
      AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) LEFT JOIN civicrm_activity_assignment ON civicrm_activity.id = civicrm_activity_assignment.activity_id LEFT JOIN civicrm_contact contact_b ON civicrm_activity_assignment.assignee_contact_id = contact_b.id
      LEFT JOIN civicrm_email email_b ON (contact_b.id = email_b.contact_id AND email_b.is_primary = 1) LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.contact_id_b = contact_b.id) WHERE ( contact_b.is_deleted = 0 AND contact_b.sort_name LIKE '%demo@example.org%' AND civicrm_activity_assignment.activity_id = civicrm_activity.id AND civicrm_activity_assignment.assignee_contact_id = contact_b.id AND civicrm_activity.status_id IN (1,2) AND ( contact_b.sort_name LIKE '%jones, jennifer%' AND contact_b.id != contact_a.id ) AND civicrm_relationship.is_active = 1 AND ( civicrm_relationship.end_date is NULL OR civicrm_relationship.end_date >= 20110317 ) AND civicrm_relationship.relationship_type_id = 2 ) AND (contact_a.is_deleted = 0) [nativecode=1066 ** Not unique table/alias: 'contact_b']
      [type] => DB_Error
      [user_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
      AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) LEFT JOIN civicrm_activity_assignment ON civicrm_activity.id = civicrm_activity_assignment.activity_id LEFT JOIN civicrm_contact contact_b ON civicrm_activity_assignment.assignee_contact_id = contact_b.id
      LEFT JOIN civicrm_email email_b ON (contact_b.id = email_b.contact_id AND email_b.is_primary = 1) LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.contact_id_b = contact_b.id) WHERE ( contact_b.is_deleted = 0 AND contact_b.sort_name LIKE '%demo@example.org%' AND civicrm_activity_assignment.activity_id = civicrm_activity.id AND civicrm_activity_assignment.assignee_contact_id = contact_b.id AND civicrm_activity.status_id IN (1,2) AND ( contact_b.sort_name LIKE '%jones, jennifer%' AND contact_b.id != contact_a.id ) AND civicrm_relationship.is_active = 1 AND ( civicrm_relationship.end_date is NULL OR civicrm_relationship.end_date >= 20110317 ) AND civicrm_relationship.relationship_type_id = 2 ) AND (contact_a.is_deleted = 0) [nativecode=1066 ** Not unique table/alias: 'contact_b']
      [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_activity_target ON civicrm_activity_target.target_contact_id = contact_a.id LEFT JOIN civicrm_activity ON ( civicrm_activity.id = civicrm_activity_target.activity_id
      AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 ) LEFT JOIN civicrm_activity_assignment ON civicrm_activity.id = civicrm_activity_assignment.activity_id LEFT JOIN civicrm_contact contact_b ON civicrm_activity_assignment.assignee_contact_id = contact_b.id
      LEFT JOIN civicrm_email email_b ON (contact_b.id = email_b.contact_id AND email_b.is_primary = 1) LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.contact_id_b = contact_b.id) WHERE ( contact_b.is_deleted = 0 AND contact_b.sort_name LIKE '%demo@example.org%' AND civicrm_activity_assignment.activity_id = civicrm_activity.id AND civicrm_activity_assignment.assignee_contact_id = contact_b.id AND civicrm_activity.status_id IN (1,2) AND ( contact_b.sort_name LIKE '%jones, jennifer%' AND contact_b.id != contact_a.id ) AND civicrm_relationship.is_active = 1 AND ( civicrm_relationship.end_date is NULL OR civicrm_relationship.end_date >= 20110317 ) AND civicrm_relationship.relationship_type_id = 2 ) AND (contact_a.is_deleted = 0) [nativecode=1066 ** Not unique table/alias: 'contact_b']"]
      )

        Attachments

          Activity

            People

            • Assignee:
              lobo Donald A. Lobo
              Reporter:
              eileen Eileen McNaughton
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: