Joining an entity to a query with a simple join

Joining tables in SQL SELECT statements

In SQL, you can join two tables based on columns with matching values to form a new, virtual join table. The rows in the join table contain columns from each original table. On the join table, you can specify restrictions, which can have columns from both tables. When the SQL query runs, the result set has rows and columns from the join table. A join typically involves a column in one table that contains a foreign key value that matches a primary key value in a column in a second table. A primary key is a column with values that uniquely identify each row in a database table. A foreign key is a column in one table that contains values of a primary key in another table. The definition of a foreign key column specifies whether the values in the column are unique.

The following example SQL Select statement uses the JOIN keyword to join the addresses table to the companies table.

SELECT * FROM companies
  JOIN addresses 
    ON addresses.ID = companies.primary_address;

In response to the preceding example SQL statement, the database returns a result set with all the rows in the companies table that have a primary address. The result set includes columns for the companies and columns for their primary addresses. The result set does not include companies without a primary address.

The columns in the result set are a union of the columns in the companies and addresses tables. The company named “Industrial Wire and Chain” is not in the result set. That row in the companies table has null as the value for primary_address, so no row in the addresses table matches.

Joining entities with the query builder API

With the query builder API, you can join a related entity type to the primary entity type of the query. When you join an entity type to a query, the query builder API construct an internal object to represent the joined entity and its participation in the query.

In the simplest case, use the join method to join a dependent entity type to a query. You must specify the name of a property on the primary entity that the Data Dictionary defines as a foreign key to the dependent entity. Use the Gosu property name, not the actual column name in the database.

For example, the following Gosu code shows how to join the Address entity to a query of the Company entity. The code uses the PrimaryAddress property of Company, which is a foreign key to Address.

uses gw.api.database.Query

var query = Query.make(Company)
query.join(Company#PrimaryAddress) // Join Address entity to the query by a foreign key on Company
var select = query.select()

// Fetch the data with a for loop and print it. 
for (company in select) {
  print (company)
}

The Company named “Industrial Wire and Chain” is not in the result object. This instance has null as its primary_address, so no instance of Address matches.

A significant difference from SQL is that the query builder API return only instances of the primary entity type. With the query builder API, you use dot notation to access information from the joined entity type. For example:

uses gw.api.database.Query

var query = Query.make(Company)
query.join(Company#PrimaryAddress) // Join Address entity to the query by a foreign key on Company
var select = query.select()

// Fetch the data with a for loop and print it --
for (company in select) {
  print (company + ", " + company.PrimaryAddress.City)
}

The preceding Gosu code fetches the Address instance lazily from the database. If the record is not available in the cache, this code could cause the execution of a query for every company. To reduce the number of database queries, you can use a row query to retrieve the complete set of rows and the necessary columns from the primary and joined tables.

See also

• Working with row queries