Searching for reservations/availability with Solr

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Searching for reservations/availability with Solr

Constantijn Visinescu
Hello,

I have a problem i'm trying to solve where i want to check if objects are
reserved or not. (by reservation i mean like making a reservation at a
hotel, because you would like to stay there on certain dates).

I have the following in my schema.xml

<field name="name" type="text" indexed="true" stored="true"/>
<dynamicField name="reserved_from_*" type="date" indexed="true"
stored="true"/>
<dynamicField name="reserved_to_*" type="date" indexed="true"
stored="true"/>

and the follwoing 2 documents in Solr

<doc>
<str name="name">Room1</str>
<date name="reserved_from_11">2000-08-01T00:00:00Z</date>
<date name="reserved_to_11">2000-08-31T23:59:59Z</date>
</doc>
<doc>
<str name="name">Room2</str>
<date name="reserved_from_24">2000-08-01T00:00:00Z</date>
<date name="reserved_to_24">2000-08-13T23:59:59Z</date>
<date name="reserved_from_36">2000-08-20T00:00:00Z</date>
<date name="reserved_to_36">2000-08-22T23:59:59Z</date>
</doc>

Now i want to run a query that gives me all documents(rooms) that are
avaiable from aug 15th to aug 18th (should return Room2), or from aug 10th
to aug 15th (should return none) or from sept 1st to sept 5th (should return
both).

Is it possible to run queries like this in solr? (either with my current
schema setup, or a different one that accomplishes the same idea).

I'm at a loss as to how to formulate a solr query to get the data i want.

Thanks in advance,
Constantijn Visinescu
Reply | Threaded
Open this post in threaded view
|

Re: Searching for reservations/availability with Solr

Avlesh Singh
From what I understood, you need a day level granularity (i.e booked on
15th, 16th and 17th of August) in your indexes. If this is true, then why
even store a "date"? For your use case, I think this should suffice -
<dynamicField name="reserved_dates_*" type="integer" indexed="true"
stored="true" multiValued="true"/>

Each document will have values like these for this particular field -
reserved_dates_August => 15, 16, 19
reserved_dates_September => 1, 3
....

Searches would be for documents (rooms) that don't have certain dates in
their multi-valued fields for the a particular month.
E.g if you wanted to find out rooms available on 15th, 16th and 17th of
August, the query could be:
q=!(+reserved_dates_August:15 +reserved_dates_August:16
+reserved_dates_August:17)

Hope this helps.

Cheers
Avlesh

On Tue, Aug 11, 2009 at 7:08 PM, Constantijn Visinescu
<[hidden email]>wrote:

> Hello,
>
> I have a problem i'm trying to solve where i want to check if objects are
> reserved or not. (by reservation i mean like making a reservation at a
> hotel, because you would like to stay there on certain dates).
>
> I have the following in my schema.xml
>
> <field name="name" type="text" indexed="true" stored="true"/>
> <dynamicField name="reserved_from_*" type="date" indexed="true"
> stored="true"/>
> <dynamicField name="reserved_to_*" type="date" indexed="true"
> stored="true"/>
>
> and the follwoing 2 documents in Solr
>
> <doc>
> <str name="name">Room1</str>
> <date name="reserved_from_11">2000-08-01T00:00:00Z</date>
> <date name="reserved_to_11">2000-08-31T23:59:59Z</date>
> </doc>
> <doc>
> <str name="name">Room2</str>
> <date name="reserved_from_24">2000-08-01T00:00:00Z</date>
> <date name="reserved_to_24">2000-08-13T23:59:59Z</date>
> <date name="reserved_from_36">2000-08-20T00:00:00Z</date>
> <date name="reserved_to_36">2000-08-22T23:59:59Z</date>
> </doc>
>
> Now i want to run a query that gives me all documents(rooms) that are
> avaiable from aug 15th to aug 18th (should return Room2), or from aug 10th
> to aug 15th (should return none) or from sept 1st to sept 5th (should
> return
> both).
>
> Is it possible to run queries like this in solr? (either with my current
> schema setup, or a different one that accomplishes the same idea).
>
> I'm at a loss as to how to formulate a solr query to get the data i want.
>
> Thanks in advance,
> Constantijn Visinescu
>
Reply | Threaded
Open this post in threaded view
|

Re: Searching for reservations/availability with Solr

Shalin Shekhar Mangar
In reply to this post by Constantijn Visinescu
On Tue, Aug 11, 2009 at 7:08 PM, Constantijn Visinescu
<[hidden email]>wrote:

>
> <doc>
> <str name="name">Room1</str>
> <date name="reserved_from_11">2000-08-01T00:00:00Z</date>
> <date name="reserved_to_11">2000-08-31T23:59:59Z</date>
> </doc>
> <doc>
> <str name="name">Room2</str>
> <date name="reserved_from_24">2000-08-01T00:00:00Z</date>
> <date name="reserved_to_24">2000-08-13T23:59:59Z</date>
> <date name="reserved_from_36">2000-08-20T00:00:00Z</date>
> <date name="reserved_to_36">2000-08-22T23:59:59Z</date>
> </doc>
>
> Now i want to run a query that gives me all documents(rooms) that are
> avaiable from aug 15th to aug 18th (should return Room2), or from aug 10th
> to aug 15th (should return none) or from sept 1st to sept 5th (should
> return
> both).
>
> Is it possible to run queries like this in solr? (either with my current
> schema setup, or a different one that accomplishes the same idea).
>

I'm not sure how you are creating the dynamic field's name. But if you know
the field name, the query can be like this:

q=*:*&fq=-reserved_from:[2008-08-15T00:00:00Z TO 2008-08-18T00:00:00Z]

Note the minus at the start of the fq. The above query says give me all
documents (q=*:*) excluding the ones in which reserved_from is between 15th
to 18th August.

--
Regards,
Shalin Shekhar Mangar.
Reply | Threaded
Open this post in threaded view
|

Re: Searching for reservations/availability with Solr

Avlesh Singh
In reply to this post by Avlesh Singh
>
> Searches would be for documents (rooms) that don't have certain dates in
> their multi-valued fields for the a particular month.
> E.g if you wanted to find out rooms available on 15th, 16th and 17th of
> August, the query could be:
> q=!(+reserved_dates_August:15 +reserved_dates_August:16
> +reserved_dates_August:17)
>

I was too fast to suggest the query above. The dates should be individually
negated.

Cheers
Avlesh

On Tue, Aug 11, 2009 at 9:39 PM, Avlesh Singh <[hidden email]> wrote:

> From what I understood, you need a day level granularity (i.e booked on
> 15th, 16th and 17th of August) in your indexes. If this is true, then why
> even store a "date"? For your use case, I think this should suffice -
> <dynamicField name="reserved_dates_*" type="integer" indexed="true"
> stored="true" multiValued="true"/>
>
> Each document will have values like these for this particular field -
> reserved_dates_August => 15, 16, 19
> reserved_dates_September => 1, 3
> ....
>
> Searches would be for documents (rooms) that don't have certain dates in
> their multi-valued fields for the a particular month.
> E.g if you wanted to find out rooms available on 15th, 16th and 17th of
> August, the query could be:
> q=!(+reserved_dates_August:15 +reserved_dates_August:16
> +reserved_dates_August:17)
>
> Hope this helps.
>
> Cheers
> Avlesh
>
>
> On Tue, Aug 11, 2009 at 7:08 PM, Constantijn Visinescu <[hidden email]
> > wrote:
>
>> Hello,
>>
>> I have a problem i'm trying to solve where i want to check if objects are
>> reserved or not. (by reservation i mean like making a reservation at a
>> hotel, because you would like to stay there on certain dates).
>>
>> I have the following in my schema.xml
>>
>> <field name="name" type="text" indexed="true" stored="true"/>
>> <dynamicField name="reserved_from_*" type="date" indexed="true"
>> stored="true"/>
>> <dynamicField name="reserved_to_*" type="date" indexed="true"
>> stored="true"/>
>>
>> and the follwoing 2 documents in Solr
>>
>> <doc>
>> <str name="name">Room1</str>
>> <date name="reserved_from_11">2000-08-01T00:00:00Z</date>
>> <date name="reserved_to_11">2000-08-31T23:59:59Z</date>
>> </doc>
>> <doc>
>> <str name="name">Room2</str>
>> <date name="reserved_from_24">2000-08-01T00:00:00Z</date>
>> <date name="reserved_to_24">2000-08-13T23:59:59Z</date>
>> <date name="reserved_from_36">2000-08-20T00:00:00Z</date>
>> <date name="reserved_to_36">2000-08-22T23:59:59Z</date>
>> </doc>
>>
>> Now i want to run a query that gives me all documents(rooms) that are
>> avaiable from aug 15th to aug 18th (should return Room2), or from aug 10th
>> to aug 15th (should return none) or from sept 1st to sept 5th (should
>> return
>> both).
>>
>> Is it possible to run queries like this in solr? (either with my current
>> schema setup, or a different one that accomplishes the same idea).
>>
>> I'm at a loss as to how to formulate a solr query to get the data i want.
>>
>> Thanks in advance,
>> Constantijn Visinescu
>>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: Searching for reservations/availability with Solr

Constantijn Visinescu
Hi,

This seems like a bit of an unconventional suggestion but it just might
work.

I'd have to add the year because things might end up reserved far ahead, or
they might be reserved again on the same day next year.
I do have 2 questions:

1) Wouldn't it be terribly inefficient when I have 10million+ documents in
my index and i want to check all of em for availabilty over a 2 week period?
That'd mean 14 extra parts in my query. I'm a bit worried that this might
mess up efficiency a lot? (Yes, I have aprox 10 mil documents in my index
;))

2) I populate my index with a data import handler (
http://wiki.apache.org/solr/DataImportHandler ) from a table that has lines
that contain a foreign key to the room number, a reserved_from date and a
reserved_to date. Any way i could get the functionality you're describing
without having to basically write my own data import handler implementation?

Constantijn Visinescu

On Wed, Aug 12, 2009 at 10:49 AM, Avlesh Singh <[hidden email]> wrote:

> >
> > Searches would be for documents (rooms) that don't have certain dates in
> > their multi-valued fields for the a particular month.
> > E.g if you wanted to find out rooms available on 15th, 16th and 17th of
> > August, the query could be:
> > q=!(+reserved_dates_August:15 +reserved_dates_August:16
> > +reserved_dates_August:17)
> >
>
> I was too fast to suggest the query above. The dates should be individually
> negated.
>
> Cheers
> Avlesh
>
> On Tue, Aug 11, 2009 at 9:39 PM, Avlesh Singh <[hidden email]> wrote:
>
> > From what I understood, you need a day level granularity (i.e booked on
> > 15th, 16th and 17th of August) in your indexes. If this is true, then why
> > even store a "date"? For your use case, I think this should suffice -
> > <dynamicField name="reserved_dates_*" type="integer" indexed="true"
> > stored="true" multiValued="true"/>
> >
> > Each document will have values like these for this particular field -
> > reserved_dates_August => 15, 16, 19
> > reserved_dates_September => 1, 3
> > ....
> >
> > Searches would be for documents (rooms) that don't have certain dates in
> > their multi-valued fields for the a particular month.
> > E.g if you wanted to find out rooms available on 15th, 16th and 17th of
> > August, the query could be:
> > q=!(+reserved_dates_August:15 +reserved_dates_August:16
> > +reserved_dates_August:17)
> >
> > Hope this helps.
> >
> > Cheers
> > Avlesh
> >
> >
> > On Tue, Aug 11, 2009 at 7:08 PM, Constantijn Visinescu <
> [hidden email]
> > > wrote:
> >
> >> Hello,
> >>
> >> I have a problem i'm trying to solve where i want to check if objects
> are
> >> reserved or not. (by reservation i mean like making a reservation at a
> >> hotel, because you would like to stay there on certain dates).
> >>
> >> I have the following in my schema.xml
> >>
> >> <field name="name" type="text" indexed="true" stored="true"/>
> >> <dynamicField name="reserved_from_*" type="date" indexed="true"
> >> stored="true"/>
> >> <dynamicField name="reserved_to_*" type="date" indexed="true"
> >> stored="true"/>
> >>
> >> and the follwoing 2 documents in Solr
> >>
> >> <doc>
> >> <str name="name">Room1</str>
> >> <date name="reserved_from_11">2000-08-01T00:00:00Z</date>
> >> <date name="reserved_to_11">2000-08-31T23:59:59Z</date>
> >> </doc>
> >> <doc>
> >> <str name="name">Room2</str>
> >> <date name="reserved_from_24">2000-08-01T00:00:00Z</date>
> >> <date name="reserved_to_24">2000-08-13T23:59:59Z</date>
> >> <date name="reserved_from_36">2000-08-20T00:00:00Z</date>
> >> <date name="reserved_to_36">2000-08-22T23:59:59Z</date>
> >> </doc>
> >>
> >> Now i want to run a query that gives me all documents(rooms) that are
> >> avaiable from aug 15th to aug 18th (should return Room2), or from aug
> 10th
> >> to aug 15th (should return none) or from sept 1st to sept 5th (should
> >> return
> >> both).
> >>
> >> Is it possible to run queries like this in solr? (either with my current
> >> schema setup, or a different one that accomplishes the same idea).
> >>
> >> I'm at a loss as to how to formulate a solr query to get the data i
> want.
> >>
> >> Thanks in advance,
> >> Constantijn Visinescu
> >>
> >
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: Searching for reservations/availability with Solr

Constantijn Visinescu
In reply to this post by Shalin Shekhar Mangar
On Tue, Aug 11, 2009 at 6:13 PM, Shalin Shekhar Mangar <
[hidden email]> wrote:

> On Tue, Aug 11, 2009 at 7:08 PM, Constantijn Visinescu
> <[hidden email]>wrote:
>
> >
> > <doc>
> > <str name="name">Room1</str>
> > <date name="reserved_from_11">2000-08-01T00:00:00Z</date>
> > <date name="reserved_to_11">2000-08-31T23:59:59Z</date>
> > </doc>
> > <doc>
> > <str name="name">Room2</str>
> > <date name="reserved_from_24">2000-08-01T00:00:00Z</date>
> > <date name="reserved_to_24">2000-08-13T23:59:59Z</date>
> > <date name="reserved_from_36">2000-08-20T00:00:00Z</date>
> > <date name="reserved_to_36">2000-08-22T23:59:59Z</date>
> > </doc>
> >
> > Now i want to run a query that gives me all documents(rooms) that are
> > avaiable from aug 15th to aug 18th (should return Room2), or from aug
> 10th
> > to aug 15th (should return none) or from sept 1st to sept 5th (should
> > return
> > both).
> >
> > Is it possible to run queries like this in solr? (either with my current
> > schema setup, or a different one that accomplishes the same idea).
> >
>
> I'm not sure how you are creating the dynamic field's name. But if you know
> the field name, the query can be like this:
>
> q=*:*&fq=-reserved_from:[2008-08-15T00:00:00Z TO 2008-08-18T00:00:00Z]
>
> Note the minus at the start of the fq. The above query says give me all
> documents (q=*:*) excluding the ones in which reserved_from is between 15th
> to 18th August.
>
> --
> Regards,
> Shalin Shekhar Mangar.


It wolud be ideal if i can get this working, but the above query includes
room 1, which is reserved for all of august, in the search results when i
want all the rooms that are avaliable from aug 15th, to aug 18th.

The above query would be ideal if i can get it working, I'm just not sure
how :) i've done some experimenting but i can't seem to get the query quite
right
Reply | Threaded
Open this post in threaded view
|

Re: Searching for reservations/availability with Solr

Avlesh Singh
In reply to this post by Constantijn Visinescu
>
> I'd have to add the year because things might end up reserved far ahead, or
> they might be reserved again on the same day next year.
>
If that is the case, then yes you'll have to take year into account too.

Wouldn't it be terribly inefficient when I have 10million+ documents in my
> index and i want to check all of em for availabilty over a 2 week period?
> That'd mean 14 extra parts in my query. I'm a bit worried that this might
> mess up efficiency a lot?
>
I don't see a reason as to why 14 Boolean clauses in a query should be
considered a problem. Actually, it is too small a number to worry about.
Moreover, you can use a negated range query, as Shalin proposed. You should
make the field of type sortable-int in that case.

Any way i could get the functionality you're describing without having to
> basically write my own data import handler implementation

Worst come worst, you may need to write a transformer.

Cheers
Avlesh

On Wed, Aug 12, 2009 at 4:38 PM, Constantijn Visinescu
<[hidden email]>wrote:

> Hi,
>
> This seems like a bit of an unconventional suggestion but it just might
> work.
>
> I'd have to add the year because things might end up reserved far ahead, or
> they might be reserved again on the same day next year.
> I do have 2 questions:
>
> 1) Wouldn't it be terribly inefficient when I have 10million+ documents in
> my index and i want to check all of em for availabilty over a 2 week
> period?
> That'd mean 14 extra parts in my query. I'm a bit worried that this might
> mess up efficiency a lot? (Yes, I have aprox 10 mil documents in my index
> ;))
>
> 2) I populate my index with a data import handler (
> http://wiki.apache.org/solr/DataImportHandler ) from a table that has
> lines
> that contain a foreign key to the room number, a reserved_from date and a
> reserved_to date. Any way i could get the functionality you're describing
> without having to basically write my own data import handler
> implementation?
>
> Constantijn Visinescu
>
> On Wed, Aug 12, 2009 at 10:49 AM, Avlesh Singh <[hidden email]> wrote:
>
> > >
> > > Searches would be for documents (rooms) that don't have certain dates
> in
> > > their multi-valued fields for the a particular month.
> > > E.g if you wanted to find out rooms available on 15th, 16th and 17th of
> > > August, the query could be:
> > > q=!(+reserved_dates_August:15 +reserved_dates_August:16
> > > +reserved_dates_August:17)
> > >
> >
> > I was too fast to suggest the query above. The dates should be
> individually
> > negated.
> >
> > Cheers
> > Avlesh
> >
> > On Tue, Aug 11, 2009 at 9:39 PM, Avlesh Singh <[hidden email]> wrote:
> >
> > > From what I understood, you need a day level granularity (i.e booked on
> > > 15th, 16th and 17th of August) in your indexes. If this is true, then
> why
> > > even store a "date"? For your use case, I think this should suffice -
> > > <dynamicField name="reserved_dates_*" type="integer" indexed="true"
> > > stored="true" multiValued="true"/>
> > >
> > > Each document will have values like these for this particular field -
> > > reserved_dates_August => 15, 16, 19
> > > reserved_dates_September => 1, 3
> > > ....
> > >
> > > Searches would be for documents (rooms) that don't have certain dates
> in
> > > their multi-valued fields for the a particular month.
> > > E.g if you wanted to find out rooms available on 15th, 16th and 17th of
> > > August, the query could be:
> > > q=!(+reserved_dates_August:15 +reserved_dates_August:16
> > > +reserved_dates_August:17)
> > >
> > > Hope this helps.
> > >
> > > Cheers
> > > Avlesh
> > >
> > >
> > > On Tue, Aug 11, 2009 at 7:08 PM, Constantijn Visinescu <
> > [hidden email]
> > > > wrote:
> > >
> > >> Hello,
> > >>
> > >> I have a problem i'm trying to solve where i want to check if objects
> > are
> > >> reserved or not. (by reservation i mean like making a reservation at a
> > >> hotel, because you would like to stay there on certain dates).
> > >>
> > >> I have the following in my schema.xml
> > >>
> > >> <field name="name" type="text" indexed="true" stored="true"/>
> > >> <dynamicField name="reserved_from_*" type="date" indexed="true"
> > >> stored="true"/>
> > >> <dynamicField name="reserved_to_*" type="date" indexed="true"
> > >> stored="true"/>
> > >>
> > >> and the follwoing 2 documents in Solr
> > >>
> > >> <doc>
> > >> <str name="name">Room1</str>
> > >> <date name="reserved_from_11">2000-08-01T00:00:00Z</date>
> > >> <date name="reserved_to_11">2000-08-31T23:59:59Z</date>
> > >> </doc>
> > >> <doc>
> > >> <str name="name">Room2</str>
> > >> <date name="reserved_from_24">2000-08-01T00:00:00Z</date>
> > >> <date name="reserved_to_24">2000-08-13T23:59:59Z</date>
> > >> <date name="reserved_from_36">2000-08-20T00:00:00Z</date>
> > >> <date name="reserved_to_36">2000-08-22T23:59:59Z</date>
> > >> </doc>
> > >>
> > >> Now i want to run a query that gives me all documents(rooms) that are
> > >> avaiable from aug 15th to aug 18th (should return Room2), or from aug
> > 10th
> > >> to aug 15th (should return none) or from sept 1st to sept 5th (should
> > >> return
> > >> both).
> > >>
> > >> Is it possible to run queries like this in solr? (either with my
> current
> > >> schema setup, or a different one that accomplishes the same idea).
> > >>
> > >> I'm at a loss as to how to formulate a solr query to get the data i
> > want.
> > >>
> > >> Thanks in advance,
> > >> Constantijn Visinescu
> > >>
> > >
> > >
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: Searching for reservations/availability with Solr

Constantijn Visinescu
Hmm .. I looked up the transformer class and that should work, thanks for
the tip :)

Thinking about it a bit more, wouldn't it be easier to just add a field like

<field name="reserved_date" type="sint" indexed="true" stored="true"
multiValued="true" />

And use a function in the transformer to converts the day to int (number of
days since jan 1st 2000 or something).

Then if i use the same date to int conversion in my front end that queries
solr I can ask for all documents minus the range of int-ified dates that the
user wants.

That way I just have 1 field I can query and I dont have to mess with the
date to figure out the potential field name.

Solr should be able to query this easily for 10 mil documents? I'm
relatively new to using Solr and i don't have a feeling yet for what types
of single querys can bring Solr to it's knees ;) (so far i haven't found
anything that's actually useful that takes more then a second on my 10 mil
documents test index, so so far I'm happy and impressed :))


On Wed, Aug 12, 2009 at 1:18 PM, Avlesh Singh <[hidden email]> wrote:

> >
> > I'd have to add the year because things might end up reserved far ahead,
> or
> > they might be reserved again on the same day next year.
> >
> If that is the case, then yes you'll have to take year into account too.
>
> Wouldn't it be terribly inefficient when I have 10million+ documents in my
> > index and i want to check all of em for availabilty over a 2 week period?
> > That'd mean 14 extra parts in my query. I'm a bit worried that this might
> > mess up efficiency a lot?
> >
> I don't see a reason as to why 14 Boolean clauses in a query should be
> considered a problem. Actually, it is too small a number to worry about.
> Moreover, you can use a negated range query, as Shalin proposed. You should
> make the field of type sortable-int in that case.
>
> Any way i could get the functionality you're describing without having to
> > basically write my own data import handler implementation
>
> Worst come worst, you may need to write a transformer.
>
> Cheers
> Avlesh
>
> On Wed, Aug 12, 2009 at 4:38 PM, Constantijn Visinescu
> <[hidden email]>wrote:
>
> > Hi,
> >
> > This seems like a bit of an unconventional suggestion but it just might
> > work.
> >
> > I'd have to add the year because things might end up reserved far ahead,
> or
> > they might be reserved again on the same day next year.
> > I do have 2 questions:
> >
> > 1) Wouldn't it be terribly inefficient when I have 10million+ documents
> in
> > my index and i want to check all of em for availabilty over a 2 week
> > period?
> > That'd mean 14 extra parts in my query. I'm a bit worried that this might
> > mess up efficiency a lot? (Yes, I have aprox 10 mil documents in my index
> > ;))
> >
> > 2) I populate my index with a data import handler (
> > http://wiki.apache.org/solr/DataImportHandler ) from a table that has
> > lines
> > that contain a foreign key to the room number, a reserved_from date and a
> > reserved_to date. Any way i could get the functionality you're describing
> > without having to basically write my own data import handler
> > implementation?
> >
> > Constantijn Visinescu
> >
> > On Wed, Aug 12, 2009 at 10:49 AM, Avlesh Singh <[hidden email]> wrote:
> >
> > > >
> > > > Searches would be for documents (rooms) that don't have certain dates
> > in
> > > > their multi-valued fields for the a particular month.
> > > > E.g if you wanted to find out rooms available on 15th, 16th and 17th
> of
> > > > August, the query could be:
> > > > q=!(+reserved_dates_August:15 +reserved_dates_August:16
> > > > +reserved_dates_August:17)
> > > >
> > >
> > > I was too fast to suggest the query above. The dates should be
> > individually
> > > negated.
> > >
> > > Cheers
> > > Avlesh
> > >
> > > On Tue, Aug 11, 2009 at 9:39 PM, Avlesh Singh <[hidden email]>
> wrote:
> > >
> > > > From what I understood, you need a day level granularity (i.e booked
> on
> > > > 15th, 16th and 17th of August) in your indexes. If this is true, then
> > why
> > > > even store a "date"? For your use case, I think this should suffice -
> > > > <dynamicField name="reserved_dates_*" type="integer" indexed="true"
> > > > stored="true" multiValued="true"/>
> > > >
> > > > Each document will have values like these for this particular field -
> > > > reserved_dates_August => 15, 16, 19
> > > > reserved_dates_September => 1, 3
> > > > ....
> > > >
> > > > Searches would be for documents (rooms) that don't have certain dates
> > in
> > > > their multi-valued fields for the a particular month.
> > > > E.g if you wanted to find out rooms available on 15th, 16th and 17th
> of
> > > > August, the query could be:
> > > > q=!(+reserved_dates_August:15 +reserved_dates_August:16
> > > > +reserved_dates_August:17)
> > > >
> > > > Hope this helps.
> > > >
> > > > Cheers
> > > > Avlesh
> > > >
> > > >
> > > > On Tue, Aug 11, 2009 at 7:08 PM, Constantijn Visinescu <
> > > [hidden email]
> > > > > wrote:
> > > >
> > > >> Hello,
> > > >>
> > > >> I have a problem i'm trying to solve where i want to check if
> objects
> > > are
> > > >> reserved or not. (by reservation i mean like making a reservation at
> a
> > > >> hotel, because you would like to stay there on certain dates).
> > > >>
> > > >> I have the following in my schema.xml
> > > >>
> > > >> <field name="name" type="text" indexed="true" stored="true"/>
> > > >> <dynamicField name="reserved_from_*" type="date" indexed="true"
> > > >> stored="true"/>
> > > >> <dynamicField name="reserved_to_*" type="date" indexed="true"
> > > >> stored="true"/>
> > > >>
> > > >> and the follwoing 2 documents in Solr
> > > >>
> > > >> <doc>
> > > >> <str name="name">Room1</str>
> > > >> <date name="reserved_from_11">2000-08-01T00:00:00Z</date>
> > > >> <date name="reserved_to_11">2000-08-31T23:59:59Z</date>
> > > >> </doc>
> > > >> <doc>
> > > >> <str name="name">Room2</str>
> > > >> <date name="reserved_from_24">2000-08-01T00:00:00Z</date>
> > > >> <date name="reserved_to_24">2000-08-13T23:59:59Z</date>
> > > >> <date name="reserved_from_36">2000-08-20T00:00:00Z</date>
> > > >> <date name="reserved_to_36">2000-08-22T23:59:59Z</date>
> > > >> </doc>
> > > >>
> > > >> Now i want to run a query that gives me all documents(rooms) that
> are
> > > >> avaiable from aug 15th to aug 18th (should return Room2), or from
> aug
> > > 10th
> > > >> to aug 15th (should return none) or from sept 1st to sept 5th
> (should
> > > >> return
> > > >> both).
> > > >>
> > > >> Is it possible to run queries like this in solr? (either with my
> > current
> > > >> schema setup, or a different one that accomplishes the same idea).
> > > >>
> > > >> I'm at a loss as to how to formulate a solr query to get the data i
> > > want.
> > > >>
> > > >> Thanks in advance,
> > > >> Constantijn Visinescu
> > > >>
> > > >
> > > >
> > >
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: Searching for reservations/availability with Solr

Avlesh Singh
>
> <field name="reserved_date" type="sint" indexed="true" stored="true"
> multiValued="true" />
>
I think this should be good enough with the "number of days since ..." kind
of data.

Cheers
Avlesh

On Wed, Aug 12, 2009 at 5:26 PM, Constantijn Visinescu
<[hidden email]>wrote:

> Hmm .. I looked up the transformer class and that should work, thanks for
> the tip :)
>
> Thinking about it a bit more, wouldn't it be easier to just add a field
> like
>
> <field name="reserved_date" type="sint" indexed="true" stored="true"
> multiValued="true" />
>
> And use a function in the transformer to converts the day to int (number of
> days since jan 1st 2000 or something).
>
> Then if i use the same date to int conversion in my front end that queries
> solr I can ask for all documents minus the range of int-ified dates that
> the
> user wants.
>
> That way I just have 1 field I can query and I dont have to mess with the
> date to figure out the potential field name.
>
> Solr should be able to query this easily for 10 mil documents? I'm
> relatively new to using Solr and i don't have a feeling yet for what types
> of single querys can bring Solr to it's knees ;) (so far i haven't found
> anything that's actually useful that takes more then a second on my 10 mil
> documents test index, so so far I'm happy and impressed :))
>
>
> On Wed, Aug 12, 2009 at 1:18 PM, Avlesh Singh <[hidden email]> wrote:
>
> > >
> > > I'd have to add the year because things might end up reserved far
> ahead,
> > or
> > > they might be reserved again on the same day next year.
> > >
> > If that is the case, then yes you'll have to take year into account too.
> >
> > Wouldn't it be terribly inefficient when I have 10million+ documents in
> my
> > > index and i want to check all of em for availabilty over a 2 week
> period?
> > > That'd mean 14 extra parts in my query. I'm a bit worried that this
> might
> > > mess up efficiency a lot?
> > >
> > I don't see a reason as to why 14 Boolean clauses in a query should be
> > considered a problem. Actually, it is too small a number to worry about.
> > Moreover, you can use a negated range query, as Shalin proposed. You
> should
> > make the field of type sortable-int in that case.
> >
> > Any way i could get the functionality you're describing without having to
> > > basically write my own data import handler implementation
> >
> > Worst come worst, you may need to write a transformer.
> >
> > Cheers
> > Avlesh
> >
> > On Wed, Aug 12, 2009 at 4:38 PM, Constantijn Visinescu
> > <[hidden email]>wrote:
> >
> > > Hi,
> > >
> > > This seems like a bit of an unconventional suggestion but it just might
> > > work.
> > >
> > > I'd have to add the year because things might end up reserved far
> ahead,
> > or
> > > they might be reserved again on the same day next year.
> > > I do have 2 questions:
> > >
> > > 1) Wouldn't it be terribly inefficient when I have 10million+ documents
> > in
> > > my index and i want to check all of em for availabilty over a 2 week
> > > period?
> > > That'd mean 14 extra parts in my query. I'm a bit worried that this
> might
> > > mess up efficiency a lot? (Yes, I have aprox 10 mil documents in my
> index
> > > ;))
> > >
> > > 2) I populate my index with a data import handler (
> > > http://wiki.apache.org/solr/DataImportHandler ) from a table that has
> > > lines
> > > that contain a foreign key to the room number, a reserved_from date and
> a
> > > reserved_to date. Any way i could get the functionality you're
> describing
> > > without having to basically write my own data import handler
> > > implementation?
> > >
> > > Constantijn Visinescu
> > >
> > > On Wed, Aug 12, 2009 at 10:49 AM, Avlesh Singh <[hidden email]>
> wrote:
> > >
> > > > >
> > > > > Searches would be for documents (rooms) that don't have certain
> dates
> > > in
> > > > > their multi-valued fields for the a particular month.
> > > > > E.g if you wanted to find out rooms available on 15th, 16th and
> 17th
> > of
> > > > > August, the query could be:
> > > > > q=!(+reserved_dates_August:15 +reserved_dates_August:16
> > > > > +reserved_dates_August:17)
> > > > >
> > > >
> > > > I was too fast to suggest the query above. The dates should be
> > > individually
> > > > negated.
> > > >
> > > > Cheers
> > > > Avlesh
> > > >
> > > > On Tue, Aug 11, 2009 at 9:39 PM, Avlesh Singh <[hidden email]>
> > wrote:
> > > >
> > > > > From what I understood, you need a day level granularity (i.e
> booked
> > on
> > > > > 15th, 16th and 17th of August) in your indexes. If this is true,
> then
> > > why
> > > > > even store a "date"? For your use case, I think this should suffice
> -
> > > > > <dynamicField name="reserved_dates_*" type="integer" indexed="true"
> > > > > stored="true" multiValued="true"/>
> > > > >
> > > > > Each document will have values like these for this particular field
> -
> > > > > reserved_dates_August => 15, 16, 19
> > > > > reserved_dates_September => 1, 3
> > > > > ....
> > > > >
> > > > > Searches would be for documents (rooms) that don't have certain
> dates
> > > in
> > > > > their multi-valued fields for the a particular month.
> > > > > E.g if you wanted to find out rooms available on 15th, 16th and
> 17th
> > of
> > > > > August, the query could be:
> > > > > q=!(+reserved_dates_August:15 +reserved_dates_August:16
> > > > > +reserved_dates_August:17)
> > > > >
> > > > > Hope this helps.
> > > > >
> > > > > Cheers
> > > > > Avlesh
> > > > >
> > > > >
> > > > > On Tue, Aug 11, 2009 at 7:08 PM, Constantijn Visinescu <
> > > > [hidden email]
> > > > > > wrote:
> > > > >
> > > > >> Hello,
> > > > >>
> > > > >> I have a problem i'm trying to solve where i want to check if
> > objects
> > > > are
> > > > >> reserved or not. (by reservation i mean like making a reservation
> at
> > a
> > > > >> hotel, because you would like to stay there on certain dates).
> > > > >>
> > > > >> I have the following in my schema.xml
> > > > >>
> > > > >> <field name="name" type="text" indexed="true" stored="true"/>
> > > > >> <dynamicField name="reserved_from_*" type="date" indexed="true"
> > > > >> stored="true"/>
> > > > >> <dynamicField name="reserved_to_*" type="date" indexed="true"
> > > > >> stored="true"/>
> > > > >>
> > > > >> and the follwoing 2 documents in Solr
> > > > >>
> > > > >> <doc>
> > > > >> <str name="name">Room1</str>
> > > > >> <date name="reserved_from_11">2000-08-01T00:00:00Z</date>
> > > > >> <date name="reserved_to_11">2000-08-31T23:59:59Z</date>
> > > > >> </doc>
> > > > >> <doc>
> > > > >> <str name="name">Room2</str>
> > > > >> <date name="reserved_from_24">2000-08-01T00:00:00Z</date>
> > > > >> <date name="reserved_to_24">2000-08-13T23:59:59Z</date>
> > > > >> <date name="reserved_from_36">2000-08-20T00:00:00Z</date>
> > > > >> <date name="reserved_to_36">2000-08-22T23:59:59Z</date>
> > > > >> </doc>
> > > > >>
> > > > >> Now i want to run a query that gives me all documents(rooms) that
> > are
> > > > >> avaiable from aug 15th to aug 18th (should return Room2), or from
> > aug
> > > > 10th
> > > > >> to aug 15th (should return none) or from sept 1st to sept 5th
> > (should
> > > > >> return
> > > > >> both).
> > > > >>
> > > > >> Is it possible to run queries like this in solr? (either with my
> > > current
> > > > >> schema setup, or a different one that accomplishes the same idea).
> > > > >>
> > > > >> I'm at a loss as to how to formulate a solr query to get the data
> i
> > > > want.
> > > > >>
> > > > >> Thanks in advance,
> > > > >> Constantijn Visinescu
> > > > >>
> > > > >
> > > > >
> > > >
> > >
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: Searching for reservations/availability with Solr

Constantijn Visinescu
Ok, i'm going to go and implement this solution then. Thanks for the help :)

On Wed, Aug 12, 2009 at 2:11 PM, Avlesh Singh <[hidden email]> wrote:

> >
> > <field name="reserved_date" type="sint" indexed="true" stored="true"
> > multiValued="true" />
> >
> I think this should be good enough with the "number of days since ..." kind
> of data.
>
> Cheers
> Avlesh
>
> On Wed, Aug 12, 2009 at 5:26 PM, Constantijn Visinescu
> <[hidden email]>wrote:
>
> > Hmm .. I looked up the transformer class and that should work, thanks for
> > the tip :)
> >
> > Thinking about it a bit more, wouldn't it be easier to just add a field
> > like
> >
> > <field name="reserved_date" type="sint" indexed="true" stored="true"
> > multiValued="true" />
> >
> > And use a function in the transformer to converts the day to int (number
> of
> > days since jan 1st 2000 or something).
> >
> > Then if i use the same date to int conversion in my front end that
> queries
> > solr I can ask for all documents minus the range of int-ified dates that
> > the
> > user wants.
> >
> > That way I just have 1 field I can query and I dont have to mess with the
> > date to figure out the potential field name.
> >
> > Solr should be able to query this easily for 10 mil documents? I'm
> > relatively new to using Solr and i don't have a feeling yet for what
> types
> > of single querys can bring Solr to it's knees ;) (so far i haven't found
> > anything that's actually useful that takes more then a second on my 10
> mil
> > documents test index, so so far I'm happy and impressed :))
> >
> >
> > On Wed, Aug 12, 2009 at 1:18 PM, Avlesh Singh <[hidden email]> wrote:
> >
> > > >
> > > > I'd have to add the year because things might end up reserved far
> > ahead,
> > > or
> > > > they might be reserved again on the same day next year.
> > > >
> > > If that is the case, then yes you'll have to take year into account
> too.
> > >
> > > Wouldn't it be terribly inefficient when I have 10million+ documents in
> > my
> > > > index and i want to check all of em for availabilty over a 2 week
> > period?
> > > > That'd mean 14 extra parts in my query. I'm a bit worried that this
> > might
> > > > mess up efficiency a lot?
> > > >
> > > I don't see a reason as to why 14 Boolean clauses in a query should be
> > > considered a problem. Actually, it is too small a number to worry
> about.
> > > Moreover, you can use a negated range query, as Shalin proposed. You
> > should
> > > make the field of type sortable-int in that case.
> > >
> > > Any way i could get the functionality you're describing without having
> to
> > > > basically write my own data import handler implementation
> > >
> > > Worst come worst, you may need to write a transformer.
> > >
> > > Cheers
> > > Avlesh
> > >
> > > On Wed, Aug 12, 2009 at 4:38 PM, Constantijn Visinescu
> > > <[hidden email]>wrote:
> > >
> > > > Hi,
> > > >
> > > > This seems like a bit of an unconventional suggestion but it just
> might
> > > > work.
> > > >
> > > > I'd have to add the year because things might end up reserved far
> > ahead,
> > > or
> > > > they might be reserved again on the same day next year.
> > > > I do have 2 questions:
> > > >
> > > > 1) Wouldn't it be terribly inefficient when I have 10million+
> documents
> > > in
> > > > my index and i want to check all of em for availabilty over a 2 week
> > > > period?
> > > > That'd mean 14 extra parts in my query. I'm a bit worried that this
> > might
> > > > mess up efficiency a lot? (Yes, I have aprox 10 mil documents in my
> > index
> > > > ;))
> > > >
> > > > 2) I populate my index with a data import handler (
> > > > http://wiki.apache.org/solr/DataImportHandler ) from a table that
> has
> > > > lines
> > > > that contain a foreign key to the room number, a reserved_from date
> and
> > a
> > > > reserved_to date. Any way i could get the functionality you're
> > describing
> > > > without having to basically write my own data import handler
> > > > implementation?
> > > >
> > > > Constantijn Visinescu
> > > >
> > > > On Wed, Aug 12, 2009 at 10:49 AM, Avlesh Singh <[hidden email]>
> > wrote:
> > > >
> > > > > >
> > > > > > Searches would be for documents (rooms) that don't have certain
> > dates
> > > > in
> > > > > > their multi-valued fields for the a particular month.
> > > > > > E.g if you wanted to find out rooms available on 15th, 16th and
> > 17th
> > > of
> > > > > > August, the query could be:
> > > > > > q=!(+reserved_dates_August:15 +reserved_dates_August:16
> > > > > > +reserved_dates_August:17)
> > > > > >
> > > > >
> > > > > I was too fast to suggest the query above. The dates should be
> > > > individually
> > > > > negated.
> > > > >
> > > > > Cheers
> > > > > Avlesh
> > > > >
> > > > > On Tue, Aug 11, 2009 at 9:39 PM, Avlesh Singh <[hidden email]>
> > > wrote:
> > > > >
> > > > > > From what I understood, you need a day level granularity (i.e
> > booked
> > > on
> > > > > > 15th, 16th and 17th of August) in your indexes. If this is true,
> > then
> > > > why
> > > > > > even store a "date"? For your use case, I think this should
> suffice
> > -
> > > > > > <dynamicField name="reserved_dates_*" type="integer"
> indexed="true"
> > > > > > stored="true" multiValued="true"/>
> > > > > >
> > > > > > Each document will have values like these for this particular
> field
> > -
> > > > > > reserved_dates_August => 15, 16, 19
> > > > > > reserved_dates_September => 1, 3
> > > > > > ....
> > > > > >
> > > > > > Searches would be for documents (rooms) that don't have certain
> > dates
> > > > in
> > > > > > their multi-valued fields for the a particular month.
> > > > > > E.g if you wanted to find out rooms available on 15th, 16th and
> > 17th
> > > of
> > > > > > August, the query could be:
> > > > > > q=!(+reserved_dates_August:15 +reserved_dates_August:16
> > > > > > +reserved_dates_August:17)
> > > > > >
> > > > > > Hope this helps.
> > > > > >
> > > > > > Cheers
> > > > > > Avlesh
> > > > > >
> > > > > >
> > > > > > On Tue, Aug 11, 2009 at 7:08 PM, Constantijn Visinescu <
> > > > > [hidden email]
> > > > > > > wrote:
> > > > > >
> > > > > >> Hello,
> > > > > >>
> > > > > >> I have a problem i'm trying to solve where i want to check if
> > > objects
> > > > > are
> > > > > >> reserved or not. (by reservation i mean like making a
> reservation
> > at
> > > a
> > > > > >> hotel, because you would like to stay there on certain dates).
> > > > > >>
> > > > > >> I have the following in my schema.xml
> > > > > >>
> > > > > >> <field name="name" type="text" indexed="true" stored="true"/>
> > > > > >> <dynamicField name="reserved_from_*" type="date" indexed="true"
> > > > > >> stored="true"/>
> > > > > >> <dynamicField name="reserved_to_*" type="date" indexed="true"
> > > > > >> stored="true"/>
> > > > > >>
> > > > > >> and the follwoing 2 documents in Solr
> > > > > >>
> > > > > >> <doc>
> > > > > >> <str name="name">Room1</str>
> > > > > >> <date name="reserved_from_11">2000-08-01T00:00:00Z</date>
> > > > > >> <date name="reserved_to_11">2000-08-31T23:59:59Z</date>
> > > > > >> </doc>
> > > > > >> <doc>
> > > > > >> <str name="name">Room2</str>
> > > > > >> <date name="reserved_from_24">2000-08-01T00:00:00Z</date>
> > > > > >> <date name="reserved_to_24">2000-08-13T23:59:59Z</date>
> > > > > >> <date name="reserved_from_36">2000-08-20T00:00:00Z</date>
> > > > > >> <date name="reserved_to_36">2000-08-22T23:59:59Z</date>
> > > > > >> </doc>
> > > > > >>
> > > > > >> Now i want to run a query that gives me all documents(rooms)
> that
> > > are
> > > > > >> avaiable from aug 15th to aug 18th (should return Room2), or
> from
> > > aug
> > > > > 10th
> > > > > >> to aug 15th (should return none) or from sept 1st to sept 5th
> > > (should
> > > > > >> return
> > > > > >> both).
> > > > > >>
> > > > > >> Is it possible to run queries like this in solr? (either with my
> > > > current
> > > > > >> schema setup, or a different one that accomplishes the same
> idea).
> > > > > >>
> > > > > >> I'm at a loss as to how to formulate a solr query to get the
> data
> > i
> > > > > want.
> > > > > >>
> > > > > >> Thanks in advance,
> > > > > >> Constantijn Visinescu
> > > > > >>
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>