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

Contact Relationship Report fatal error if used from the API with a relationship_type_id filter (also affects excel export)

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Trivial
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.7.27
    • Fix Version/s: 4.7.30
    • Component/s: CiviReport
    • Labels:
      None
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Contributed Code
    • Verified?:
      No

      Description

      I wanted to use the "Relationship Report" (contact/relationship) via APIv3, using an instance that filters on a relationship_type_id (i.e. lists all relationships of a specific type), but had a fatal error:

      [nativecode=1054 ** Unknown column '11_b_a' in 'where clause']

      $ drush cvapi -u 1 ReportTemplate.getrows instance_id=50
      Array(
          [error_code] => no such field                                                                               
          [sql] => SELECT contact_civireport.sort_name as civicrm_contact_sort_name_a, contact_civireport.id as civicrm_contact_id, contact_b_civireport.sort_name as civicrm_contact_b_sort_name_b, contact_b_civire
      port.id as civicrm_contact_b_id, relationship_type_civireport.label_a_b as civicrm_relationship_type_label_a_b
            FROM civicrm_relationship relationship_civireport
           INNER JOIN civicrm_contact contact_civireport ON ( relationship_civireport.contact_id_a =  contact_civireport.id )
           INNER JOIN civicrm_contact contact_b_civireport ON ( relationship_civireport.contact_id_b = contact_b_civireport.id)                            
           INNER JOIN civicrm_relationship_type_en_US relationship_type_civireport ON ( relationship_civireport.relationship_type_id  = relationship_type_civireport.id  )
          WHERE ( ( contact_civireport.contact_type IN ( 'Organization') ) )
                 AND ( contact_b_civireport.sort_name LIKE '%Example Org%' ) AND ( ( contact_b_civireport.contact_type IN ( 'Organization') ) )
                  AND ( relationship_civireport.is_active = 1 )
                  AND ( relationship_civireport.relationship_type_id IN (11_b_a) )  GROUP BY relati
      onship_civireport.id
                   ORDER BY contact_civireport.sort_name ASC, contact_b_civireport.sort_name ASC  LIMIT 25 [nativecode=1054 ** Unknown column '11_b_a' in 'where clause']

      This also affects reports being exported to XLSX using the civiexcelexport extension (which currently calls "beginPostProcess"  and "buildQuery" directly, but I will change to using the API if possible, and if not to beginCommonPostProcess().

        Attachments

          Activity

            People

            • Assignee:
              mlutfy Mathieu Lutfy
              Reporter:
              mlutfy Mathieu Lutfy
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: