Range query over dates period

Elastic Stack Elasticsearch
1

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 elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

2

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 elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

3

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": {}
}
}
}
}

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 elasticsearch+unsubscribe@.
For more options, visit https://groups.google.com/groups/opt_out.

4

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 elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

5

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 :slight_smile:
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 elasticsearch+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.