Implement Search API WHERE Filter

[MRichards99]

Description: Similar to DataGateway API, the search API supports a where filter. This uses a different syntax to the WHERE filters in DataGateway API which has additional functionality. Basic syntax: {"where": {"property": "value"}}

The Search API requires all of the LoopBack operators to be supported. The following operators are not implemented in DataGateway API and need to be checked that they can be supported and then implemented into the Search API:

• lte - should just need to add that as an operator in create_filter()
• between - does Python ICAT's creation of JPQL queries support this? Not sure how you'd specify two values within Python ICAT. Might be easier just to implement < and > conditions instead of using between
• inq - we use in to specify in an array, just need to add a conditional to that branch
• nin - need to see what JPQL supports for not in
• near - this is geographical, but ICAT doesn't store geographical data so I guess we can ignore this
• neq - like inq, we support this but using a different name, simple to add
• ilike - this is already a feature request from https://github.com/ral-facilities/datagateway-api/issues/243, although maybe making a separate ilike operator might be required for this (instead of converting the existing like operator)
• nilike - once case insensitivity is supported in Python ICAT then this shouldn't be too difficult?
• regexp - does JPQL support this/do we need to support this for a first release of the Search API?

The text operator is something new for the Search API. It's used to query data matching a string and can be applied to the following fields:

• Dataset - Title
• Document - Title, Summary
• File - Name
• Instrument - Name, Facility
• Sample - Name, Description
• Technique - Name

We should check what should happen if the text operator is applied to a field not listed above (via the example implementation). I'm guessing a 400 would be appropriate.

AND/OR functionality is required in the Search API as well. DataGateway API uses AND implicitly via Python ICAT, so OR is the new feature required. Python ICAT does not currently support the OR functionality (evidence) so support needs to be added on Python ICAT's side before this can be implemented on the Search API.

Acceptance criteria:

• [ ] Get basic syntax to work
• [ ] Allow different operators to be used on the new syntax (between, inq etc.)
• [ ] Implement text operator functionality on specified fields, handling cases where the operator is used on unsupported fields
• [ ] Support needed for OR operator in Python ICAT (LINK TO ISSUE ON PYTHON ICAT REPO ONCE CREATED)
• [ ] Add OR functionality, being able to choose AND/OR in the request
• [ ] Unit tests should be written to cover each aspect of the where filter
• Basic syntax - {"where": {"property": "value"}}
• Different operators - {"where": {"property": {"operator": "value"}}}
• Text operators - {"where": {"text": "value"}}
• AND/OR functionality - {"where": {"and": [{"property": "value"}] }}

[VKTB]

I investigated the following 3 operators which are currently not implemented in DataGateway API:

between:

• JPQL supports this operator. SELECT o FROM Instrument o WHERE o.createTime BETWEEN '2002-01-01' and '2006-12-31' returns all the instruments that were created between 1st Jan 2002 and 31st Dec 2006. These are the instruments with id 1, 6, 10, 12.
• Python ICAT seems to support this too. Sending the {'createTime': "between '2002-01-01' and '2006-12-31'"} Where filter to it, returns the same instruments as above.
• We can add a between operator in create_filter() in DataGateway API that will require clients to send such a Where filter in this format: {"createTime": {"between": ["2002-01-01", "2006-12-31"]}}.

nin:

• JPQL supports this operator. SELECT o FROM Instrument o WHERE o.name not in ('INSTRUMENT 1', 'INSTRUMENT 5') returns all the instruments except the ones with name INSTRUMENT 1 and INSTRUMENT 5.
• Python ICAT seems to support this too. Sending the {'name': "not in ('INSTRUMENT 1', 'INSTRUMENT 5')"} Where filter to it, returns the same instruments as above.
• We can add a nin operator in create_filter() in DataGateway API that will require clients to send such a Where filter in this format: {"name": {"nin": ["INSTRUMENT 1", "INSTRUMENT 5"]}}. DataGateway API supports the in operator which is the opposite of nin so it would be just a case of reusing and tweaking the in logic.

regexp:

• JPQL supports this operator. SELECT o FROM Instrument o WHERE o.url REGEXP '^http:' returns all the instruments that have a url which begins with http:. These are the instruments with id 1, 4, 5, 6, 13 and 14.
• Python ICAT seems to support this too. Sending the {'url': "regexp '^http:'"} Where filter to it, returns the same instruments as above.
• We can add a regexp operator in create_filter() in DataGateway API that will require clients to send such a Where filter in this format: {"url": {"regexp ": "^http:"}}.

[MRichards99]

Brilliant, thanks for investigating that! It's great they are all supported in Python ICAT.

For the between operator, I think we can assume there will only ever be two values (I don't think you can search between 3 values in query languages?). The search API expects the two values in a list (see example) so if we decide to implement it into DataGateway API, then we should follow the same way of getting the user's input in.

[VKTB]

I agree - we can assume there will only ever be two values. Loop Back's operator documentation seems to indicate that you can only have two values. Happy to implement any of these 3 operators in DataGateway API.