0

Requirement:

A component that displays duplicate Contacts (having the same Name) along with the Account each of those Contacts is linked to.

Here is my current query:

[SELECT Name, count(Id), Account.Name FROM Contact GROUP BY Name HAVING count(Id)>1];

Basically, it's using an aggregate function to find the duplicate Contacts and then traversing to Account.Name in the query

But this isn't working. Error message in console:

duplicate alias: Name

I suspect this is because the parser can't distinguish between the Contact.Name and the Account.Name field in our query. Since, according my understanding, using AS isn't possible for fields, only for tables in SOQL, I'm at the end of my latin.

Anybody has an idea how to resolve this?

0

You are getting that error because you are selecting Account.Name field without grouping it. You either have to group it or you have to aggregate it.

So basically, this below code should suffice your needs

Set<string> setNames = new Set<string>();
for(AggregateResult ar : [SELECT  Name, count(Id) ContactCount FROM Contact GROUP BY Name HAVING count(Id)>1]){
    setNames.add(string.valueOf(ar.get('Name')));
}

List<Contact> dupContacts = [SELECT Id, Name, Account.Name FROM Contact WHERE Name IN :setNames order by Name];
  • Thanks VIjay. I see what you're doing and it's a highly intelligent idea, but that part "as per your requirement, you want to get the duplicate Contacts under same Account" isn't true: I want to get all accounts of a duplicate-contact-group. As each of the contacts might have a different account (perhaps the Contact works for several companies), but they're still duplicates. – Blake Feb 26 at 15:09
  • Ok, got you.. Updated the answer, that should work for you. – Vijay Ganji Feb 26 at 15:27
  • Beautiful. Thank you Sir. (to someone else reading: replace "put" with "add", but that was just a minor typo in otherwise brilliant thinking) – Blake Feb 26 at 16:08
  • My pleasure !! and thanks for the observation !! Corrected it :) – Vijay Ganji Feb 26 at 16:14
1

Sometimes SOQL and Apex error messages are rather unintuitive.

I think here the "real" issue is that you're trying to SELECT a field (Account.Name) you neither group nor aggregate in your query. You cannot do that using Aggregate SOQL; each field you SELECT must either by aggregated (count(Id)) or GROUPed BY (Name). Note that each duplicate-group of Contacts may be on different Accounts!

You'd need to run this query to generate a List<AggregateResult> and then iterate over your results to accumulate a Set<String> of the duplicate Name fields. You'd need to then run a non-aggregate SOQL query along the lines of

SELECT Id, Name, Account.Name FROM Contact WHERE Name IN :nameSet

to pull back the actual results you can display.

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.