Range query over dates period

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Range query over dates period

avairet
Hi,

I need to exclude some documents of my resultset considering dates
period.
My search engine is for apartments rental, so I would like to exclude
from resultset, the apartments that are allready booked for the
considered period.

My example:

apartments data in my index:

apart_id: 1
name: Chinois
sleepings: 5
rooms: 2
city_id: 2
district: Opéra
facilities: [
    Climatisation
    Internet
    Parking
]
elevator: 0
is_available_to_rent: 1
periods: [

    {
        start_date: 2013-09-04
        end_date: 2013-09-06
        apartment_id: 1
    }
    {
        start_date: 2013-09-07
        end_date: 2013-09-10
        apartment_id: 1
    }
]

apart_id: 2
name: Anglais
sleepings: 3
rooms: 2
city_id: 2
district: Opéra
facilities: [
    Climatisation
    Internet
    Parking
]
elevator: 0
is_available_to_rent: 1
periods: [

    {
        start_date: 2013-05-13
        end_date: 2013-05-17
        apartment_id: 1
    }
    {
        start_date: 2013-09-07
        end_date: 2013-09-10
        apartment_id: 1
    }
]

I would like to get only the first apartment in my resultset (e.g.
exclude the first period of the second apartment)

For example, if I search apartment that is available for the period
between 2013-05-10 and 2013-05-15, the second should not match because
it has a period that is overlaped by the search period.

I try bool query with a range query (or range filter), but it does not
work...

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/groups/opt_out.


Reply | Threaded
Open this post in threaded view
|

Re: Range query over dates period

phill
I think you need to use nested objects, not an array of objects, but you
don't show your mapping, so maybe I don't completely understand your issue.
To enable nested objects, you will have to use a mapping in your index
and not just rely on default mapping.
-Paul

On 5/6/2013 9:27 AM, avairet wrote:

> Hi,
>
> I need to exclude some documents of my resultset considering dates
> period.
> [...]
> apart_id: 2
> name: Anglais
> sleepings: 3
> rooms: 2
> city_id: 2
> district: Opéra
> facilities: [
>      Climatisation
>      Internet
>      Parking
> ]
> elevator: 0
> is_available_to_rent: 1
> periods: [
>
>      {
>          start_date: 2013-05-13
>          end_date: 2013-05-17
>          apartment_id: 1
>      }
>      {
>          start_date: 2013-09-07
>          end_date: 2013-09-10
>          apartment_id: 1
>      }
> ]

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/groups/opt_out.


Reply | Threaded
Open this post in threaded view
|

Re: Range query over dates period

sslhj
This post has NOT been accepted by the mailing list yet.
In reply to this post by avairet
hi,
  you can use bool query with must of term qeurys, such as,
   
  {
  "query": {
    "filtered": {
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "deptPath": "xxx"
              }
            },
            {
              "term": {
                "deptPath": "yyy"
              }
            }
          ]
        }
      },
      "filter": {
        "match_all": {}
      }
    }
  }
}

avairet wrote
Hi,

I need to exclude some documents of my resultset considering dates
period.
My search engine is for apartments rental, so I would like to exclude
from resultset, the apartments that are allready booked for the
considered period.

My example:

apartments data in my index:

apart_id: 1
name: Chinois
sleepings: 5
rooms: 2
city_id: 2
district: Opéra
facilities: [
    Climatisation
    Internet
    Parking
]
elevator: 0
is_available_to_rent: 1
periods: [

    {
        start_date: 2013-09-04
        end_date: 2013-09-06
        apartment_id: 1
    }
    {
        start_date: 2013-09-07
        end_date: 2013-09-10
        apartment_id: 1
    }
]

apart_id: 2
name: Anglais
sleepings: 3
rooms: 2
city_id: 2
district: Opéra
facilities: [
    Climatisation
    Internet
    Parking
]
elevator: 0
is_available_to_rent: 1
periods: [

    {
        start_date: 2013-05-13
        end_date: 2013-05-17
        apartment_id: 1
    }
    {
        start_date: 2013-09-07
        end_date: 2013-09-10
        apartment_id: 1
    }
]

I would like to get only the first apartment in my resultset (e.g.
exclude the first period of the second apartment)

For example, if I search apartment that is available for the period
between 2013-05-10 and 2013-05-15, the second should not match because
it has a period that is overlaped by the search period.

I try bool query with a range query (or range filter), but it does not
work...

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/groups/opt_out.
Reply | Threaded
Open this post in threaded view
|

Re: Range query over dates period

avairet
In reply to this post by phill
Thanks for answer Paul!

Yes I think nested object is the good way...
So I have mapped my periods as nested objects, but now, I don't know how to query (or filter) my documents to exclude those who are not available for the considered period...

There is a test I have made but without success:
{
    "nested" : {
        "path" : "periods",
        "score_mode" : "total",
        "query" : {
            "bool" : {
                "must_not" : [
                    {
                        "range" : {"periods.start_date" : {"to" : "2013-05-10"}}
                    },
{
"range" : {"periods.end_date" : {"to" : "2013-05-17"}}}
                ]
            }
        }
    }
}




Le lundi 6 mai 2013 19:10:48 UTC+2, P Hill a écrit :
I think you need to use nested objects, not an array of objects, but you
don't show your mapping, so maybe I don't completely understand your issue.
To enable nested objects, you will have to use a mapping in your index
and not just rely on default mapping.
-Paul

On 5/6/2013 9:27 AM, avairet wrote:

> Hi,
>
> I need to exclude some documents of my resultset considering dates
> period.
> [...]
> apart_id: 2
> name: Anglais
> sleepings: 3
> rooms: 2
> city_id: 2
> district: Op�ra
> facilities: [
>      Climatisation
>      Internet
>      Parking
> ]
> elevator: 0
> is_available_to_rent: 1
> periods: [
>
>      {
>          start_date: 2013-05-13
>          end_date: 2013-05-17
>          apartment_id: 1
>      }
>      {
>          start_date: 2013-09-07
>          end_date: 2013-09-10
>          apartment_id: 1
>      }
> ]

--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/groups/opt_out.
 
 
Reply | Threaded
Open this post in threaded view
|

Re: Range query over dates period

phill
You are saying to exclude any entry that has a available start before your start date, this would exclude, for example an entry that started 1900-01-01 :-)
You have a similar problem with the end date. Must_not means any of expressions (boolean OR) excludes the entry (the rental).
So If I listed that my place was available 2000-01-01 to 2100-01-01 I would always be excluded! Hmm, something is wrong.  I am assuming the periods are available to rent periods.

What you want is a statement that says, if there is an entry has an _available_  period that has a start date on or before the interval of interest (from 2013-05-10 to 2013-05-17 in the example you're using) AND an end date on or after the end date.

Does this mean you have it exactly backwards and the query should be "must" instead of "must_not"?   I can't count the number of times I've worked out logic exactly opposite of what I wanted.  I would give that a try.

-Paul
On 5/7/2013 12:59 AM, avairet wrote:
"must_not" : [
{
   "range" : {"periods.start_date" : {"to" : "2013-05-10"}}
},
{
"range" : {"periods.end_date" : {"to" : "2013-05-17"}}}
]


--
You received this message because you are subscribed to the Google Groups "elasticsearch" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/groups/opt_out.