DocumentQuery to join parent name

Stefan Lorenz asked on July 18, 2014 11:11

I want to retrieve certain documents together with their parent documents name. In SQL, this will be

SELECT B.DocumentName as BoardName,N.DocumentName FROM View_Kplus_News_Joined N
INNER JOIN View_Kplus_NewsBoard_Joined B ON (B.NodeID=N.NodeParentID)

I'd like to use DocumentQuery for several reasons (mainly because I don't know the SQL equivalent to CheckPermissions()). Without the joined column, the code will look like

var data=new TreeProvider().SelectNodes("Kplus.News")
                           .OnSite(SiteContext.CurrentSiteName)
                           .CheckPermissions(true)
                           .Columns("DocumentName");

but I found now way to include the parent column in the above. I experimented with .Source<>(), but that results in errors ("Missing ##SOURCE## macro in the query text...").

If that isn't possible with the current DocumentQuery, a hint how to reproduce CheckPermissions() in SQL would be very helpful.

Best regards,

Stefan

Recent Answers

Brenden Kehren answered on July 18, 2014 13:53

Hey Stefan, have you checked out the Article on DocumentQuery? About half way down, it talks about CheckPermissions(). Here is the code snippet Jaroslav provides:

var user = UserInfoProvider.GetUserInfo("public");
using (new CMSActionContext(user))
{
    var news = DocumentHelper.GetDocuments("CMS.News")
                              .OnSite("CorporateSite")
                              .Path("/News", PathTypeEnum.Children)
                              .CheckPermissions();
}

I think what you are looking for or are missing is the .Path(string, PathTypeEnum) property.

0 votesVote for this answer Mark as a Correct answer

Stefan Lorenz answered on July 18, 2014 14:12

I've read those articles, and .Path() isn't my problem. I need a dataset containing nodes and their parent node's name in the end, so I'll have to use a join in some way (good) or use parts of DocumentNamePath (not a clean solution).

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on July 18, 2014 18:04

If you iterate through the data or in my example news items, you will have access to the parent info simply by using:

treeNode.Parent.GetValue("ColumnName");
treeNode.Parent.DocumentName;
treeNode.Parent.NodeID;

There should be no need to join back to itself. If you wanted you can even check the level before going to the parent to ensure there is in fact a parent.

0 votesVote for this answer Mark as a Correct answer

Stefan Lorenz answered on July 21, 2014 08:49

Sure, that works, but it isn't viable performance-wise. I should have been more specific in my initial post, I want to sort the result dataset by the parent's name. So retrieving all entries, sort them by code and then display only a single page of them isn't a good idea. Doing it in the DB is the only performant solution - and that involves a join.

The DocumentQuery API should be able to create those joins, it's just that I'm stuck with the syntax.

Thanks for your help,

Stefan

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on July 22, 2014 20:22

Fair enough, check out this article then about the JOIN syntax.

0 votesVote for this answer Mark as a Correct answer

Stefan Lorenz answered on July 23, 2014 08:43

That was the first I did and as I wrote in my initial post, this raises erros as soon as I use it.

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.