I'm trying to figure out the most efficient way to build a query. I have a "Category" object and in that category object there is a "Parent_Category__c" field. The category is for a Product (and there is a "Product_Category__c" junction object that links categories to products.) A product can have multiple levels of parent categories.
Product -> Product_Category__c -> Category <--|
|_______|
The way the system (which I inherited) is built is causing me to start from bottom-up. So I have to find the product first, then find the product_category__c, and then find the all the categories__c. So I get the lowest level child category. What is an efficient way to query all the way up to the root category -- keeping in mind I will have to do this a few thousand times for each product.
I had thought about querying all categories and storing them in a map for reference but I don't know how many categories this client will have. It could get large.
Thanks for the help.
Decided to go with recursive route...