Comparing column values with each other

Some predicate methods have signatures that let you compare a column value on an entity instance with another column value. These methods use a property reference to access the first column and a column reference to access the second column. Use the getColumnRef method on query, table, and restriction objects to obtain a column reference.

Note: The ColumnRef object that the getColumnRef method returns does not implement the IQueryBuilder interface. You cannot chain the getColumnRef method with other query builder methods.

For example, you need to query the database for contacts where the primary and secondary email addresses differ. The following SQL statement compares the two email address columns on a contact row with each other.

SELECT * FROM pc_contact
  WHERE EmailAddress1 <> EmailAddress2;

The following Gosu code constructs and executes a functionally equivalent query to the preceding SQL statement. This Gosu code uses the getColumnRef method to retrieve the second email address property.

uses gw.api.database.Query

var query = Query.make(Contact) // Query for contacts where email 1 and email 2 do not match.
query.compare(Contact#EmailAddress1, NotEquals, query.getColumnRef("EmailAddress2"))

var result = query.select()

for (contact in result) { // Execute the query and iterate the results
  print("public ID '" + contact.PublicID + " with name " + contact.DisplayName 
          + " and emails " + contact.EmailAddress1 + "," + contact.EmailAddress2)
}