Searching

ObjectQuel provides three approaches for matching and searching text: wildcard patterns, regular expressions, and full-text search. All three are used within where clauses and can be combined with other filter conditions. This page covers each approach from simplest to most powerful.

Wildcard Patterns

Wildcard patterns are the simplest way to match text in ObjectQuel. Place the pattern in single quotes and use the = operator — ObjectQuel detects the wildcard characters and converts the comparison into a pattern match automatically.

Two wildcard characters are available:

• * — matches any number of characters (including zero)
• ? — matches exactly one character

Prefix Matching

The most efficient wildcard pattern. When the pattern starts with a literal string, ObjectQuel can use an index to narrow results before scanning:

// Find all products whose name starts with "iPhone"
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.name = 'iPhone*'
");

Suffix Matching

Matches the end of a string. Less efficient than prefix matching because the database cannot use a standard index for trailing matches:

// Find all products whose name ends with "Pro"
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.name = '*Pro'
");

Contains Matching

Matches anywhere within a string. This always requires a full table scan — use sparingly on large tables:

// Find all products with "wireless" anywhere in the name
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.name = '*wireless*'
");

Single-Character Matching

The ? wildcard matches exactly one character. Use it when you know the structure of the string but not the exact characters:

// Match product codes like "A1B", "X9Z", etc.
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.code = '???'
");

Combining Wildcards

Mix * and ? in a single pattern for more precise matching:

// Match SKUs like "ABC-anything-3chars-anything-XYZ"
// e.g., "ABC-PRO-001-BLUE-XYZ" or "ABC--X7Q-TEST-XYZ"
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.sku = 'ABC*-???-*XYZ'
");

Negating Wildcard Patterns

Use != to find rows that do not match a pattern:

// Find all products whose name does NOT start with "Test"
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.name != 'Test*'
");

Wildcard Performance

Regular Expressions

When wildcards aren't expressive enough, ObjectQuel supports regular expressions. Enclose the pattern in forward slashes instead of quotes:

// Match company email addresses
$results = $entityManager->executeQuery("
    range of u is App\\Entity\\UserEntity
    retrieve (u) where u.email = /^[a-zA-Z0-9._%+-]+@company\.com$/
");

ObjectQuel uses the regex syntax supported by your underlying database engine.

Case Sensitivity

How case sensitivity works depends on the MySQL version ObjectQuel detects at runtime:

• MySQL 8.0 and higher — ObjectQuel uses REGEXP_LIKE(), which supports flags after the closing slash. Append i for a case-insensitive match (e.g. /pattern/i), or c to force case-sensitive matching regardless of the column's collation (e.g. /pattern/c).
• MySQL 5.x and lower — ObjectQuel falls back to the REGEXP operator, which does not support inline flags. Case sensitivity is then determined entirely by the collation of the column being matched: columns with a case-insensitive collation (e.g. utf8mb4_unicode_ci) match case-insensitively, and columns with a case-sensitive or binary collation match case-sensitively.

When running MySQL 8.0+, flags after the closing slash are the clearest way to express intent and override the column's collation without schema changes:

// Case-insensitive match regardless of column collation (MySQL 8.0+)
$results = $entityManager->executeQuery("
    range of u is App\\Entity\\UserEntity
    retrieve (u) where u.email = /^[a-z0-9._%+-]+@company\.com$/i
");

// Case-sensitive match regardless of column collation (MySQL 8.0+)
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.code = /^[A-Z]{3}-[0-9]+$/c
");

On MySQL 5.x, where inline flags are unavailable, control case sensitivity by adjusting the column's collation in your schema.

Common Regex Patterns

// Starts with a digit
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.sku = /^[0-9]/
");

// Contains exactly 3 consecutive uppercase letters
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.code = /[A-Z]{3}/
");

// Matches a phone number format: +31-6-12345678
$results = $entityManager->executeQuery("
    range of u is App\\Entity\\UserEntity
    retrieve (u) where u.phone = /^\+[0-9]{1,3}-[0-9]+-[0-9]+$/
");

Full-Text Search

For natural language queries — where you want to find rows containing certain words regardless of their position — use the search() function. When a full-text index is available, ObjectQuel uses it for fast, relevance-ranked results. Without one, it falls back to a series of LIKE statements that produce the same results but scan the entire table.

Single-Field Search

// Find products with "bluetooth" in the description
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where search(p.description, 'bluetooth speaker')
");

Multi-Field Search

Pass multiple fields to search across several columns at once:

// Search both name and description
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where search(p.name, p.description, 'premium quality')
");

Boolean Operators

Control which terms are required, excluded, or optional using boolean prefixes in the search string:

// Must include bluetooth, must not include expensive,
// wireless is optional but boosts ranking
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where search(p.description, 'wireless +bluetooth -expensive')
");

Combining Search with Other Filters

The search() function is a regular where-clause condition and can be combined with any other filter:

// Full-text search combined with price range and category filter
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    range of c is App\\Entity\\CategoryEntity via p.category
    retrieve (p, c.name)
    where search(p.name, p.description, '+wireless +bluetooth')
      and p.price between :min and :max
      and c.name = 'Electronics'
    sort by p.price asc
", [
    'min' => 25.00,
    'max' => 200.00
]);

Searching Across Related Entities

You can apply search() to fields on related entities, just as you would any other where-clause condition:

// Find orders where the product description mentions "organic"
$results = $entityManager->executeQuery("
    range of o is App\\Entity\\OrderEntity
    range of i is App\\Entity\\OrderItemEntity via o.items
    range of p is App\\Entity\\ProductEntity via i.product
    retrieve (o, p.name)
    where search(p.description, '+organic')
      and o.status = 'completed'
");

Quick Reference