Making an inner join with the foreign key on the right

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, UpdateUserID, is on the secondary table, pc_address. Because SQL considers the primary table to be on the left side, the foreign key is on the right side of the join. The query returns a result with all addresses and users who lasted updated them. The result contains all columns in the address and user tables.

SELECT * FROM pc_user
  INNER JOIN pc_address
    ON pc_user.ID = pc_address.UpdateUserID;

With the query builder APIs, you use the join method to specify an inner join. Use a property reference of the dependent entity and property name as a single parameter if the dependent entity has the foreign key. In these cases, the foreign key is on the right. The database must have a column that contains the foreign key. The data model definition of the column must specify foreignkey. You cannot use a column that is a onetoone or edgeForeignKey type because these types do not use a database column. To confirm that a foreign key uses a database field, in the Data Dictionary, check that the foreign key does not have “(virtual property)” text after its name.

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

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

var queryUser = Query.make(User) 
queryUser.join(Address#UpdateUser)

Similarly to SQL, you must specify the dependent entity to join, which in this case is Address. Unlike SQL, you do not specify the property on the primary entity, ID. The query builder APIs use metadata from the Data Dictionary to provide the missing information.

Applying predicates to the dependent entity

You seldom want to retrieve all instances of the primary type of a query. To select a subset, you often join a dependent entity and apply predicates to one or more of its properties. You apply these predicates for a secondary table with the foreign key on the right in the same way as for predicates where the foreign key is on the left.

For example, you want to select only users who have updated addresses in the city of Chicago. You must join the Address entity to your User query. Then, you can apply the predicate City = "Chicago" to Address, the dependent entity. The SQL for this query looks like the following lines.

SELECT DISTINCT pc_user.PublicID FROM pc_user
  INNER JOIN pc_address
    ON pc_user.ID = pc_address.UpdateUserID
    AND pc_address.City = 'Chicago';

The following Gosu code represents the same SQL query.

uses gw.api.database.Query
uses gw.api.database.Relop

// -- Query the User entity --
var queryUser = Query.make(User)

// -- Select only User instances who last updated addresses in the city of Chicago --
var tableAddress = queryUser.join(Address#UpdateUser)
tableAddress.compare(Address#City, Equals, "Chicago")

// -- Fetch the User instances with a for loop and print them --
var result = queryUser.select()

for (user in result) {
  print (user.DisplayName)
}