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

Advanced Search on relationships does not work with smart groups

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 4.4.7, 4.5.5
    • Fix Version/s: 4.5.7
    • Component/s: CiviCRM Search
    • Labels:
      None
    • Documentation Required?:
      None

      Description

      I have already reproduced this on the public sandbox at: http://d45.demo.civicrm.org/

      Steps to reproduce:
      1) Create a smart group of contacts (I created a smart group of people born before Jan. 1, 2014, but after Jan. 1, 2004)

      2) Click "Search ... Advanced Search" then go to the "relationship" panel and choose "relationship type" (I choose "Parent of" ). For the field "Target Contact in Group" choose the smart group from step 1.

      3) Click the "search" button. You will now see an error message:

      DB Error: no such field

      SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact_cache `civicrm_relationship_group_contact_cache` ON contact_b.id = `civicrm_relationship_group_contact_cache`.contact_id 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 ) LEFT JOIN civicrm_group_contact civicrm_relationship_group_contact ON civicrm_relationship_group_contact.contact_id = contact_b.id AND civicrm_relationship_group_contact.status = 'Added' WHERE ( ( ( civicrm_relationship_group_contact.group_id IN (6) ) OR ( `civicrm_relationship_group_contact_cache`.group_id IN (6) ) ) AND (
      civicrm_relationship.is_active = 1 AND
      ( civicrm_relationship.end_date IS NULL OR civicrm_relationship.end_date >= 20150127 ) AND
      ( civicrm_relationship.start_date IS NULL OR civicrm_relationship.start_date <= 20150127 )
      ) AND civicrm_relationship.relationship_type_id = 5 ) AND (contact_a.is_deleted = 0) ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc

      [nativecode=1054 ** Unknown column 'contact_b.id' in 'on clause']

      Other notes: I have this issue with all my smart groups, and all relationship types. I do not have this issue with non-smart groups.

        Attachments

          Activity

            People

            • Assignee:
              rohan1990 Rohan Ramesh Katkar
              Reporter:
              sgladstone Sarah Gladstone
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: