Skip to main content

Joins

The QuerySearch system provides a way to perform explicit joins in cases where another table has to be involved in a query. Typically, the JPA implementation will determine when type of join is needed when performing an implicit join by comparing the primary key of a table with the foreign key referencing it, but there are cases where this results in different behavior than the developer intends.

Often a cross join operation is selected, which removes any records for which the foreign key field is null. In that case a developer may wish to also include those records, requiring a left outer join.

Example

In this example a join operation is used to implement sorting based on the name of a linked element.

  • It requires a left outer join to ensure that no records are omitted when the linkfield is not set.
  • The condition is added to make sure the join is only used when sorting on that field is actually requested in the search operation.
  • The variable name o_country could be used in a constraint to reference the linked Country object.
QueryBuilder queryBuilder = select("o")
    .from("com.example.city.City", "o"
        // @anchor:from-arguments:start
        // @anchor:from-arguments:end
        // anchor:custom-from-arguments:start
        , join("o.country", "o_country").leftOuter()
            .condition(() -> sortFields.stream().map(SortField::getFieldName)
                .anyMatch("country.name"::equals))
        // anchor:custom-from-arguments:end
    )

Join builders​

The JoinBuilder class is implemented to provide a clean syntax of defining join operations, similar to how query constraints are represented. Instances of the builder can be passed as an argument in QueryBuilder.from(), in order to add them to the query.

Factory method​

A factory method for the builder is provided as static JoinBuilder join(String fieldReference, String variableName):

  • fieldReference: A reference to the field that points to the table that should be joined with, starting from the main table the query is executed on. This will usually take the form of "o.fieldName".
  • variableName: A name for the variable that will represent the joined table in the query. This can be anything, but it should not collide with any other variable in the query to avoid ambiguity.

Builder methods​

Several methods exist on the JoinBuilder class to define how the join should be handled:

  • condition(Supplier<Boolean> condition): This method takes a lambda returning a boolean as an argument that determines whether the join should be added to the query or not. If no condition is defined, the join will always be added. This can be useful to add the join only when a specific filter operation requires it, based on the content of the QueryFilter object.
  • eager(): Enable eager fetching of the join relationship. This ensures that the record of the joining table is always fetched immediately, regardless of whether is actually involved in the query. By default this is not the case.
  • leftOuter(): Use a left outer join to ensure that rows in the main table that contain null for the field reference are still included in the results. The default is an inner join, which will not include these records.