0

I have an object PurchaseOrder that has a List<VendorStatus> VendorStatuses, VendorStatus has an EstimatedShipDate column. I need to filter a purchase order by the latest EstimatedShipDate. How can I do this?

I've tried:

Filter<PurchaseOrder>.Range(r => r
    .OnField(x => x.VendorStatuses.OrderByDescending(v => v.StatusUpdateDate).First().EstimatedShipDate)
    .GreaterOrEquals(fromDate)
    .LowerOrEquals(toDate));

and

var f = Filter<PurchaseOrder>.Range(r => r
    .OnField("VendorStatuses.EstimatedShipDate")
    .GreaterOrEquals(fromDate)
    .LowerOrEquals(toDate));

and

var newFilter = Filter<PurchaseOrder>.Nested(nfd => nfd.Path(x => x.VendorStatuses.First().EstimatedShipDate))
    .Filter(f2 => f2.Range(x => x.OnField(f => f.VendorStatuses.First().EstimatedShipDate)
    .GreaterOrEquals(fromDate)
    .LowerOrEquals(toDate))));

But none of these worked. How can I do this?

I'm not even able to get the date range working when running against elastic directly. The following query returns no results even though when using the head plugin i can see them listed under "po"

{
    "query": {
        "range": {
            "po.vendorStatuses.estimatedShipDate": {
                "gte": "2016-10-01",
                "lte": "2016-11-01",
            }
        }
    }
}

We're using Nest version 1.7.2 in our project with elastic 1.4.5.

Here's some more info on our types:

//that builds our search query, contains many methods to build up the FilterContainer
public class PurchaseOrderSearchQueryBuilder 
{
    protected FilterContainer Filter { get; set; }

    public PurchaseOrderSearchQueryBuilder WithExpectedShipDate(DateTime fromDate, DateTime toDate)
    {
        var newFilter = Filter<PurchaseOrder>. 
        //method i need to implment

        Filter &= newFilter;

        return this;
    }
}

//example working filter method
public PurchaseOrderSearchQueryBuilder WithCustomerId(long customerId)
{
    if (customerId > 0)
        Filter &= Filter && Filter<PurchaseOrder>.Term(p => p.CustomerId, customerId);
    return this;
}

public class PurchaseOrder 
{
    ...
    public long CustomerId { get; set; }
    public List<PoVendorStatus> VendorStatuses { get; set; }
}

public class PoVendorStatus
{
    public long Id { get; set; } 
    ...
    public DateTime? EstimatedShipDate { get; set; }
    public DateTime StatusUpdateDate { get; set; }
}
5
  • Are you using ElasticSearch.Net or Nest? I cannot find any static class Filter<> in both Namespaces. Commented Oct 28, 2016 at 20:56
  • What version of NEST are you using? And What version of Elasticsearch are you running against? Commented Oct 29, 2016 at 22:54
  • @RussCam we're using Nest version 1.7.2 Commented Oct 31, 2016 at 13:14
  • @Dleh and version of Elasticsearch you are targeting? Also, latest PurchaseOrder by EstimatedShipDate out of all PurchaseOrders? Is List<VendorStatus mapped as a nested type? Can you show the mapping in Elasticsearch for PurchaseOrder? Commented Oct 31, 2016 at 21:27
  • We have a much more complex filter that is being created, this is just one aspect of it. We are limited to Elastic 1.4.5. I'll provide some more info on my question Commented Nov 1, 2016 at 13:10

1 Answer 1

2

Using nest this will return all the parent object that have one inner object that has met the condition. If you want to query nested object as well the need to be mapped as nested and you need to use nested query.

//Fluent
client.Search<PurchaseOrder>(s=>s.Query(
                    q=>q.DateRange(
                        dr=>dr.Field(p=>p.VendorStatuses.First().StatusUpdateDate)
                              .GreaterThan(fromDate)
                              .LessThan(toDate))));
//Object
client.Search<PurchaseOrder>(new SearchRequest<PurchaseOrder>()
                {
                    Query =new DateRangeQuery
                    {
                        //If you used all default mappings this might be camelCase
                        Field = "VendorStatuses.StatusUpdateDate",
                        GreaterThan = fromDate,
                        LessThan = toDate
                    }
                });
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.