Joins

Non-trivial queries often require data of several entity types (tables). In the SQL world, you can make that happen by “joining” two or more tables using a join condition.

Let’s consider an entity User, which has a one-to-many relation to an Address entity. Then we want to query for users living on “Sesame Street”: We would have to join the Address entity with the User entity using the user ID and define a WHERE condition on the Address entity:

The join requires the target entity class as a parameter and a join property of each entity. In the example, just the join property of the Address entity is defined, because the primary key property is used by default. In other words, the query results in users, which have an Address entity with a userId equal to the User entity’s ID and also have a specific street.

QueryBuilder Join API

Because you can omit the join property when the primary key property can be used, there are three overloaded join methods available in QueryBuilder:

Chained Joins

In addition, greenDAO allows to chain joins across multiple tables. Here, you define a join using another join and a destination entity. In this case, the destination entity of the first join becomes the starting entity for the second join.

The QueryBuilder API for chained joins looks like this:

Let’s look at another example with three entities: City, Country, and Continent. If we want to query all cities in Europe with a population of at least one million, it would look like this:

Self Join / Tree example

A join can also be used with relations referencing a single entity. For example, we want to find all persons, whose grandfather’s name is “Lincoln”. Let’s assume we have a Person entity with a fatherId property pointing to the same entity. Then, the query is built like this:

As you can see, joins are a powerful tool to build queries spanning multiple entity types or relations.

Spread the love