I need a SELECT which filters IDS based on an ‘id’ list coming from another 
database, i.e. SELECT * FROM maindb.maintable WHERE id IN (SELECT myid FROM 
otherdb.other_table). 

The docs are fetched from a MySql DB while the list of IDs to includ in that 
first SELECT WHERE statement is fetched from a view in a PgSql DB, so you 
cannot simply include the table name in the WHERE clause. I have added two 
dataSources, and I think I’ll need an <entity> which caches the ID list from 
‘otherdb’ in memory and then somehow references that cached list in place of 
the inner select?

However since the list of IDs are UUID strings and there are a few thousand of 
them, I guess the SELECT becomes too large if you just send a huge OR clause to 
MySql. I have been thinking about a 2-stage solution, first create a temp table 
in MySql and INSERT all the IDs there, then include the temp table in the WHERE 
as usual, and delete the tmp table afterwards. Does DIH have a built-in and 
efficient feature for such an operation?

--
Jan Høydahl, search solution architect
Cominvent AS - www.cominvent.com

Reply via email to