CiviCRM Community Forums (archive)

Hello,

I am finalizing a project for a client and putting together a custom report. For the most part this has gone well. The last piece I am trying to do is add custom fields to my report. Originally I tried add the protected class and setting it to participants. This did not work. So I finally got my sql correct in PHPMYADMIN to return all the data I want via joins. This is the sql I eventually ended up with:

function from() {
    $this->_from = "
FROM civicrm_pcp {$this->_aliases['civicrm_pcp']}

LEFT JOIN civicrm_contribution_soft {$this->_aliases['civicrm_contribution_soft']}
          ON {$this->_aliases['civicrm_pcp']}.id =
             {$this->_aliases['civicrm_contribution_soft']}.pcp_id

LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']}
          ON {$this->_aliases['civicrm_contribution_soft']}.contribution_id =
             {$this->_aliases['civicrm_contribution']}.id

LEFT JOIN civicrm_contact {$this->_aliases['civicrm_contact']}
          ON {$this->_aliases['civicrm_pcp']}.contact_id =
             {$this->_aliases['civicrm_contact']}.id

LEFT JOIN civicrm_contribution_page {$this->_aliases['civicrm_contribution_page']}
          ON {$this->_aliases['civicrm_pcp']}.page_id =
             {$this->_aliases['civicrm_contribution_page']}.id

LEFT JOIN civicrm_event {$this->_aliases['civicrm_event']}
          ON {$this->_aliases['civicrm_pcp']}.page_id =
             {$this->_aliases['civicrm_event']}.id

LEFT JOIN civicrm_participant {$this->_aliases['civicrm_participant']}
          ON {$this->_aliases['civicrm_participant']}.event_id =
{$this->_aliases['civicrm_event']}.id

LEFT JOIN civicrm_value_test_4 {$this->_aliases['civicrm_value_test_4']}
          ON {$this->_aliases['civicrm_participant']}.id =
{$this->_aliases['civicrm_value_test_4']}.entity_id";

  }
I am having some trouble getting this to work on the php end of things.

I added this code in the __constructs function:

'civicrm_value_test_4' =>
      array(
        'dao' => 'CRM_Event_DAO_Participant',
        'fields' =>
        array(
          'id' =>
          array(
            'name' => 'id',
            'no_display' => TRUE,
            'required' => TRUE,
          ),
          'beneficiary__test_4' =>
          array('title' => ts('Beneficiary'),
            'default' => TRUE,
          ),
        ),
'filters' =>
        array(
          'title' =>
          array('title' => ts('Beneficiary'),
            'type' => CRM_Utils_Type::T_STRING,
          ),
        ),
        'grouping' => 'pcp-fields',
      ),

I am getting this error when I preview the report

Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= event_civireport.id LEFT JOIN civicrm_value_test_4 value_test_4_civir' at line 25, 1064

For one I am not sure if I used the correct "dao" to access the custom field. My logic was to use CRM_Event_DAO_Participant as this custom field was filled in during an event registration. At this point I am very stumped though. Am I on the right track? Is there something glaring? Still rather inexperienced with php coding and this pretty much goes beyond my limits. Any help would be greatly appreciated.