Making an inner join with the foreign key on the left

In SQL, an inner join is the default type of join. You make an inner join with the JOIN keyword. To be explicit about the type of join, use INNER JOIN. You specify the table that you need to join to the primary table of the query. You use the ON keyword to specify the columns that join the tables. By convention, you specify the join column on the primary table first. In the following SQL statement, the foreign key, PrimaryAddressID, is on the primary table, pc_contact. Because SQL considers the primary table to be on the left side, the foreign key is on the left side of the join.

SELECT * FROM pc_contact 
  INNER JOIN pc_address
    ON pc_address.ID = pc_contact.PrimaryAddressID;

With the query builder API, you use the join method to specify an inner join. Use a property reference of the primary entity and property name as the single parameter if the primary entity has the foreign key. In these cases, the foreign key is on the left. You specify a property of the primary entity that the Data Dictionary defines as a foreign key to the dependent entity. The query builder API uses metadata to determine the entity type to which the foreign key relates.

In the following Gosu code, the primary entity type, Company, has a foreign key, PrimaryAddress, which relates to the dependent entity type, Address.

var queryCompany = Query.make(Company) 
queryCompany.join(Company#PrimaryAddress)

Unlike SQL, you do not specify which dependent entity to join, in this case Address. Neither do you specify the property on the dependent entity, ID. The query builder API uses metadata from the Data Dictionary to provide the missing information.