Get Documents where child document contains value

Joseph Pickering asked on March 31, 2020 18:13

I'm wondering how to get documents of a certain type where one of their child nodes contains a value.

Example tree:

Folder (CMS.Folder) 
└───Accommodation 1 (custom.Accommodation)
│   │   Address (custom.Address)
└───Accommodation 2 (custom.Accommodation)
│   │   Address (custom.Address)
└───Accommodation 3 (custom.Accommodation)
│   │   Address (custom.Address)

Using the example tree above, I'd like to get all of the accommodation where the child address contains a certain value for it's 'Town' field. Then I'd like to be able to select the accommodation's ID and Name, as well as it's child Address' 'Longitude' and 'Latitude' values.

I've tried a number of different MultiDocumentQuerys, but I can't figure it out.

This is the closest I've got, but this gets all accommodation and the accommodation and address data rows are separate.

var documents = DocumentHelper.GetDocuments()
                              .Type("vn.Address", q => q.Columns("Town", "Longitude", "Latitude").WhereEquals("Town", id))
                              .Type("vn.Accommodation", q => q.Columns("ID", "Name"))

Correct Answer

Peter Mogilnitski answered on April 2, 2020 14:44

The problem is that you need to get columns from both: address and accommodation.

So I've taken some time to examen possible solutions here. My approach would be to get the subtree and query it using LINQ.

Here is what you need to to do.

Get the whole sub-tree underneath your folder. Please note we get only columns we need and specific types.

var listResult = DocumentHelper.GetDocuments()
    .Path("/FolderPath/ ", PathTypeEnum.Children)
    .Type("custom.Accommodation", q => q.Columns("ID", "Name")) // type specific
    .Type("custom.Address", q => q.Columns("Town", "Longitude", "Latitude")) // type specific
    .Columns("ClassName", "NodeId", "NodeParentID", "DocumentId") // shared for all types
    .OnSite("your site")

Cache it. So every time you search for a new town you will not query DB

Use LINQ to get what need

var obj = listResult.Where(x => x.ClassName == "custom.Address" && x.GetValue("Town") == "London")
    .Select(address => { 
        var accomodation = listResult.Where(p => p.ClassName == "custom.Accommodation" && p.NodeID == address.NodeParentID).FirstOrDefault();
        return new
            ID = accomodation.GetValue("ID"),
            Name = accomodation.GetValue("Name"),
            Town = address.GetValue("Town"),
            Longitude = address.GetValue("Longitude"),
            Latitude = address.GetValue("Latitude"),

It is not the final code, but that should give the idea how to approach it.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Arjan van Hugten answered on April 1, 2020 14:27 (last edited on April 1, 2020 14:33)


Are you looking for something like this? I changed the query with some more strongly typed variables of your class using 'nameof' and 'CLASS_NAME'.

var foundAddresses = DocumentHelper.GetDocuments(Address.CLASS_NAME)
    .WhereEquals(nameof(Address.Town), id)
    .Columns(nameof(Address.Town), nameof(Address.Longitude), nameof(Address.Latitude), nameof(TreeNode.NodeParentID));

Then you can get the values with:

foreach(var address in foundAddresses)
    var accommodationName = address.Parent.GetStringValue(nameof(Accommodation.Name), string.Empty);
    var accommodationId = address.Parent.GetStringValue(nameof(Accommodation.Id), string.Empty);
    var addressLongitude = address.GetStringValue(nameof(Address.Longitude), string.Empty);
    var addressLatitude = address.GetStringValue(nameof(Address.Latitude), string.Empty);

Maybe you could do it the way around with the TreeNode children. But here the where is more logical I think.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on April 1, 2020 17:56 (last edited on April 1, 2020 18:25)

Do you have info classes for your types? As far as I know you need info classes to do the join.

It should be something like this:

var mylist = DocumentHelper.GetDocuments("custom.Accommodation").Source(
    sourceItem => sourceItem.Join<customAddressInfo>("custom_Accommodation.NodeId", "NodeParentID")
.Columns("ID", "Name", "Town", "Longitude", "Latitude")
.WhereLike("town", "London")
0 votesVote for this answer Mark as a Correct answer

Dmitry Bastron answered on April 1, 2020 23:27

Hi guys,

Peter, I'm afraid the method you proposed with Join will work only for Module Custom Classes (or Custom tables) and won't work if custom.Address is a content-only page type. I was literally about to post the same query, but when I tried it I realized that joining page types doesn't work. I guess it's because of complication with workflow or something.

Arjan, you proposed method should work, but every single .Parent call will fire the SQL query so that there will be N+1 SQL query. This can be optimized the following way:

var foundAddresses = DocumentHelper.GetDocuments(Address.CLASS_NAME)
    .WhereEquals(nameof(Address.Town), id)

var foundAccommodations = DocumentHelper.GetDocuments(Accommodation.CLASS_NAME)
    .WhereIn("NodeID", foundAddresses)
    // other accommodation conditions if needed

Note that foundAddresses is still of IDataQuery type so it won't be executed. As a result you will have only 1 combined SQL query.

0 votesVote for this answer Mark as a Correct answer

Joseph Pickering answered on April 2, 2020 10:38

Thank you all very much for your answers - they've helped me gain a better understanding of how the DocumentHelper works and what you can do with it.

@Dmitry Bastron Using your proposed solution, how would I be able to select the accommodation's ID and Name, as well as it's child Address' Longitude and Latitude values.

0 votesVote for this answer Mark as a Correct answer

Joseph Pickering answered on April 2, 2020 16:36

Thank you very much for your help.

Using Peter's solution, this is the code that I ended up with:

var results = DocumentHelper.GetDocuments()
                            .Type("custom.Accommodation", q => q.Columns("ID", "Name"))
                            .Type("custom.Address", q => q.Columns("Town", "Longitude", "Latitude"))
                            .Columns("ClassName", "NodeID", "NodeParentID", "DocumentID");
var response = results.AsEnumerable()
                      .Where(x => x.ClassName == "custom.Address" && x.GetValue("Town") == "London")
                      .Select(address =>
                          var accommodation = results.FirstOrDefault(p => p.ClassName == "custom.Accommodation" && p.NodeID == address.NodeParentID);

                          if (accommodation == null)
                              return null;

                          return new
                              ID = accommodation.GetValue("ID"),
                              Name = accommodation.GetValue("Name"),
                              Longitude = address.GetValue("Longitude"),
                              Latitude = address.GetValue("Latitude")
                      .Where(x => x != null);
0 votesVote for this answer Mark as a Correct answer

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