Joining to a subtype of an entity type

Many PolicyCenter entity types have subtypes. The parent entity type and its subtypes all share the same database table. For example, the Contact entity type, its direct subtypes, Company, Person, and Place, and its indirect subtypes all have rows in the pc_contact database table. If you create a query that accesses a subtype as the primary entity, the query builder API adds appropriate filters to the query to return only rows of that subtype. You use the same syntax to access properties on the parent type and properties that are specific to the subtype. If you create a query that joins a subtype as a dependent entity, the query builder API joins the least restrictive entity type to the query.

For example, the following lines join organizations to contacts:

var queryOrg = Query.make(Organization)
var tableContact = queryOrg.outerJoin(Organization#Contact)

Because the Contact property exists on the Organization entity type, the following code also joins organizations to contacts and does not join only company records:

var queryOrg = Query.make(Organization)
var tableContact = queryOrg.outerJoin(Company#Contact)

If you want to access company properties or only need to see organizations that have a company as a contact, you must cast the joined table to Company. Use code like the following lines:

var queryOrg = Query.make(Organization)
var tableContact = queryOrg.join(Organization#Contact).cast(Company)

If the column that you use to join the dependent table is specific to a particular subtype of an entity type, the query builder API performs the more restrictive join. In this case, you do not need to cast the type of the joined table.

Restricting a result set by joining to a subtype of an entity type

You can restrict the result set of a query by requiring a specific subtype for the joined entity. You use the entity name and a property to specify the join. The query builder API generates an SQL query to join to the database table. If the property for the join exists on the subtype entity and not on the more general entity type, the generated SQL query adds a restriction for the subtype. If the property for the join exists on the more general entity type, the generated SQL query does not add a restriction for the subtype. To restrict the rows in the result set to those matching the subtype of the joined table, you must use the cast method.

For example, the following code prints the public IDs of policies that have ever been renewed:

uses gw.api.database.Query

var queryPolicy = Query.make(Policy).withDistinct(true)

// Join to any type of Job
// queryPolicy.join(Renewal#Policy)         // This join does not select only Renewal type jobs

// Join to only Renewal jobs
queryPolicy.join(Job#Policy).cast(Renewal)  // This join does select only Renewal type jobs
var result = queryPolicy.select()

for (policy in result) {
  print(policy.PublicID)
}

Accessing properties of a joined subtype of an entity type

If you need to access the properties of a specific subtype of an entity type, you must ensure that the query returns only rows for that subtype. If the subtype is the primary entity, you make a query by specifying the subtype name. If the subtype is a joined entity and the join uses a property on the parent entity type, you must cast the table to the required subtype. Additionally, if you need to access a property on the joined entity subtype, you must cast the property on the primary entity to the specific subtype.

For example, the following code prints information about activities for submission jobs and submission information about those jobs.

uses gw.api.database.Query

var queryActivity = Query.make(Activity)

// Join to only Submission jobs
var tableJob = queryActivity.join(Activity#Job).cast(Submission)

var result = queryActivity.select()

for (a in result) {
  // Use a variable to access properties on the Submission entity type
  var sj = a.Job as Submission
  print(a + " " + a.UpdateUser + " " + a.PolicyPeriod.PolicyNumber + " " + sj.BindOption)
}