Restricting query results by using fields on joined entities

You seldom want to retrieve all instances of the primary type of a query. In many cases, you join a dependent entity to a query so you can restrict the results by applying predicates to one or more of its properties. To apply predicates to the dependent entity in a join, you can save the Table object that the join method returns in a local variable. Then, you can use predicate methods on the Table object, such as compare, to restrict which dependent instances to include in the result. That restriction also restricts the primary entity instances that the result returns.

Applying predicates to the dependent entity of an inner join

For example, you want a query that returns all companies in the city of Chicago. The SQL statement for this query looks like:

SELECT * FROM pc_contact 
  JOIN pc_address
    ON pc_address.ID = pc_contact.PrimaryAddressID
  WHERE pc_contact.Subtype IN (1,8) 
    AND pc_address.City = 'Chicago';

Using the query API, the primary entity type of your query is Company, because you want instances of that type in your result.

var queryCompany = Query.make(Company)

The Company entity does not have a City property. That property is on the Address entity. You need to join the Address entity to your query. You must capture the object reference that the join method returns so that you can specify predicates on values in Address.

var tableAddress = queryCompany.join(Company#PrimaryAddress)
tableAddress.compare(Address#City, Equals, "Chicago")

Alternatively, you can chain the join method and call the predicate method on the returned Table object in a single statement, as the following Gosu code shows. If you use this pattern, you do not need to make a variable for the Table object.

queryCompany.join(Company#PrimaryAddress).compare(Address#City, Equals, "Chicago")

When you run the query, the result contains companies that have Chicago as the city on their primary addresses. The following code demonstrates the use of this predicate on a joined entity.

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

// Start a new query with Company as the primary entity.
var queryCompany = Query.make(Company)

// Join Address as the dependent entity to the pimary entity Company.
var tableAddress = queryCompany.join(Company#PrimaryAddress)

// Add a predicate on the dependent entity.
tableAddress.compare(Address#City, Equals, "Chicago")

// Fetch the data with a for loop. 
var result = queryCompany.select()
for (company in result) {
  print (company + ", " + company.PrimaryAddress.City)
}

Running this code produces a list like the following one:

Jones and West Insurance, Chicago
Shumway Contracting, Chicago
New Energy Corp., Chicago
...
Auto Claims of the West, Chicago
Auto Claims Defenders, Chicago

The query result of the previous example contains only Company instances. Even though the code includes the Address entity in the query, the results do not include information from joined entities. The code example uses dot notation to access information from related Address instances after retrieving Company instances from the result. By joining Address to the query and applying the predicate, the code ensures that Company instances in the result have only “Chicago” in the property company.PrimaryAddress.City.

Predicates on the left outer join dependent entity differ from SQL

With the query builder APIs, generally you join a dependent entity to a query only so you can restrict the results with predicates on the dependent entity. Using the compare method to apply a predicate to a dependent entity that you join with a left outer join has no effect on the rows that Gosu retrieves.

The ways that SQL queries and Gosu code filter the dependent table rows differ. Gosu fetches the dependent entity only if necessary, if code that accesses the dependent entity executes. Gosu applies the filter when running the query on the primary table, not when fetching the dependent table row. The value of a dependent entity property that you access by using dot notation is null only if there is no matching entity instance regardless of the filter. To retrieve filtered values from related entities, use a row query.

For example, the following SQL filters the names of group supervisors. If the name does not match, the value for the supervisor user and contact columns are null.

SELECT * FROM groups
  LEFT OUTER JOIN users 
    ON groups.supervisor = users.ID
  LEFT OUTER JOIN contacts 
    ON users.contactID = contacts.ID
    AND contacts.lastName = 'Visor';

The following Gosu code shows the effect of dot notation to access information related to the primary entity of an outer-join query. The code accesses the DisplayName information for the group supervisor from the User entity with dot notation on Group instances retrieved from the result. The output shows the supervisor name for every group because Gosu applies the filter on the primary table query, not when print retrieves the property values for related entities.

uses gw.api.database.Query

// -- Query the Group entity --
var queryGroup = Query.make(Group) 
// -- Supervisor is a foreign key from Group to User.
var userTable = queryGroup.outerJoin(Group#Supervisor) 
// -- Contact is a foreign key from User to UserContact.
var contactTable = userTable.outerJoin(User#Contact)
// -- Apply a filter to the outer joined entity
contactTable.compare(UserContact#LastName, Relop.Equals, "Visor")
// -- Fetch the Group instances with a for loop and print them --
var result = queryGroup.select()

for (group in result) {
  if (group.Supervisor != null) {
    // Every supervisor’s name appears because the outer join does not filter the groups
    print(group.Name + ": " + group.Supervisor.Contact.DisplayName)
  } else { // Provide user-friendly text if the group has no supervisor
    // This text appears only for groups that have no supervisor
    print (group.Name + ": " + "This group has no supervisor")
  }
}