Advanced queries involving multiple entities

As I've mentioned a number of times, FIQL can help with expressing the advanced search conditions in a compact and easy to understand syntax.

The queries like "find all the books published before a given date" are very easy to type in FIQL and extending this query with a restriction like "and having the page count between 80 and 100 pages or less than 20" is quite straightforward too, manually, or with the help of the client FIQL builder.

However, what if one would like to do a query involving multiple entities, for example, "find the library in a preferred location where all the books published before a given date and written by more than 2 authors are available" ? Or imagine or more interesting query if you'd like.

This bit, "where all the books published before a given date and written by more than 2 authors" is an easy one to support with the CXF search extension. The question is, how to structure the advanced query such that Book entities matching the arbitrary complex query are selected first and then a Library entity is checked per every Book on whether it matches its own selection criteria or not.

Another question is how to express the requirement that it is actually "the list of books",  "published before a given date and written by more than 2 authors and available in one of the libraries in a preferred location", that has to be returned to a user.

As you can see, there are at least two main requirements to deal with. First one is how to let users choose which entity (among a number of entities being matched) is actually returned which affects the response representation. Second one is how to do an advanced query involving multiple entities at the level of the JAX-RS application handling such a query.

The good news is that the first requirement can be easily managed with the help of URI path and/or query components. For example, an expression like "/books[date=le=2012-02-11]/library" can be used to find all the libraries having the matching books, and the one like "/library(dist=lt=10;dist=gt=5)/books(date=le=2012-02-11)" can get all the books matching a given search criteria available in a library within a 10 km range from some well-known location (such as the city center) but not closer than 5 (given that the parking is not free within a 5 km range).

It is really up to the designer of the service how to get the actual expressions captured, which characters to use to mark them, example "[" and "]", "(" and ")" or ";" and how to support the selective retrieval of the entities involved in the multi-entity search. See this section for more examples.

Yet another good news is that a complex multi-entity query can be managed pretty easily at the JAX-RS resource level, see this section for a number of options on how it might be done. Note how multiple FIQL expressions can be handled.

What is mentioned there is that it can be more optimal to get say JPA to execute a JOIN like query at a database level in one go as opposed to getting it to find a list of entities matching the first FIQL expression, then doing the follow-up in-memory match against the selected entities.

Right now CXF can not automate the process of creating a JPA TypedQuery or SQL expression which can 'span' the multiple entities - this is the next task, and in meantime please experiment with the demonstrated approach and see how it fares against the one involving the composite JOIN-like queries. Alternatively, introspect the captured search conditions as shown in this section and build a composite query in the code.

Please also have a look at the OData4J project and check how the advanced queries can be managed there. 

Finally, I'd like to answer on the following question: why one would worry about offering such a search interface to users, would it not be simpler to offer a Google-like search interface where one enters a few words and gets the list of matching data pages ? I think that when one creates a service for exposing the data with the known properties and relationships, the opportunity is there to offer an optimized search engine for users to get the customized search experience.