Making a query with a left outer join

For a left outer join, items from the primary entity on the left are joined to items from the entity on the right, based on matching values. If a primary item on the left has no matching value on the right, that primary item is included in the result, anyway. A foreign key from one side or the other is the basis of matching.

In SQL, it generally does not matter which side of the join the foreign key is on. It generally does not matter whether the foreign key is defined in metadata. It does not matter if the column or property names are the same. All that matters is for one side of the join to have a column or property with values that match values in the column of another table or property.

    Making a left outer join with the foreign key on the left

    In SQL, you make a left outer join with the LEFT OUTER JOIN keywords. You specify the table that you want to join to the primary table of the query. The ON keyword lets you specify which columns join the tables. In the following SQL statement, the foreign key, supervisor, is on the primary table, groups. So, the foreign key is on the left side. 

    SELECT * FROM groups
  LEFT OUTER JOIN users 
    ON groups.supervisor = users.ID;

    With the query builder APIs, use the outerJoin method with a 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 APIs use metadata to determine the entity type to which the foreign key the relates.

    Joining to a dependent entity with the foreign key on the left

    In the following Gosu code, the primary entity type, Group, has a foreign key, Supervisor, which relates to the dependent entity type, User. 

    var queryGroup = Query.make(Group) 
queryGroup.outerJoin(Group#Supervisor) // Supervisor is a foreign key from Group to User.

    Notice that unlike SQL, you do not specify which dependent entity to join, in this case User. Neither do you specify the property on the dependent entity, ID. The query builder APIs use metadata from the Data Dictionary to fill in the missing information.