I have 4 custom objects (as shown in the schema diagram). And they are associated using a lookup relationship. Custom Product Schema Diagram

Now the objective of my question is to retrieve Product profile(product) for a given set of characteristics Value (multiple characteristics values). e.g. What product should I use if I need Color as Black and Material Type as Solid. Where Color and Material Type are Characteristics and Black & Solid are Characteristics Values.

So I was looking around some SOQL / SOQL queries to resolve this issue, but so far not able to get the desired output. I have also attached sample table with some data.

Sample data in each object

Here are some of the SOQL queries I have tried so far :-

SELECT Product_Characteristic__c.Product_Profile__c
FROM Product_Characteristic__c
WHERE Product_Characteristic__c.Value_Reference__r.NAME LIKE '%Black%'
OR Product_Characteristic__c.Value_Reference__r.NAME LIKE '%Solid%'
GROUP BY Product_Characteristic__c.Product_Profile__c

SELECT Id,Name FROM Product_Profile__c 
    WHERE Id IN (SELECT Product_Profile__c 
                   FROM Product_Characteristic__c 
                   WHERE Product_Characteristic__c.Value_Reference__r.Name like '%Black%'  
                      OR Product_Characteristic__c.Value_Reference__r.Name like '%Solid%')

I would like to use AND instead of OR but it fetches no results. And I know that there is a product which has both Black and Solid as its characteristics value (product1 - see image above)

  • Please include some of the SOQL you have tried. – Adrian Larson Aug 25 '15 at 5:37
  • (1) In your first SOQL, if there are n where clauses, then those groups of product_profile__c of size n are the ones that match the desired result. (2) You can also omit all the Product_characteristic__c. from the where clause and group by clause. (3) Why the Like instead of = unless asking for %solid% should find semi-solid (PC3) as well – cropredy Aug 26 '15 at 6:20

You need to first fetch Ids of the Characteristics for Color and Material type. Then you need to fetch ids of the Characteristic values for Black and Solid. Then query the Product characteristic which has these Ids.

  • If you look at my sample queries, I was able to get the product profile using exactly the same method you have described here. But my problem is getting inner join working with this SOQL query. – SK25 Aug 26 '15 at 5:13

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.