Skip Headers

# Using TopLink Expression API For Joins

Previous

Next

You can use joins anywhere expressions are used, including: selection-criteria, ordering (see "Specifying Read Ordering"), report queries (see "Report Query"), partial objects (see "Partial Object Queries"), one-to-one relational mappings (see "Configuring Joining at the Mapping Level"), and join reading (see "Join Reading and Object-Level Read Queries").

Use the expression API shown in Table: Expression API for Joins to configure inner and outer join expressions.

Expression API for Joins

Expression API | Type of Join | Type of Mapping
--- | --- | ---
`get` | inner | one-to-one
`getAllowingNull` | outer | one-to-one
`anyOf` | inner | one-to-many, many-to-many
`anyOfAllowingNone` | outer | one-to-many, many-to-many

To query across a one-to-many or many-to-many relationship, use the `anyOf` operation. As its name suggests, this operation supports queries that return all items on the "many" side of the relationship that satisfy the query criteria.

Example: Using an Expression with a One-to-Many Relationship illustrates an expression that returns employees who manage at least one employee (through a one-to-many relationship) with a salary less than $10,000.

Using an Expression with a One-to-Many Relationship

    emp.anyOf("managedEmployees").get("salary").lessThan(10000);

Example: Using an Expression with a Many-to-Many Relationship illustrates how to query across a many-to-many relationship using a similar strategy:

Using an Expression with a Many-to-Many Relationship

    emp.anyOf("projects").equal(someProject)

TopLink translates these queries into SQL that joins the relevant tables using a `DISTINCT` clause to remove duplicates. TopLink translates Example: Using an Expression with a One-to-Many Relationship into the following SQL:

    SELECT DISTINCT . . . FROM EMP t1, EMP t2 WHERE
    t2.MANAGER_ID = t1.EMP_ID AND t2.SALARY < 10000

You can use one-to-one and one-to-many join expressions in an `ObjectLevelReadyQuery` to configure joins on a per-query basis (see "Join Reading and Object-Level Read Queries").

You can also configure joins at the mapping level (see "Configuring Joining at the Mapping Level").

Note: Calling `anyOf` once would result in a different outcome than if you call it twice. For example, if you query for an employee with a telephone area code of 613 and a number of 123-4599, you would use a single anyOf and a temporary variable. If you query for an employee, whose telephone has an area code of 613, and whose telephone has a number of 123-4599, you would call anyOf twice.