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.

Regex Flags

Append flags after the closing slash to modify matching behavior:

// Case-insensitive: matches "Smartphone", "SMARTPHONE", "smartphone", etc.
$results = $entityManager->executeQuery("
    range of p is App\\Entity\\ProductEntity
    retrieve (p) where p.description = /smartphone/i
");

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. This leverages database full-text indexing for fast, relevance-ranked results.

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