Repeater with Custom Query, Trouble with WHERE condition

Michael Legacy asked on December 19, 2019 21:34

Hey all,

Hoping this is a pure syntax thing, and someone can help me out.

I have client who's site was originally using Page relationships to relate ingredients to products. Recently, they have a legal requirement to be able to order those ingredients. Because of that, we are redesigning the architecture of how this works, using a Repeater with a Custom Query, and a Custom Table that stores the URLs of both the Product and Ingredient pages as well as an orderable integer instead. I have a custom query on the ingredient page type that looks like this:

SELECT I.*
FROM View_CMS_Tree_Joined AS V
INNER JOIN dbo.jelmar_ingredient_product_relationship AS T
ON '/' + V.DocumentCulture + V.NodeAliasPath = T.IngredientDocument
LEFT JOIN dbo.jelmar_ingredients AS I
ON I.IngredientsID = V.DocumentForeignKeyValue
WHERE T.ProductDocument = ##WHERE##
ORDER BY T.IngredientOrder

I have tried setting my WHERE CONDITION in the Repeater with Custom Query web part to a number of things, all resulting in an error. Is my problem that I can't include ##WHERE## like this? If not, is it possible to write this in the WHERE condition on the web part?:

T.ProductDocument = '{%CurrentDocument.AbsoluteURL|(identity)GlobalAdministrator%}'

I mostly have experience with MVC, so the smaller syntax stuff like this on Portal engine kind of trips me up. Any help would be massively appreciated!

Correct Answer

Michael Legacy answered on December 19, 2019 22:20

Thanks Zach!

That got me on the right track. I am using the following and it's now working:

SELECT I.*
FROM View_CMS_Tree_Joined AS V
INNER JOIN dbo.jelmar_ingredient_product_relationship AS T
ON '/' + V.DocumentCulture + V.NodeAliasPath = T.IngredientDocument
LEFT JOIN dbo.jelmar_ingredients AS I
ON I.IngredientsID = V.DocumentForeignKeyValue
WHERE ##WHERE##
ORDER BY T.IngredientOrder

Then on the web part, I'm using this for a where condition:

T.ProductDocument = '/' + '{%CurrentDocument.DocumentCulture + CurrentDocument.NodeAliasPath|(handlesqlinjection)false #%}'

1 votesVote for this answer Unmark Correct answer

Recent Answers

Zach Perry answered on December 19, 2019 22:02 (last edited on December 19, 2019 22:04)

Might want to check the query that is getting ran, from the way it looks to me, this is the query that will get ran based off your settings:

SELECT I.*
FROM View_CMS_Tree_Joined AS V
INNER JOIN dbo.jelmar_ingredient_product_relationship AS T
ON '/' + V.DocumentCulture + V.NodeAliasPath = T.IngredientDocument
LEFT JOIN dbo.jelmar_ingredients AS I
ON I.IngredientsID = V.DocumentForeignKeyValue
WHERE T.ProductDocument = T.ProductDocument = '{%CurrentDocument.AbsoluteURL|(identity)GlobalAdministrator%}'
ORDER BY T.IngredientOrder

I think you need to write your custom query like this:

SELECT I.*
FROM View_CMS_Tree_Joined AS V
INNER JOIN dbo.jelmar_ingredient_product_relationship AS T
ON '/' + V.DocumentCulture + V.NodeAliasPath = T.IngredientDocument
LEFT JOIN dbo.jelmar_ingredients AS I
ON I.IngredientsID = V.DocumentForeignKeyValue
WHERE T.ProductDocument = ##WHERE##
ORDER BY T.IngredientOrder

and have this in your where condition on the webpart: '{%CurrentDocument.AbsoluteURL|(identity)GlobalAdministrator%}'

Whatever you have in the Where field on the webpart will repalce ##WHERE##. Sometimes for macros to evaluate you have to click the caret next to the field then enter the macro.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on December 27, 2019 11:01

Yes, the things is that the ##WHERE## macro is replacing the entire condition - so everything you would write after "WHERE" in your SQL statement.

0 votesVote for this answer Mark as a Correct answer