Apex Code Development You need to sign in to do that
Don't have an account?
sobject query for contact records only if they have activity history
I want to create an SOQL query returning all Contacts that have ActivityHistory. If I do this:
SELECT Id, Name, (SELECT CallType,ActivityDate,Subject FROM ActivityHistories) FROM Contact
...then I get ALL Contacts regardless of whether they have ActivityHistories or not.
So then I tried:
SELECT Id, Name,
(SELECT CallType,ActivityDate,Subject
FROM ActivityHistories where ActivityDate != null and Id != null) FROM Contact
...and STILL I get back Contacts without ActivityHistories. Does anyone know how to formulate
a query that returns all Contacts and only Contacts which have ActivityHistories?
P.S. sorry for the cross-post (I posted in General Discussion by mistake - first post here)
I was just made aware that there is already a sort of roll-up of activity on the Contact (and Account, etc.) - a standard field called LastActivityDate, so now I can accomplish what I want with a simple query:
All Answers
Your main query needs something in its where statement to get your desired outcome. Unfortunately I don't think you can create rollup summaries or triggers on ActivityHistory, essentially limiting your available options to using more than 1 soql query.
I am not as familar with the ActivityHistory object but I believe you would have to do something similar to the following:
Set<Id> whatIds = new Set<Id>(); for(ActivityHistory ah : [SELECT WhatId FROM ActivityHistory]) { whatIds.add(ah.WhatId); } List<Contact> contactsWithoutActivityHistory = [SELECT Id, Name FROM Contact WHERE Id not in :whatIds];Yes I am aware this solution sucks.
Use this
SELECT Id, Name FROM Contact where id in (SELECT WhoId FROM ActivityHistories)
if whoid does not work then you can try with whatid
Wizrad,
Thanks for the tip, however I want to try to solve this in a single query, if possible, otherwise I'll resort to this solution.
-sObject
Shashikant,
Unfortunately your query suggestion does not work - the error is "malformed query". I also tried:
SELECT Id, Name FROM Contact where id in (SELECT WhatId FROM ActivityHistories)
SELECT Id, Name FROM Contact where id in (SELECT Id FROM ActivityHistories)
...these also resulted in "malformed query".
I even tried:
Select c.Id, c.Name From Contact c where c.Id in (Select ah.WhoId From c.ActivityHistories ah)
...and this resulted in the error: INVALID_TYPE:row 1:72 - first sObject of the FROM statement must be a valid sObject
Any other ideas? It's looking like I might have to resort to Wizrad's two-step approach.
I tried this also:
select c.Id, c.Name from Contact c where (select count() from c.ActivityHistories) > 0
...and the error is on the second select - are you sure "select" is allowed in "where" clause?
I'm not sure how big your org is but I feel like all these proposed solutions are bound to hit governor limits if there is a sizable number of users or data.
AFAIK you cant put a subquery in your where clause. If you can thats sweet. As far as 2 queries vs 1, if you use a subquery it still counts as 2 queries. You could slightly optimize my above solution if you do the original query you were trying as part of a for each loop, iterate over the results adding a result to a list when result.ActivityHistories.isEmpty().
I guess the solution that will allow for one query and be governor limit friendly would be as follows. Add a checkbox field to the Contact sObject. Call it something like Unmodified__c and default it to true. Put a trigger on Event and Task (If you can even do this), they will both do the same thing. Iterate over Trigger.new, if the parent record is a contact and one of the fields that has history tracking enabled has changed, mark Unmodified__c on the parent contact as false. Then your soql query can just be [SELECT Id FROM Contact WHERE Unmodified__c = false]. The drawback to this solution is that its obviously way more expensive to implement.
Wizrad,
I see where you're going with that, but not only do I need to get only Contacts which have an activity, but I need to filter by ActivityDate, so some sort of subquery is still needed. The solution I'm going to use, for now, involves dynamic SOQL - the query will still return contact records with no ActivityHistory, so I still need to post-process the results. If I hit govenor limits, then I'll try your idea with the trigger. So here's what I have so far:
private static string qstr = 'Select c.Name, c.Id, ' + '(Select Id, IsTask, ActivityDate, ActivityType, CallType, Type__c ' + 'From ActivityHistories where ActivityDate > 2010-12-31) From Contact c limit 100'; public static testMethod void main() { List<sObject> recs = Database.query(qstr); for (sObject o : recs) { SObject[] childRecordsFromParent = o.getSObjects('ActivityHistories'); if (childRecordsFromParent != null) { system.debug('********* ' + o); for (sObject a : childRecordsFromParent) system.debug(' ********* ' + a); } } }I was just made aware that there is already a sort of roll-up of activity on the Contact (and Account, etc.) - a standard field called LastActivityDate, so now I can accomplish what I want with a simple query: