I have written a custom Query to retrieve child documents (Products) where the Categories obtain particular values driven from a filter. My Query as defined in Kentico is:
SELECT DISTINCT ##COLUMNS## FROM CMS_Category as C
INNER JOIN CMS_DocumentCategory as DC on DC.CategoryID = C.CategoryID
INNER JOIN View_CMS_Tree_Joined as D on DC.DocumentID = D.DocumentID
WHERE C.CategoryEnabled = 'true' AND D.NodeAliasPath LIKE '/%' ##WHERE##
Group By D.NodeAliasPath, D.DocumentName, D.DocumentUrlPath, D.SKUPrice, D.SKUImagePath
An example query that I've used for testing that illustrates the columns I'm using and an example WHERE condition:
SELECT D.DocumentName, D.DocumentUrlPath, D.SKUImagePath, D.SKUPrice, D.NodeAliasPath FROM CMS_Category as C
INNER JOIN CMS_DocumentCategory as DC on DC.CategoryID = C.CategoryID
INNER JOIN View_CMS_Tree_Joined as D on DC.DocumentID = D.DocumentID
WHERE C.CategoryEnabled = 'true' AND D.NodeAliasPath LIKE '/%'
--AND (C.CategoryParentID = '27' AND c.CategoryName IN ('ManufacturerA','ManufacturerB','ManufacturerC')) OR
-- (C.CategoryParentID = '39' AND C.CategoryName IN ('Brown','Orange','Red')) OR
-- (C.CategoryParentID = '43' AND C.CategoryName IN ('Large','Medium','Small')) OR 1 = 1
Group By D.NodeAliasPath, D.DocumentName, D.DocumentUrlPath, D.SKUPrice, D.SKUImagePath
The problem I have, is once I get my TreeNodes back, I seem unable to retrieve values from it. A snippet of code is shown below, where treeNodes is a List<TreeNode>.
TreeProvider treeProvider = new TreeProvider(CMSContext.CurrentUser);
TreeNodeDataSet dataSet = treeProvider.ExecuteQuery("CMS.Product.ProductsByCategory", null, whereCondition, null, -1, columns, 0, 0, ref totalRecords);
if (dataSet != null)
{
treeNodes = dataSet.AsEnumerable<TreeNode>().ToList();
}
CacheHelper.Add(string.Format("Products_{0}", whereCondition.GetHashCode()), treeNodes, null, DateTime.Now.AddMinutes(10), Cache.NoSlidingExpiration);
}
// If we have some data then create some objects that we can return as JSON
if (treeNodes != null && treeNodes.Count > 0)
{
var nodes = treeNodes.Skip(startPoint)
.Take(count)
.Select(node => new
{
NodeAliasPath = node.NodeAliasPath,
ProductTitle = node.DocumentName,
Price = node.GetStringValue("SKUPrice", String.Empty),
ProductImagePath = node.GetStringValue("SKUImagePath", String.Empty),
ProductLogoImagePath = node.GetStringValue("LogoImagePath", String.Empty)
});
// Load actual data
result.Data = new { ProductItems = nodes, AllLoaded = (startPoint + count) >= treeNodes.Count };
}
My problem is that whenever I use node.GetStringValue() or node.GetValue() I always receive a null/my default value instead of the actual value. This is the same for the
LogoImagePath
and the
SKUPrice
even though SKUPrice is a built in field.
What might I be doing wrong?