Restricting query results with fields on primary and joined entities

You can restrict the result set of a query by applying predicates to both the primary and joined entities. You use different techniques to add the predicates depending on whether you want both (AND) or either (OR) to apply.

Using AND to combine predicates on primary and joined entities

If you need both predicates to apply to the result set, you can use a predicate method on the primary query and another predicate method on the secondary table.

For example, you want a query that returns all companies having a name that matches “Stewart Media” 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_contact.Name = 'Stewart Media'
    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. You add the predicate that specifies the restriction on the Name property.

var queryCompany = Query.make(Company).compare(Company#Name, Equals, "Stewart Media")

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")

When you run the query, the result contains companies that have Chicago as the city on their primary addresses. 

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

// Start a new query with Company as the primary entity and restrict the result by company name.
var queryCompany = Query.make(Company).compare(Company#Name, Equals, "Stewart Media")

// 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:

Stewart Media, Chicago
You can add predicates to the primary entity of a query before or after you join a new entity to the query. For example, the following code adds a predicate to the primary entity before joining the secondary entity:
uses gw.api.database.Query
uses gw.api.database.Relop

// -- Query the Contact entity --
var queryContact = Query.make(Contact)
// -- Select only contacts that have a primary phone number that is a work phone
queryContact.compare(Contact#PrimaryPhone, Equals, typekey.PrimaryPhoneType.TC_WORK)
// -- Select only Contact instances that have a primary address in the city of Chicago --
queryContact.join(Contact#PrimaryAddress).compare(Address#City, Equals, "Chicago")
The following code adds a predicate to the primary entity after joining the secondary entity, and is equivalent to the previous code example:
uses gw.api.database.Query
uses gw.api.database.Relop

// -- Query the Contact entity --
var queryContact = Query.make(Contact)

// -- Add a predicate on the secondary entity
// -- Select only Contact instances that have a primary address in the city of Chicago --
var tableAddress = queryContact.join(Contact#PrimaryAddress)
tableAddress.compare(Address#City, Equals, "Chicago")

// -- Add a predicate on the primary entity
// -- Select only contacts that have a primary phone number that is a work phone
queryContact.compare(Contact#PrimaryPhone, Equals, typekey.PrimaryPhoneType.TC_WORK)

Using OR to combine predicates on primary and joined entities

If you need either predicate to apply to the result set, you use an or method on the secondary table. To refer to a column on the secondary table in the or block, you can use a property reference. To refer to a column on the primary table in the or block, you must use a column reference. A property on the primary table is not available in the context of the secondary table.

For example, you want a query that returns all companies either having a name that matches “Armstrong Cleaners” or 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_contact.Name = 'Armstrong Cleaners'
     OR 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. You use an or block to add the predicate that specifies the restriction on the Name property in the Company entity. You must use a column reference to access the Name column because that column is not in the Address entity.

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

When you run the query, the result contains companies that have Chicago as the city on their primary addresses. 

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 both predicates on the dependent entity.
tableAddress.or( \ or1 -> {
  or1.compare(Address#City, Equals, "Chicago")
  or1.compare(queryCompany.getColumnRef("Name"), Equals, "Armstrong Cleaners")
})

// 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:

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

See also