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

Advanced Search (Activity AND Relationship) fails with "Not unique table/alias: 'contact_b'"

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Major
    • Resolution: Fixed/Completed
    • Affects Version/s: 3.3.5
    • Fix Version/s: 3.4.alpha
    • Component/s: CiviCRM Search
    • Labels:
      None

      Description

      Discovered while testing v3.4.alpha1, but verified that it also affects v3.3.5: Advanced search on both Activity and Relationship in the same search fails with "Not unique table/alias: 'contact_b'". Commented error details (with offending file, line number) below:

      [debug_info] =>
      SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name
      FROM civicrm_contact contact_a
      LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 )
      LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id
      LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id
      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 )

      – CRM/Activity/BAO/Query.php line 384
      LEFT JOIN civicrm_contact contact_b ON civicrm_activity.source_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)

      – CRM/Contact/BAO/Query.php line 2100
      LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_a = contact_a.id )
      LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_b = contact_b.id )

      WHERE ( civicrm_state_province.id IN (1037) AND civicrm_country.id = 1228 AND contact_b.is_deleted = 0 AND contact_b.sort_name LIKE '%%' AND civicrm_activity.source_contact_id = contact_b.id AND civicrm_activity.status_id IN (1,2) AND civicrm_relationship.is_active = 1 AND ( civicrm_relationship.end_date is NULL OR civicrm_relationship.end_date >= 20110225 ) AND civicrm_relationship.relationship_type_id = 17 ) AND (contact_a.is_deleted = 0)
      [nativecode=1066 ** Not unique table/alias: 'contact_b']

        Attachments

          Activity

            People

            • Assignee:
              lobo Donald A. Lobo
              Reporter:
              fen Fen Labalme
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 1 hour
                1h
                Remaining:
                Remaining Estimate - 1 hour
                1h
                Logged:
                Time Spent - Not Specified
                Not Specified