Making an inner join without regard to foreign keys

In SQL, you can make an inner join between two tables regardless of any formal SQL declaration of a foreign key relation between the tables. The columns on which a SQL Select statement joins two tables must have values that match, so the data types of both columns must be the same. In addition, application logic, not a declared foreign key constraint, must ensure that both columns contain values that potentially match.

To use the query builder API to create an inner join that does not use a foreign key, the database must have columns that contain each join property. The data model definition of the column must specify foreignkey or column. You cannot use a virtual column, nor a column that is an array, 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.

Test the use of an inner join that you make without regard to foreign keys in a realistic environment. Using this technique on a query can cause a full-table scan in the database if the query cannot use an index.

Warning: For a query on large tables, using an inner join without regard to foreign keys can cause an unacceptable delay to the user interface.

Comparing SQL syntax to query builder syntax

For an inner join in SQL, the ON clause specifies the columns that join the tables. The clause requires the names of both tables and both columns. The following SQL statement joins rows from the addresses table to rows in the companies table where values in ID match values in primary_address.

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

With the query builder API, you use the join method with the following signature to make inner joins of entities without regard to foreign keys in the data model.

join("columnPrimary", table, "columnDependent")

The first parameter is the name of a column on the primary entity of the query. The second and third parameters are the names of the dependent entity and the column that has matching values.

Example

In the following Gosu code, the primary entity Note has no declared foreign key relation with the dependent entity Contact. An indirect relation exists through their mutual foreign key relations with the User entity. Both entities in the query have a column that contains the user ID, so the join method can join the Contact entity to the query of the Note entity.

uses gw.api.database.Query

// -- query the Note entity --
var queryNote = Query.make(Note).withDistinct(true)

// -- select only notes where the same user created a contact.
queryNote.join("CreateUser", Contact, "CreateUser")

// -- fetch the Note instances with a for loop and print them --
var result = queryNote.select()

for (note in result) {
  print ("The user who created the note '" + note.Subject + "' also created a contact")
}

The Note entity has no direct relevant foreign key relation to Contact. You cannot use Gosu dot notation to navigate from notes in the result to properties on the contact.