Compare Junction Object List with its Master-Objects lists

Question

I have two different custom objects Catalogs and Products. I have a visualforce page where user can select multiple catalogs and multiple products from two different picklists respectively. I also have a junction object called Catalog-Products, a child to both catalogs and products. The selected catalogs and products are created as records for Catalog-Products Junction object. So, I have two different lists of each of those object called as CatalogList and ProductList which are captured from that mulit-select picklists (that I explained above). There might be already some Catalog-Product records with the selected product and selected Catalogs.

How can I compare both those lists with existing Catalog-Product junction object records and insert only new records?

Thanks.

Update- I am stuck with the code here

list<Catalog_Product__c> existingList = [select Id, Product__c, Catalog__c from Catalog_Product__c where Product__c IN: prodIds and Catalog__c IN: catalogIds];

//prodIds and catalogIds are Ids retrieved from selected values of Picklists 

list<Catalog_Product__c> newList = new list<Catalog_Product__c>();

 for(Id clogId: catalogIds)
 {
   for(Id prdId: prodIds)
   {
     Catalog_Product__c cpd = new Catalog_Product__c();
     cpd.Product__c = prdId;
     cpd.Catalog__c = clogId;
     newList.add(cpd);
   }
}

newList and existingList are the two lists of same Object . Need help in comparing those lists and inserting the non-duplicate values.

Answer 1

This is a good use case for leveraging an External ID field on the Catalog_Product__c object. The value that is contained in this field will be comprised of two keys concatenated together to create a unique value that represents the junction. In traditional database terminology this is known as a "compound key" and allows you to use a few bits of information to create a unique identifier that might not exist naturally.

You can use the external id field to also enforce a uniqueness constraint (preventing duplicates) as well as greatly simplifying your code as there's no need to search for any records are already in existence. If there are records in the junction object already, they will simply be updated.

Assuming you've created an External ID field on the Catalog_Product__c object with the Unique attribute set true, which is named CatProd_ExtId__c and will have values in it that take the form "CatalogId + ProductId" (2 SFDC Id values of the parents, 30 characters in total), your save method can look like this:

public void save() {

    // the list of junction object records being inserted or updated later (upsert)
    List<Catalog_Product__c> catalogProductsToUpsert = new List<Catalog_Product__c>();

    // assuming catalogIDs are the items selected by the user
    for (Id catalogId: catalogIds) {
       for (Id productId: prodIds) {
         Catalog_Product__c cpd = new Catalog_Product__c();
         cpd.Product__c = productId;
         cpd.Catalog__c = catalogId;

         // concatenate the two ID values together to create the compound key
         cpd.CatProd_ExtId__c = productId + catalogId;

         catalogProductsToUpsert.add(cpd);
       }
    }

    // only perform the DML operation if there are records to upsert
    if (!catalogProductsToUpsert.isEmpty()) {

        // execute the upsert, specifying our CatProd_ExtId__c field as the identifier to use
        upsert catalogProductsToUpsert Catalog_Product__c.Fields.CatProd_ExtId__c;
    }
}

Answer 2

If you would rather not use an upsert, you can leverage the same compound key idea with code like this:

List<Catalog_Product__c> existingList = [select Id
                                         , Product__c
                                         , Catalog__c 
                                         from Catalog_Product__c 
                                         where Product__c IN: prodIds 
                                             AND Catalog__c IN: catalogIds];

// using a set to store the unique list of records that are already in existence
Set<String> existingJunctionSet = new Set<String>();

for (Catalog_Product__c catProd : existingList) {
    existingJunctionSet.add(catProd.Catalog__c + catProd.Product__c);
}

//prodIds and catalogIds are Ids retrieved from selected values of Picklists 

list<Catalog_Product__c> newList = new list<Catalog_Product__c>();

// iterate the lists and if the pair isn't in the set above
// add it to the list for insert
for(Id clogId: catalogIds) {
    for(Id prdId: prodIds) {

        // only add to the list if it's not in the existing set already
        if (!existingJunctionSet.contains(clogId + prdId)) {
            Catalog_Product__c cpd = new Catalog_Product__c();
            cpd.Product__c = prdId;
            cpd.Catalog__c = clogId;
            newList.add(cpd);

            // to prevent duplicates as we iterate, add this new entry to the set too
            existingJunctionSet.add(clogId + prdId);
        }
    }
}

Answer 3

If these products and catalogs are a small set of data, consider showing a custom multiselect picklist with combination of Catalog-Product available and already existing in left and right panels. You can find existing component in below link,

https://github.com/metadaddy-sfdc/Visualforce-Multiselect-Picklist

Answer 4

If there is only insertion of records required rather than updating any existing records this logic also works fine just for removing the duplicate records by comparing two lists of Catalog_Product__c Object (Existing and NewList).

list<Catalog_Product__c> existingList = [select Id, 
                                         Product__c, 
                                         Catalog__c from Catalog_Product__c where 
                                         Product__c IN: prodIds 
                                         and Catalog__c IN: catalogIds];

//prodIds and catalogIds are Ids retrieved from selected values of Picklists 

list<Catalog_Product__c> newList = new list<Catalog_Product__c>();

 for(Id clogId: catalogIds)
 {
   for(Id prdId: prodIds)
   {
     Catalog_Product__c cpd = new Catalog_Product__c();
     cpd.Product__c = prdId;
     cpd.Catalog__c = clogId;
     newList.add(cpd);
   }
}
if(existingList!=null && !existingList.isEmpty())
{
   for(Catalog_Product__c cpd: existingList)
   {
      Integer i=0;
      while(i<newList.size())
      {
         if(newList[i].Product__c == cpd.Product__c 
            && newList[i].Catalog__c ==cpd.Catalog__c)
         {
            newList.remove(i);
            break;
         }
         else
         {
            i++;
         }  
      } 
    }
 }

if(!newList.isEmpty()){
insert newList;
}