Portal Engine Questions on portal engine and web parts.
Version 7.x > Portal Engine > Is there something like NodeChildNodesCount that only counts published nodes? View modes: 
User avatar
Member
Member
matt-awg - 1/23/2014 3:21:04 PM
   
Is there something like NodeChildNodesCount that only counts published nodes?
I am trying to hide things in a menu because they have no children that are published. NodeChildNodesCount does not seem to work - I am assuming it is counting non-published nodes. I have "Select only published" checked on my menu web part. Using NodeChildNodesCount > 0 in the where clause is working to hide cases where there are really no child nodes at all but not where there are some that are all not published. Any ideas?
Thanks!

User avatar
Kentico MVP
Kentico MVP
Brenden Kehren - 1/24/2014 10:20:59 AM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
Maybe switch your WHERE clause up a bit and leave the Select only published checked and add "NodeChildNodesCount > 0".

The more I think about this, I believe this SELECT statment will work just fine
SELECT m1.NodeChildNodesCount, m1.*
FROM View_CONTENT_MenuItem_Joined m1
WHERE m1.NodeChildNodesCount > 0
AND (SELECT COUNT(*) FROM View_CONTENT_MenuItem_Joined WHERE Published = 1 AND NodeParentID = m1.NodeID) > 0
AND NodeID = 5
I just used NodeID 5 because I tested a document that had children that were not published

User avatar
Member
Member
matt-awg - 1/24/2014 10:30:53 AM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?

Maybe switch your WHERE clause up a bit and leave the Select only published checked and add "NodeChildNodesCount > 0".

Thanks FroggEye but this is exactly what I said I already have set up - I had checked "select only published" and then my where clause said "NodeChildNodesCount > 0". It does not seem to check whether or not child nodes are published.

As for your select... this is not a repeater where I can specify the SQL - it is a Tree Menu web part. I feel like you are about to tell me to clone the web part and customize it.... I am really getting tired of doing that! It seems like nothing actually works out of the box with Kentico and I end up customizing everything. Then I can't apply hotfixes (well I can but they won't update all the web parts I had to clone and customize if any bugs were fixed in the originals). Very frustrating.

Thanks again though.

User avatar
Kentico MVP
Kentico MVP
Brenden Kehren - 1/25/2014 2:33:53 PM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
You don't need to specify the full SELECT statement, just the WHERE clause. I only posted the full statement so you could run it and test the results yourself.

Most everything works out of the box, just need to know how to use the tools together. Comes with experience I guess. Not 100% sure how long you've worked with Kentico but I continue to learn new things every day. For instance, I wasn't aware I could specify the CssClass on a webpart, I was always wrapping it in another < div > tag, learned that today. Simple but saves additional code.

User avatar
Member
Member
matt-awg - 1/27/2014 11:34:14 AM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
Well... that idea sort of worked... I can't specify the full select so I have no idea what tables it is using. I had to force a sql error with a bogus where clause and then capture the full sql statement in the error in the event log and then I added a custom where clause like you suggested:
NodeChildNodesCount > 0 AND (SELECT COUNT(1) FROM [dbo].[View_CMS_Tree_Joined] X WHERE X.Published = 1 AND X.NodeParentID = View_CONTENT_MenuItem_Joined.NodeID) > 0

There problem is this works on the front end of the site but this actually throws an error in the CMS desk!!!! It is using a different query in the CMSDesk then it is on the front end!!! If I change the where clause to this then it works in design mode on the CMSDesk but the front end throws an error:
NodeChildNodesCount > 0 AND (SELECT COUNT(1) FROM [dbo].[View_CMS_Tree_Joined] X WHERE X.Published = 1 AND X.NodeParentID = View_CMS_Tree_Joined_Versions.NodeID) > 0

Any idea why it uses two different queries for the Tree Menu web part uses two different sql statements depending on where it executes? So I assume I can't fix this ... I have to just tell my client "sorry, just please ignore that error message about "An error occurred processing the controls. The page is now in safe mode..." that appears in CMSDesk.

User avatar
Kentico MVP
Kentico MVP
Brenden Kehren - 1/27/2014 2:46:32 PM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
Matt,
Send me an email with the full query. I never tested my theory fully prior to sending it to you. I'm pretty sure it will work, just something simple missing...
Brenden

User avatar
Member
Member
matt-awg - 1/28/2014 11:59:27 AM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
Thanks Brenden, I sent you an email with some more details...

Anyone that works for Kentico, can you tell me if the tree menu web part uses different SQL when viewed in CMSDesk versus on the live site front end?

Thanks,
Matt

User avatar
Kentico Consulting
Kentico Consulting
Kentico_RichardS - 1/29/2014 1:39:06 AM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
Hi Matt,

Thank you for your message.

Are you referring to CSS list menu web part? If so then no, the query and the whole webpart behaviour should be same in design tab as well as on the live site.

Kind regards,
Richard Sustek

User avatar
Kentico MVP
Kentico MVP
Brenden Kehren - 1/29/2014 6:20:33 AM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
Kentico_RichardS wrote: Are you referring to CSS list menu web part? If so then no, the query and the whole webpart behaviour should be same in design tab as well as on the live site.
Richard he was using the Tree Menu webpart. I was able to replicate the same issue. It would work as expected in either the CMSDesk or the Live Site, never both.

In the Live Site it would use
... FROM View_CONTENT_MenuItem_Joined
and in the CMSDesk the query would use
... FROM View_CMS_Tree_Joined_Versions
Which makes it difficult to create a complex query without writing or modifying any code. I can understand Matt's frustration in having to clone a webpart or write a custom one but for me its second nature and simple to do. For others not so much.

User avatar
Member
Member
matt-awg - 1/29/2014 11:01:32 AM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
Hi Richard,

As FroggEye said, I was using a Tree Menu web part. He and I discussed somethings via email and he suggested trying the hierarchical viewer. So I just wasted a ton of time figuring out how to set up a hierarchical viewer that would match the output of the Tree Menu web part I was using and finally got it working only to find the SAME ERROR!!!!!!!!!!!

So then I though why not add a macro to my where clause to change the view I am joining to but I can't get that to work either. I tried this macro in my where clause but it seems to always to still use View_CONTENT_MenuItem_Joined even in the CMSDesk. I am assuming CurrentURL is not doing what I think it is doing because of the frames maybe?
(SELECT COUNT(1) FROM [dbo].[View_CMS_Tree_Joined] X WHERE X.Published = 1 AND X.NodeParentID = {%(Contains(ToLower(CurrentURL), "cmsdesk")) ? "View_CMS_Tree_Joined_Versions" : "View_CONTENT_MenuItem_Joined"%}.NodeID) > 0 AND (NodeLevel = 2 or (NodeLevel = 3 AND (NodeParentID={%CurrentDocument.Parent.NodeID%})))

and that does not work either. I have now wasted about 8 hours trying to do what should have been a simple check box on the tree menu webpart to only display items that have children that are not disabled. Any ideas how to fix this?

Thanks,
Matt

User avatar
Kentico Consulting
Kentico Consulting
Kentico_RichardS - 1/30/2014 2:13:00 AM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
Hi,

I understand your frustatrion and we are sorry you are experiencing these issues. However its impossible to offer all possible customization requests which the users all over the world would want.

I actually came up with an idea - workaround which you could use in this case.

What you can do is to set the where condition only for the live site and not for the pages inside CMS Desk. It would display correctly on the live site, unfortunately not in cms desk, but I think that this could be suitable.

Please try to edit the Tree webpart in CMSWebpart/Navigation/cmstreemenu.ascx.cs file in SetupControl(). You will use where condition only when you are viewing this webpart on the live site. You can do it with the following code:
string viewMode;
if (Request.QueryString["viewmode"] != null)
{
viewMode = Request.QueryString["viewmode"].ToString();
}
else
{
viewMode = "1";
}

if (viewMode == "3" || viewMode == "6")
{
// you are inside CMS
}
else
{
// this will happen only on live site
}

I hope this will help.

Kind regards,
Richard Sustek

User avatar
Member
Member
matt-awg - 1/30/2014 7:45:55 AM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
First off, Thanks Froggeye and Richard for your ideas... I sort of combined several of them and came up with a solution that did not require customizing a web part. For anyone that is trying to do something similar, here is the solution. TO summarize the problem:

I have an ecommerce site with a tree menu web part that was displaying some sub categories that were not empty but all products in there were not published so the sub category was showing in the menu even though when you clicked it, no products showed up.

So Froggeye's idea of adding a sub query to the where clause worked, I just had to join to the View_CMS_Tree_Joined instead of the View_CONTENT_MenuItem_Joined view because I was concerned about products in the sub category, not pages/menu items. This worked but then in the CMSDesk it was throwing an error because it was joining to a different view (View_CMS_Tree_Joined_Versions) then it was on the live site (View_CONTENT_MenuItem_Joined). So looking at Richard's idea of customizing the web part to check a querystring value, I realized instead, I could just use a query string macro, and the following where clause seems to have finally worked. Oh and I had already switched to a hierarchical viewer so technically I only tested this with that web part, I had abandoned the tree menu web part at Froggeye's suggestion but I think this solution would work there as well, you might not need the additional nodelevel part of the query with the tree menu web part, I think it handles that for you. My category structure never goes beyond NodeLevel 3 and I only wanted the sub categories to display if the user selected one of the sub categories under the same top level category or if they selected that level itself. (Just clarifying for someone that finds this thread in the future so they know what I was trying to do).

The final where clause:
(SELECT COUNT(1) FROM [dbo].[View_CMS_Tree_Joined] X WHERE X.Published = 1 AND X.NodeParentID = {% z = QueryString.GetValue("viewmode").ToInt(1); if (z==3 || z==6) {"View_CMS_Tree_Joined_Versions"} else {"View_CONTENT_MenuItem_Joined"} %})))

User avatar
Kentico Consulting
Kentico Consulting
Kentico_RichardS - 1/30/2014 7:51:40 AM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
Hi,

Thank you very much for the detailed explanation. Im glad that you were able to finally achieve what you were looking for.

Kind regards,
Richard Sustek

User avatar
Kentico MVP
Kentico MVP
Brenden Kehren - 1/31/2014 11:28:48 PM
   
RE:Is there something like NodeChildNodesCount that only counts published nodes?
Nice job Matt on not customizing the webpart! Great use of macros!

Brenden