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

Activities dashlet - sort by Added By throws fatal DB error (bad query)

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Major
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.4.2
    • Fix Version/s: 4.4.3
    • Component/s: Core CiviCRM, NYSS
    • Labels:
      None
    • Funding Source:
      Core Team Contract

      Description

      Please track time for this issue at http://dev.nysenate.gov/issues/7354
      ================
      If you load the Activities dashlet (civicrm/dashboard?reset=1) and then click 'Added By' column to sort by source contact, you'll see Processing message but the table doesn't sort.

      If you use the Firebug console to load the request in a separate tab you'll see that the query is invalid - full error w/ bad query is pasted below.

      $sortMapper in CRM_Activity_Page_AJAX::getContactActivity() defines that sort item as source_contact_name, which then throws the unkown column in order clause.

      ================
      Database Error Code: Unknown column 'source_contact' in 'order clause', 1054
      Additional Details:

      Array
      (
      [callback] => Array
      (
      [0] => CRM_Core_Error
      [1] => handle
      )

      [code] => -19
      [message] => DB Error: no such field
      [mode] => 16
      [debug_info] => INSERT INTO civicrm_temp_activity_details_4cf7f8308e4d9b5495d97ddc725e9c54 (activity_id,activity_date_time,status_id,subject,source_record_id,activity_type_id,activity_type,case_id,case_subject,campaign_id )
      SELECT DISTINCT tbl.* from (
      SELECT civicrm_activity.id as activity_id
      ,
      civicrm_activity.activity_date_time,
      civicrm_activity.status_id,
      civicrm_activity.subject,
      civicrm_activity.source_record_id,
      civicrm_option_value.value as activity_type_id,
      civicrm_option_value.label as activity_type,
      null as case_id, null as case_subject,
      civicrm_activity.campaign_id as campaign_id

      from civicrm_activity
      left join civicrm_option_value on
      civicrm_activity.activity_type_id = civicrm_option_value.value
      left join civicrm_option_group on
      civicrm_option_group.id = civicrm_option_value.option_group_id

      INNER JOIN civicrm_activity_contact ac ON ac.activity_id = civicrm_activity.id
      INNER JOIN civicrm_contact contact ON ac.contact_id = contact.id

      where
      1
      AND civicrm_option_group.name = 'activity_type' AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 AND civicrm_activity.is_test= 0 AND civicrm_activity.status_id = 1 AND ( civicrm_option_value.component_id IS NULL OR civicrm_option_value.component_id IN ( 1,2,3,4,6,8 ) )
      )
      as tbl
      LEFT JOIN civicrm_case_activity ON ( civicrm_case_activity.activity_id = tbl.activity_id )
      WHERE civicrm_case_activity.id IS NULL ORDER BY source_contact asc LIMIT 0, 25 [nativecode=1054 ** Unknown column 'source_contact' in 'order clause']
      [type] => DB_Error
      [user_info] => INSERT INTO civicrm_temp_activity_details_4cf7f8308e4d9b5495d97ddc725e9c54 (activity_id,activity_date_time,status_id,subject,source_record_id,activity_type_id,activity_type,case_id,case_subject,campaign_id )
      SELECT DISTINCT tbl.* from (
      SELECT civicrm_activity.id as activity_id
      ,
      civicrm_activity.activity_date_time,
      civicrm_activity.status_id,
      civicrm_activity.subject,
      civicrm_activity.source_record_id,
      civicrm_option_value.value as activity_type_id,
      civicrm_option_value.label as activity_type,
      null as case_id, null as case_subject,
      civicrm_activity.campaign_id as campaign_id

      from civicrm_activity
      left join civicrm_option_value on
      civicrm_activity.activity_type_id = civicrm_option_value.value
      left join civicrm_option_group on
      civicrm_option_group.id = civicrm_option_value.option_group_id

      INNER JOIN civicrm_activity_contact ac ON ac.activity_id = civicrm_activity.id
      INNER JOIN civicrm_contact contact ON ac.contact_id = contact.id

      where
      1
      AND civicrm_option_group.name = 'activity_type' AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 AND civicrm_activity.is_test= 0 AND civicrm_activity.status_id = 1 AND ( civicrm_option_value.component_id IS NULL OR civicrm_option_value.component_id IN ( 1,2,3,4,6,8 ) )
      )
      as tbl
      LEFT JOIN civicrm_case_activity ON ( civicrm_case_activity.activity_id = tbl.activity_id )
      WHERE civicrm_case_activity.id IS NULL ORDER BY source_contact asc LIMIT 0, 25 [nativecode=1054 ** Unknown column 'source_contact' in 'order clause']
      [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_temp_activity_details_4cf7f8308e4d9b5495d97ddc725e9c54 (activity_id,activity_date_time,status_id,subject,source_record_id,activity_type_id,activity_type,case_id,case_subject,campaign_id )
      SELECT DISTINCT tbl.* from (
      SELECT civicrm_activity.id as activity_id
      ,
      civicrm_activity.activity_date_time,
      civicrm_activity.status_id,
      civicrm_activity.subject,
      civicrm_activity.source_record_id,
      civicrm_option_value.value as activity_type_id,
      civicrm_option_value.label as activity_type,
      null as case_id, null as case_subject,
      civicrm_activity.campaign_id as campaign_id

      from civicrm_activity
      left join civicrm_option_value on
      civicrm_activity.activity_type_id = civicrm_option_value.value
      left join civicrm_option_group on
      civicrm_option_group.id = civicrm_option_value.option_group_id

      INNER JOIN civicrm_activity_contact ac ON ac.activity_id = civicrm_activity.id
      INNER JOIN civicrm_contact contact ON ac.contact_id = contact.id

      where
      1
      AND civicrm_option_group.name = 'activity_type' AND civicrm_activity.is_deleted = 0 AND civicrm_activity.is_current_revision = 1 AND civicrm_activity.is_test= 0 AND civicrm_activity.status_id = 1 AND ( civicrm_option_value.component_id IS NULL OR civicrm_option_value.component_id IN ( 1,2,3,4,6,8 ) )
      )
      as tbl
      LEFT JOIN civicrm_case_activity ON ( civicrm_case_activity.activity_id = tbl.activity_id )
      WHERE civicrm_case_activity.id IS NULL ORDER BY source_contact asc LIMIT 0, 25 [nativecode=1054 ** Unknown column 'source_contact' in 'order clause']"]
      )

        Attachments

          Activity

            People

            • Assignee:
              dgg David Greenberg
              Reporter:
              dgg David Greenberg
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 2 hours
                2h