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?
Easiest way would be 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