Kentico - Repeater With Custom Query Issue

Question

I have this query below in a custom query repeater to display all articles that have the same value under PracticeAreas as the current one. PracticeAreas has multiple checkboxes - and this is the problem.

SELECT ArticleTitle, NodeAliasPath FROM my_articles 
JOIN view_cms_tree_joined
ON ArticlesID = DocumentForeignKeyValue
where classname = 'my.articles' and 
##WHERE##

and have this in the WHERE condition:

PracticeAreas = '{%CurrentDocument.PracticeAreas#%}' and NodeID != {%CurrentDocument.NodeID#%}

Example: Article#1 has 1 and 2 checked under PracticeAreas; Article#2 has 1 checked; Article#3 has 1 and 2 checked.

Result: Only Article#1 and Article#3 are considered related from my code. But what I really wanted is all 3 articles above to be related because they all have one same PracticeArea which is checkbox 1. Is this possible? Hope it's clear. Thanks!

Peter Mogilnitski Peter Mogilnitski · Accepted Answer · 2016-10-29T00:08:03

Well, To start off you can use the view (made by Kentico) for your class, in your case it will probably be view_my_atricles_joined. it has all the column you need. I am not quite sure what PracticeAreas contains as a string. If I assume this a list of some IDs (CSV) for ex.: CSV = 'ID1,ID2,ID3,...ID100'

let say you article has 'ID1,ID3' then your 'WHERE' should look like : WHERE PATINDEX(',ID1,', ',' + CSV + ',') > 0 OR PATINDEX(',ID3,', ',' + CSV + ',') > 0

Kentico - Repeater With Custom Query Issue

1 Answers