I'm aiming to replace our existing search functionality with Elasticsearch and I'm taking small steps towards that goal. Right now I'm trying to update a specific method but I would like some guidance.
We have in our system Groups and Units and every Unit belongs to a Group. Right now I've indexed these separate into index groups and index units respectively but my problem is that I've got a some pretty complicated sql queries in some places that I need to translate (I'm in .net using Nest) and I'm starting to wonder if it would be better to index these tables as one nested index instead.
The query I'm trying to translate right now looks like this
const string selectSql = @" SELECT groups.GroupId, groups.GroupName, units.UnitId, units.UnitName FROM Groups groups LEFT JOIN Units units ON groups.GroupId = units.GroupId WHERE groups.GroupId IN (SELECT groups.GroupId FROM Groups LEFT JOIN Units units ON groups.GroupId = units.GroupId /**where**/ GROUP BY groups.GroupId, groups.GroupName /**orderby**/ OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY) GROUP BY groups.GroupId, groups.GroupName, units.UnitId, units.UnitName /**orderby**/ , units.UnitName ";
So my question is, is there a smart enough way to translate the left join and compare fields from different indices or would it be best to give nested mapping a go making a new, combined index? And if so would that apply to bigger, more nested examples (with more tables, joins and data) too?