I'm looking to find the best way to add a filter in the index that is based on a database query, but that isn't included in the index.
We have a table with about 200 million records and we need to use them in the query. That's obviously too large to maintain in the Lucene index. But, it's a very specific query that will run fast when directly against the database.
This is for a list of parts that make up a vehicle. So each vehicle will have a VIN, and there are on average 650 parts pre vehicle/VIN.
So, what we need is to be able to do a VIN search and get back the 650 parts from that search to be used in a product catalog search.
I can think of two ways to do this:
- Have some type of runtime database query as part of the index
- Or maybe we do a database query first, get an array of 650 productIds, and pass that to the index to filter by just those products.
What would be the best way to achieve this?
Thanks,
Scott