Fulltext search on multiple tables

[Former Member]

Hi,

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 {
        repId,
        @Search: { defaultSearchElement: true}
        @EnterpriseSearch.snippets.enabled: true
        longText,
        @Search: { defaultSearchElement: true}
        @EnterpriseSearch.snippets.enabled: true
        document.data
    }

I expose it as oData service (.xsodata).

service {
  "foo.bar::DataModel.RepDocSearchView" 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?

[JoergM]

Hi Patrick,

When you expose the view as an OData service, each line in the view is treated as a single 'object'. The service doesn't know that there is a 1:n relationship between repairs and documents.

Currently this is the only option to do a search in columns of multiple tables. So in your case it is not possible to expose the object model of repairs, documents and their association in OData and then use this object model for search.

As the OData service sees individual lines of the view only, you get back duplicate repairs by default because this is the expected OData behavior.

With a few changes it is possible to get back one line per repair only.

1) Changes to the view definition

@Search.searchable: true 

define view RepDocSearchView as select from Repair { 

  @EnterpriseSearch.key: true

  repId, 

  @Search: { defaultSearchElement: true} 

  @EnterpriseSearch.snippets.enabled: true 

  longText, 

  @Search: { defaultSearchElement: true} 

  document.data 

}

The @EnterpriseSearch.key annotation tells the search runtime that it should return one line per repId only.

Removing the @EnterpriseSearch.snippets annotation makes sure that the document.data column is not returned.

2) Changes to the OData call

To avoid duplicate repairs you have to make sure that you request columns from the repairs table only:

...&search=...&$select=repId,longText&...

Now you should get back one line per repair only.

With HANA SPS09 it is not possible to identify the column that contains the matching term (except than checking the snippets for the longText column). For SPS10 there are plans to return the information in which column(s) a search term was found.

A few final comments on the OData service definition:

The key definition key("repId") is not valid for the view because it is not unique. So you cannot read entries using this id - the OData service will return an error because there are duplicate keys in the table. Nevertheless, this is the key definition you should use if you do not want to get back duplicate repairs.

Extending the key to key("repId", "document.id")would result in a correct key definition for OData, but return duplicate repairs again, because the document.id column is then automatically requested by the OData service.

I hope this helps.

Jörg