You are viewing a plain text version of this content. The canonical link for it is here.
Posted to general@lucene.apache.org by josh45 <jo...@gmail.com> on 2010/05/25 22:24:53 UTC

Lucene with Relational Databases

Hello All,

First let me start of by saying thanks for any help anyone can give me.  I
am a newb when it comes to Lucene and what it can do and I have been
searching to see if it can solve one of my problems.  However, I guess I
don't have my black belt yet with searching google and have not yet been
able to find the answer so I figured I could ask the experts :).

So here it goes....

I have a relational database for example I have the tables, orders,
products, and customers.  The relationship is in orders has a product id and
customer id.  My issue is I would like to enter in something like "Mike and
printer" And have it return the the customer id and the printer id.  Pretty
bad example I know but I think it gets the point across.  If this can be
done or can not with Lucene I would love to know.  If I need to go into
anymore detail please let me know I would be glad to.  Thanks again, I know
its a poor question but I would love to know the answer or now where to get
the answer!
-- 
View this message in context: http://lucene.472066.n3.nabble.com/Lucene-with-Relational-Databases-tp843136p843136.html
Sent from the Lucene - General mailing list archive at Nabble.com.

Re: Lucene with Relational Databases

Posted by Sanne Grinovero <sa...@gmail.com>.
2010/5/25 josh45 <jo...@gmail.com>:
>
> Hello All,
>
> First let me start of by saying thanks for any help anyone can give me.  I
> am a newb when it comes to Lucene and what it can do and I have been
> searching to see if it can solve one of my problems.  However, I guess I
> don't have my black belt yet with searching google and have not yet been
> able to find the answer so I figured I could ask the experts :).
>
> So here it goes....
>
> I have a relational database for example I have the tables, orders,
> products, and customers.  The relationship is in orders has a product id and
> customer id.  My issue is I would like to enter in something like "Mike and
> printer" And have it return the the customer id and the printer id.  Pretty
> bad example I know but I think it gets the point across.  If this can be
> done or can not with Lucene I would love to know.  If I need to go into
> anymore detail please let me know I would be glad to.  Thanks again, I know
> its a poor question but I would love to know the answer or now where to get
> the answer!

if you're using Hibernate or JPA to abstract your database I would
recommend to look into Hibernate Search; also it's quite simple to use
in case you're new to Lucene.
(but my suggestion doesn't make sense if the application exists
already and is not using Hibernate).

Sanne

Re: Lucene with Relational Databases

Posted by "J. Delgado" <jo...@gmail.com>.
If you are using Oracle you can take advantage of the Lucene Domain Index:

http://docs.google.com/Doc?docid=0AVfwyTi0k18ZZGRndzdzanBfNTRmZ2o5a2c#1_Introduction_899460987187922


On Tue, May 25, 2010 at 3:40 PM, Ted Dunning <te...@gmail.com> wrote:
> This kind of search is really effective with Lucene, but you will have to
> denormalize your data into documents which then have to be indexed by
> Lucene.  This isn't massively hard, but there is some serious thought that
> needs to go into the denormalization.
>
> That denormalization step would create one document for each kind of object
> you would like to search for.  That document would contain all of the
> referenced material that you might like to include in your query.  For your
> application, it sounds like you might like to retrieve customers and orders.
>  A customer should probably have a list of recent products ordered, orders
> should list the customer and the products.  Each document should also
> include a document type so you can add that to your queries to limit what
> you find.
>
> If you dump these denormalized documents to a fairly standard XML format,
> you can bring up a copy of Solr and be searching your data within a few tens
> of minutes.  It is definitely worth the experiment so that you understand
> some of the possibilities.
>
> On Tue, May 25, 2010 at 1:24 PM, josh45 <jo...@gmail.com> wrote:
>
>> I have a relational database for example I have the tables, orders,
>> products, and customers.  The relationship is in orders has a product id
>> and
>> customer id.  My issue is I would like to enter in something like "Mike and
>> printer" And have it return the the customer id and the printer id.  Pretty
>> bad example I know but I think it gets the point across.  If this can be
>> done or can not with Lucene I would love to know.  If I need to go into
>> anymore detail please let me know I would be glad to.  Thanks again, I know
>> its a poor question but I would love to know the answer or now where to get
>> the answer!
>>
>

Re: Lucene with Relational Databases

Posted by Ted Dunning <te...@gmail.com>.
This kind of search is really effective with Lucene, but you will have to
denormalize your data into documents which then have to be indexed by
Lucene.  This isn't massively hard, but there is some serious thought that
needs to go into the denormalization.

That denormalization step would create one document for each kind of object
you would like to search for.  That document would contain all of the
referenced material that you might like to include in your query.  For your
application, it sounds like you might like to retrieve customers and orders.
 A customer should probably have a list of recent products ordered, orders
should list the customer and the products.  Each document should also
include a document type so you can add that to your queries to limit what
you find.

If you dump these denormalized documents to a fairly standard XML format,
you can bring up a copy of Solr and be searching your data within a few tens
of minutes.  It is definitely worth the experiment so that you understand
some of the possibilities.

On Tue, May 25, 2010 at 1:24 PM, josh45 <jo...@gmail.com> wrote:

> I have a relational database for example I have the tables, orders,
> products, and customers.  The relationship is in orders has a product id
> and
> customer id.  My issue is I would like to enter in something like "Mike and
> printer" And have it return the the customer id and the printer id.  Pretty
> bad example I know but I think it gets the point across.  If this can be
> done or can not with Lucene I would love to know.  If I need to go into
> anymore detail please let me know I would be glad to.  Thanks again, I know
> its a poor question but I would love to know the answer or now where to get
> the answer!
>

Re: Lucene with Relational Databases

Posted by Grant Ingersoll <gs...@apache.org>.
On May 25, 2010, at 4:24 PM, josh45 wrote:

> 
> Hello All,
> 
> First let me start of by saying thanks for any help anyone can give me.  I
> am a newb when it comes to Lucene and what it can do and I have been
> searching to see if it can solve one of my problems.  However, I guess I
> don't have my black belt yet with searching google and have not yet been
> able to find the answer so I figured I could ask the experts :).
> 
> So here it goes....
> 
> I have a relational database for example I have the tables, orders,
> products, and customers.  The relationship is in orders has a product id and
> customer id.  My issue is I would like to enter in something like "Mike and
> printer" And have it return the the customer id and the printer id.  Pretty
> bad example I know but I think it gets the point across.  If this can be
> done or can not with Lucene I would love to know.  If I need to go into
> anymore detail please let me know I would be glad to.  Thanks again, I know
> its a poor question but I would love to know the answer or now where to get
> the answer!

Sounds like you will need to pull into a document, the prod. id and customer id, or, alternatively, in your app, convert Mike -> Customer Id and Printer -> product id and then query on those things.

But yes, more detail would be good.

-Grant