4

I'm trying to set up a query that will return which contacts have over 5 deals with us. We have a custom sObject which is (terribly) named, contacts_deals__c which serves as an intermediary table between contact and deals__c.

Contact is the master sObject, Contacts_Deals__c is the detail. Return all Contacts that have at least 5 details associated with it.

Here's what I was thinking for a query:

SELECT name, (SELECT id 
              FROM contact.contact_deals__r) 
FROM Contact 
HAVING count(contact.contact_deals__r.id) > 4

This of course does not work.

Cutting off the "HAVING" clause returns a list of names and all of the id's associated to the name. I could technically just work from here, but I would like to add the conditional that there should be at least 5 id's returned.

How can I do this?

5

I think you will have to create a rollup summary field in contact object to show the count of deals. This is needed only if you need to get it in one query. Otherwise you can go for apex code with query and then filtering by iteration. Then you can run direct query on contact itself.

SELECT name,rollup__c FROM Contact WHERE rollup__c > 4
  • Yea - that works too... Wish there was a clean SOQL query – ZAR May 7 '15 at 19:01
  • yeah. SOQL is limited in capability :( – Victor May 7 '15 at 19:04
1

Like Victor noted, the best solution since you've got a Master-Detail relationship between these two objects, is to create a rollup summary count field on the Contact which you could then query for directly such as: FROM Contact WHERE Deal_Count__c > 4.

This solution also lets you use this deal count summary field in reports, formulas, etc. that will probably be useful in other ways where you can't write a SOQL query.

That said, if you want to do it in SOQL you will need to do it in two steps because you can't use the AggregateResult type directly in the WHERE clause binding:

// use an aggregate query to get the deal count
List<AggregateResult> results = [SELECT Contact__c contactID
                                        , Count(Id) dealCount
                                    FROM contact_deals__c 
                                    GROUP BY Contact__c 
                                    HAVING COUNT(Id) > 4];

// create a list for the contact Id values
List<Id> contactIds = new List<Id>();

// iterate the aggregate results and add the contact ids to the list
for (AggregateResult ar : results) {

    // cast the value as an Id type
    contactIds.add((Id)ar.get('contactID'));
}

List<Contact> contacts = [SELECT Id
                                , FirstName
                                , LastName
                            FROM Contact 
                            WHERE Id IN :contactIds];

system.debug(contacts);
  • 1
    The rollup is the easiest solution. Mark, thank you for your thorough answer. However, since Victor was the first to suggest the rollup, I feel that I should mark his correct. thanks again! – ZAR May 7 '15 at 19:16
0

You can use Apex:

List <Contact> contactsWithDeals = [Select c.Name,(Select Name, From contact_deals__r ORDER BY Name) From Contact c]; 


public void doSomething() {
    IF (contactsWithDeals.size()>=5) {
        // enter your logic here
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.