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
