Get Documents where child document contains value

Hey,

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.

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")
.OnSite("CorporateSite")
.Culture("en-CA")
.ToList();

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)
    .AsSingleColumn("NodeParentID");

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

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.

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.

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);