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

Participant.get API generates invalid query when searching for role_id IS NULL.

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Trivial
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.7.13
    • Fix Version/s: 4.7.15
    • Component/s: CiviCRM API, CiviEvent
    • Labels:
      None
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Core Team Funds
    • Verified?:
      No

      Description

      This API call:

          $result = civicrm_api3('Participant', 'get', array(
            'sequential' => 1,
            'role_id' => array('IS NULL' => 1),
          ));
      

      returns:

        {
          "error_code": "syntax error",
          "tip": "add debug=1 to your API call to have more info about the error",
          "is_error": 1,
          "error_message": "DB Constraint Violation - possibly participant_contact_id should possibly be marked as mandatory for this API. If so, please raise a bug report."
      }
      

      If you look at the generated query, you can see that role_id is qualified with an empty table name:

      SELECT contact_a.id as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.display_name  as `display_name`, civicrm_event.id as event_id, civicrm_event.title as event_title, civicrm_event.start_date as event_start_date, civicrm_event.end_date as event_end_date, civicrm_participant.id as participant_id, civicrm_participant.fee_level as participant_fee_level, civicrm_participant.fee_amount as participant_fee_amount, civicrm_participant.fee_currency as participant_fee_currency, event_type.label as event_type, civicrm_participant.status_id as participant_status_id, participant_status.label as participant_status, civicrm_participant.role_id as participant_role_id, civicrm_participant.role_id as participant_role, civicrm_participant.register_date as participant_register_date, civicrm_participant.source as participant_source, civicrm_note.note as participant_note, civicrm_participant.is_pay_later as participant_is_pay_later, civicrm_participant.is_test as participant_is_test, civicrm_participant.registered_by_id as participant_registered_by_id, discount_name.title as participant_discount_name, civicrm_participant.campaign_id as participant_campaign_id  FROM civicrm_contact contact_a LEFT JOIN civicrm_participant ON civicrm_participant.contact_id = contact_a.id  LEFT JOIN civicrm_event ON civicrm_participant.event_id = civicrm_event.id  LEFT JOIN civicrm_option_group option_group_event_type ON (option_group_event_type.name = 'event_type') LEFT JOIN civicrm_option_value event_type ON (civicrm_event.event_type_id = event_type.value AND option_group_event_type.id = event_type.option_group_id )  LEFT JOIN civicrm_discount discount ON ( civicrm_participant.discount_id = discount.id ) LEFT JOIN civicrm_option_group discount_name ON ( discount_name.id = discount.price_set_id )  LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_participant' AND\n                                                        civicrm_participant.id = civicrm_note.entity_id ) LEFT JOIN civicrm_option_group option_group_participant_role ON (option_group_participant_role.name = 'participant_role') LEFT JOIN civicrm_option_value participant_role ON ((civicrm_participant.role_id = participant_role.value OR SUBSTRING_INDEX(role_id,'\u0001', 1) = participant_role.value)\n                               AND option_group_participant_role.id = participant_role.option_group_id )  LEFT JOIN civicrm_participant_status_type participant_status ON (civicrm_participant.status_id = participant_status.id)  WHERE  ( civicrm_participant.is_test = 0 AND .role_id IS NULL )  AND (contact_a.is_deleted = 0)  LIMIT 0, 25
      

      You can reproduce this on the dmaster demo site via the API explorer: http://dmaster.demo.civicrm.org/civicrm/api#explorer

        Attachments

          Activity

            People

            • Assignee:
              monish.deb Monish Deb
              Reporter:
              johanv Johan Vervloet
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: