Skip to Content
Former Member
Feb 06, 2015 at 01:11 PM

Fulltext search on multiple tables



I'm trying to use fulltext search on multiple tables with oData from UI5.

I have the following data model:

@Catalog.tableType : #COLUMN
    entity Repair {
        key repId: String(12) not null;
        document : Association[*] to RepDocument on document.repId= repId;
        @SearchIndex: { text: { enabled: true, fuzzy: { enabled: true } }
        longText: String(2000) not null;

@Catalog.tableType : #COLUMN
    entity RepDocument{
        key id: Integer;
        repId: String(12) not null;
        repair: Association[1] to Repair on repair.repId= repId;
        @SearchIndex: { text: { enabled: true, async: false, textAnalysis: { mode:#SIMPLE } }, fuzzy: { enabled: true } }
        data: LargeBinary not null;

I want to search on both tables display the "Repair"s, that have a match in the repair itself, or in one of the referenced documents.

Ideally, I would also like to know which documents matched, but that might be a more difficult problem.

So i made a view like this:

@Search.searchable: true
    define view RepDocSearchView as select from Repair {
        @Search: { defaultSearchElement: true}
        @EnterpriseSearch.snippets.enabled: true
        @Search: { defaultSearchElement: true}
        @EnterpriseSearch.snippets.enabled: true

I expose it as oData service (.xsodata).

service {
  "" as "RepDoc" key ("repId");

And access it with UI5 through an oDataModel, adding a search=.... parameter.

But this will return duplicate repairs, if the match is in longText or if several documents' data matches. Doing a group by on the client side seems like a bad solution to me and will make displaying the data with ui5 with paging etc. difficult.

Also, I still won't know which document matched or if the match was in the repair.

How can I get the matching repairs without duplicates?

Can I somehow also know and display on the client in ui5, if the repair matched or which of the documents matched?

Maybe this can be done using calculation views?