Forums (Obsolete)

Member

hemanthray-gmail - 12/12/2012 4:53:29 PM

Where condition for repeater.

Can we use a switch case for Repeater?

like case querystring value has a variable called value1 use a condition and if the querystring had value2 use another condition?

I am trying to achieve both categories and Tags filter to a blog using a repeater. I am trying to update the where condition accordingly. Please help with any advice

Thanks
A


Kentico Support	kentico_janh - 12/13/2012 2:15:33 AM RE:Where condition for repeater. Hello, You can take advantage of the K# in your where condition, however K# doesn't know the switch-case, so you would need to use as many if-else statements as many cases you have in your requirement. For more information about K#, please follow the link below to our documentation: K# Best regards, Jan Hermann


Member	hemanthray-gmail - 12/13/2012 9:29:09 AM RE:Where condition for repeater. Thanks for the advice, the question is can we have a K# condition inside the where condition of a repeater? If you can send me an example it would be great Thanks

Member

hemanthray-gmail - 12/13/2012 9:52:50 AM

RE:Where condition for repeater.

I am doing this but it doesn't work I am doing anything incorrect here?

{%ToInt(ToInt(ToInt(ToInt( if (?tagid, "? > 0) {ResolveMacros(\"\\r\n    \\r\n       \\\'{?tagid"), ?}\' = 0 AND \'{?tagname?}\'=\'\') \r
      OR \r
      (DocumentID IN (SELECT DocumentID FROM CMS_DocumentTag WHERE TagID = {?tagid), ?} ))\r
      OR \r
      (DocumentID IN (SELECT DocumentID FROM CMS_DocumentTag WHERE TagID IN (SELECT TagID FROM CMS_Tag WHERE TagName = \'{?tagname?}\' AND TagGroupID = {?groupid), "?} ))\\r\n   \")} else { DocumentId <> null  }")%}

Kentico Support

kentico_janh - 12/13/2012 12:11:10 PM

RE:Where condition for repeater.

Hello,

Since you are using Kentico version 6, you can't split macro to more than one part (this functionality is available in Kentico v7), so please update your where condition to something like this:

{%
if (QueryString.GetValue("tagid").ToInt() > 0) {
return "DocumentID IN ...";
}
else {
  if (...) {

  }
}
%}

Or maybe is easier to implement a custom macro and just return final where condition instead.

Best regards,
Jan Hermann


Member	hemanthray-gmail - 12/13/2012 12:58:47 PM RE:Where condition for repeater. Actually we are using kentico 7. But i still get the issue

Kentico Support

kentico_janh - 12/13/2012 2:15:59 PM

RE:Where condition for repeater.

Hello,

If you are using version 7 then your post is in wrong section, however it actually doesn't change the macro much. You can use the same macro as for version 6 (the one I have mentioned) or you can skip those return parts and split the macro to as many parts as many if statements you have:

{%
if (QueryString.GetValue("tagid").ToInt() > 0) {
%}

But as I said, I would recommend you to implement a custom macro to avoid length limitations of the where condition field.

Best regards,
Jan Hermann

Member

hemanthray-gmail - 12/13/2012 2:18:47 PM

RE:Where condition for repeater.

I have used the way you said. But some how when it has a name it adds an extra ' apostrophe which breaks the query. Can you please check this

{%
if (QueryString.GetValue("tagname")!= null ) 
{
return "(DocumentID IN (SELECT DocumentID FROM CMS_DocumentTag WHERE TagID IN (SELECT TagID FROM CMS_Tag WHERE TagName = '" +  QueryString.GetValue("tagname").ToString() + "' AND TagGroupID = 3) ))";
}
 
if (QueryString.GetValue("categoryname")!= null )
{
return "(DocumentID IN ( SELECT DocumentID FROM CMS_DocumentCategory WHERE CategoryID IN (SELECT categoryid FROM CMS_Category WHERE categorydisplayname = '" + QueryString.GetValue("categoryname") + "'))"
}
%}

This is the Query which comes in

SELECT TOP 5 BlogPostTeaser, BlogPostSummary, BlogPostBody, BlogPostTitle, BlogPostDate, 
NodeAliasPath, DocumentURLPath, SiteName, NodeOwner, DocumentTags, DocumentTagGroupID, 
DocumentID, BlogAuthor, NodeXML, DocumentCulture, NodeID, NodeLinkedNodeID, ClassName, NodeLevel,
 NodeOrder, NodeParentID FROM View_CMS_Tree_Joined_Versions INNER JOIN CONTENT_BlogPost
  ON View_CMS_Tree_Joined_Versions.DocumentForeignKeyValue = CONTENT_BlogPost.[BlogPostID] WHERE 
  ((((NodeSiteID = 2) AND (DocumentCulture = N'en-US')) AND (NodeAliasPath LIKE N'/%')) AND 
  (('0' = 0 AND ''='') OR (DocumentID IN (SELECT DocumentID FROM CMS_DocumentTag WHERE TagID = 0 )) OR 
  (DocumentID IN (SELECT DocumentID FROM CMS_DocumentTag WHERE TagID IN (SELECT TagID FROM CMS_Tag WHERE TagName = ''Test'' AND TagGroupID = 0 ))))) AND 
  (ClassName = 'CMS.BlogPost') ORDER BY BlogPostdate DESC


Kentico Support	kentico_janh - 12/14/2012 1:56:15 AM RE:Where condition for repeater. Hello, Have you tried to remove one pair of apostrophes? ... WHERE TagName = " + QueryString.GetValue("tagname").ToString() + " AND ... Best regards, Jan Hermann


Member	hemanthray-gmail - 12/14/2012 4:05:41 AM RE:Where condition for repeater. Yes I did. When I remove the apostrophe it doesn't get anything and the query fails.

Kentico Support

Accepted solution

kentico_janh - 12/14/2012 5:30:31 AM

RE:Where condition for repeater.

Hello,

I have consulted it with our development department and those extra apostrophes are added due to security reasons for each string input. However they give me an example how to solve it by using macro parameters:

{%result="";
if (QueryString.GetValue("tagname")!= null ) 
{
result = "(DocumentID IN (SELECT DocumentID FROM CMS_DocumentTag WHERE TagID IN (SELECT TagID FROM CMS_Tag WHERE TagName = '";
result += SQLEscape(QueryString.GetValue("tagname"));
result += "' AND TagGroupID = 3) ))";
return result;
}
|(handlesqlinjection)false%}

Best regards,
Jan Hermann


Member	hemanthray-gmail - 12/14/2012 8:36:18 AM RE:Where condition for repeater. Great thanks for the help


Kentico Support	kentico_janh - 12/14/2012 8:43:11 AM RE:Where condition for repeater. Hello, You are welcome and please don't forget when using K# together with macro parameters that there has to be no space or a new line between the macro parameter and the closing macro bracket: \|(handlesqlinjection)false#%} Best regards, Jan Hermann


Member	hemanthray-gmail - 12/14/2012 9:27:13 AM RE:Where condition for repeater. Sure thanks for the advice