Partial comparison of any portion of a character field

Sometimes you need to make a partial comparison that matches any portion of a character field, not just from the beginning of the field. For example, you want to select an activity that is a review, but you do not know the type of review. In this case, you want to search for “Review” anywhere within the field for the subject of the activity.

In SQL, you apply partial comparison predicates with the LIKE operator in the WHERE clause. The following SQL statement applies the predicate LIKE "%Review%" to values in the Subject column of the pc_activity table. The percent sign (%) is an SQL wildcard symbol that matches zero or more characters. The query also restricts the set of activities to those that have a particular assigned user. Using the LIKE operator without another, more restrictive predicate causes the database to do a full table scan because no index is available to the query.

SELECT * FROM pc_activity
  INNER JOIN pc_user 
    ON pc_user.ID = pc_activity.AssignedUserID 
  WHERE pc_activity.Subject LIKE "%Review%"
    AND pc_user.PublicID = "pc:8";

The query result contains activities with a subject that includes “Review” and that have a particular assigned user.

The following query uses the LIKE operator in the WHERE clause and causes a full table scan because there is no other restriction on the primary table:

SELECT * FROM pc_activity
  WHERE pc_activity.Subject LIKE "%Review%";

With the query builder APIs, you apply partial comparison predicates that match any portion of character fields by using the contains method on the query object. Test the use of the contains method in a realistic environment. Using the contains method as the most restrictive predicate on a query causes a full-table scan in the database because the query cannot use an index.

The following Gosu code applies the contains predicate method to values in the Subject field on the Activity instance for which a User instance is the assigned user.

uses gw.api.database.Query

// Query the Activity instances for a particular user based on subject line. 
var queryActivity = Query.make(Activity)

// Join User as the dependent entity to the pimary entity Activity.
queryActivity.join(Activity#AssignedUser).compare(User#PublicID, Equals, "pc:105")

// Add a predicate on the primary entity.
// The value "false" means "case-senstive."
queryActivity.contains(Activity#Subject, "Review", false)

// Fetch the data with a for loop.
for (activity in queryActivity.select()) {
  print("Assigned to " + activity.AssignedUser + ": " + activity.Subject)
}

This Gosu code prints the assigned user and the subject of the activity where the subject of the activity contains “Review” and the assigned user has a particular public ID.

See also

• Case-insensitive partial comparisons of a character field