1

I have created several custom reports for CiviReport. To create the SQL for these reports I have used 'Toad for MySQL' to visualise the proposed end-result.

In my latest report I am trying to return a report of Participants that include their current Membership information; in the format:

Sort Name, Job Title, Employer, Email, Participant Status, Role, Membership Type, Membership Status, Membership End Date

This report should include participants that do not have a membership. The SQL is as follows:

SELECT civicrm_email.is_primary,
   civicrm_contact.sort_name,
   civicrm_contact.job_title,
   civicrm_contact.employer_id,
   civicrm_email.email,
   civicrm_participant.status_id,
   civicrm_participant.role_id,
   civicrm_membership_type.name,
   civicrm_membership_status.label,
   civicrm_membership.end_date
FROM ((((ceed_civicrm_db.civicrm_participant civicrm_participant
         INNER JOIN ceed_civicrm_db.civicrm_contact civicrm_contact
            ON (civicrm_participant.contact_id = civicrm_contact.id))
        LEFT OUTER JOIN ceed_civicrm_db.civicrm_membership civicrm_membership
           ON (civicrm_membership.contact_id = civicrm_participant.contact_id))
       LEFT OUTER JOIN
       ceed_civicrm_db.civicrm_membership_type civicrm_membership_type
          ON (civicrm_membership.membership_type_id =
              civicrm_membership_type.id))
      LEFT OUTER JOIN
      ceed_civicrm_db.civicrm_membership_status civicrm_membership_status
         ON (civicrm_membership.status_id = civicrm_membership_status.id))
     INNER JOIN ceed_civicrm_db.civicrm_email civicrm_email
        ON (civicrm_email.contact_id = civicrm_contact.id)
WHERE (civicrm_participant.event_id = 887) AND (civicrm_email.is_primary = 1)

When constructed in CiviReport, the resulting report excludes rows where the Membership is NULL, in effect changing the SQL to:

SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.employer_id as civicrm_contact_employer_id, email_civireport.email as civicrm_email_email, participant_civireport.role_id as civicrm_participant_role_id, membership_status_civireport.label as civicrm_membership_status_label, membership_type_civireport.name as civicrm_membership_type_name, membership_civireport.end_date as civicrm_membership_end_date  

        FROM civicrm_participant participant_civireport

        INNER JOIN civicrm_contact contact_civireport 
        ON participant_civireport.contact_id  = contact_civireport.id 

        LEFT OUTER JOIN civicrm_membership membership_civireport
        ON membership_civireport.contact_id = participant_civireport.contact_id 

        LEFT OUTER JOIN civicrm_membership_type membership_type_civireport
        ON membership_civireport.membership_type_id = membership_type_civireport.id 

        LEFT OUTER JOIN civicrm_membership_status membership_status_civireport
        ON membership_civireport.status_id = membership_status_civireport.id 

        INNER JOIN civicrm_email email_civireport 
        ON participant_civireport.contact_id = email_civireport.contact_id 

                WHERE participant_civireport.is_test = 0 
                AND email_civireport.is_primary = 1

                AND membership_civireport.is_test = 0
                AND 

        ( participant_civireport.event_id = 887 ) AND contact_civireport.is_deleted = 0    ORDER BY contact_civireport.sort_name   LIMIT 0, 50

The queries 'should' return identical data, but don't. Putting the latter SQL into 'Toad' removes the NULL values, but changes the SQL for visualisation to show the joins exactly as CiviReport but not the original query.

I'm probably missing something...

Any help appreciated. I'll post all the code after fix.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.