Salesforce - Apex - 根据活动历史查询帐户 [英] Salesforce - Apex - query accounts based on Activity History

问题描述

嘿 Salesforce 专家，

Hey Salesforce experts,

我有一个关于高效查询帐户信息的问题.我想根据 activityHistory 对象中的更新查询帐户.我遇到的问题是，无论是否有完整"的 activeHistory，所有帐户都在被检索.那么，有没有一种方法可以编写此查询以仅检索具有 status="complete" 和 Type_for_reporting='QRC' 的 activeHistory 的帐户?

I have a question on query account information efficiently. I would like to query accounts based on the updates in an activityHistory object. The problem I'm getting is that all the accounts are being retrieved no matter if there's "complete" activeHistory or not. So, Is there a way I can write this query to retrieve only accounts with activeHistory that has status="complete" and Type_for_reporting='QRC'?

List<Account> AccountsWithActivityHistories = [    
    SELECT
         Id
        ,Name
        ,( SELECT
                ActivityDate
               ,ActivityType
               ,Type_for_Reporting__c
               ,Description
               ,CreatedBy.Name
               ,Status
               ,WhatId
            FROM ActivityHistories
            WHERE Status ='complete'  and Type_for_Reporting__c = 'QRC'
        )
    FROM Account
];

推荐答案

你有一个关于历史的 WHERE 子句，但你仍然在帐户级别缺少一个.

You have a WHERE clause on the histories but you still miss one on the Account level.

例如，这将仅返回具有联系人的帐户:

For example this would return only Accounts that have Contacts:

SELECT Id, Name
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact) // try with NOT IN too

<小时>

对于活动，它更棘手，因为它们不喜欢以这种方式在 WHERE 中使用.

---

With Activities it's trickier because they don't like to be used in WHERE in that way.

http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select.htm

子查询中当前不支持以下对象:

The following objects are not currently supported in subqueries:

• 活动历史
• 附件
• 活动
• 活动参加者
• 注意
• 开放活动
• 标签(AccountTag、ContactTag 和所有其他标签对象)
• 任务

另外还有 ActivityHistory 定义也有点令人沮丧.

Additionally the fine print at the bottom of ActivityHistory definition is also a bit discouraging.

对没有查看所有数据"权限的用户的以下限制有助于防止出现性能问题:

The following restrictions on users who don’t have "View All Data" permission help prevent performance issues:

• 在关系查询的主子句中，只能引用一个记录.例如，您无法过滤所有包含帐户名称以A"开头；相反，您必须引用单个帐户记录.
• 不能使用 WHERE 子句.
• 您必须为列表中返回的行数指定 499 或更少的限制.
• 您必须按 ActivityDate 升序排序，LastModifiedDate 按降序排序；您可以最后显示空值.为了例如:ORDER BY ActivityDate ASC NULLS LAST、LastModifiedDate DESC.

<小时>

看起来您将需要多个查询.转到任务(或事件，取决于哪个自定义字段可见)，组成一组 AccountId，然后查询帐户?

---

Looks like you will need multiple queries. Go for Task (or Event, depending for which the custom field is visible), compose a set of AccountIds and then query the Accounts?

或者您可以从原始查询中手动过滤列表，将帐户复制到助手列表:

Or you can manually filter through list from your original query, copying accounts to helper list:

List<Account> finalResults = new List<Account>();
for(Account a : [SELECT...]){
    if(!a.ActivityHistories.isEmpty()){
        finalResults.add(a);
    }
}

这篇关于Salesforce - Apex - 根据活动历史查询帐户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！