Full-Text Search

Overview

Full-text search enables finding records by partial text matches using text search functions. Core APIs provide contains(), startwith(), and endwith() functions for flexible text matching.

Search Types:

Starts with: startwith(field, 'text')
Ends with: endwith(field, 'text')
Contains: contains(field, 'text')
Exact match: eq 'text'

Text Search Functions

startwith() Function

Starts with "Widget":

GET /api/v4/core/PRD?source=productList&\
  $filter=startwith(XPRD01,'Widget')

Generated SQL:

WHERE XPRD01 LIKE 'Widget%'

Result: "Widget Pro", "Widget Basic", "Widget Deluxe"

contains() Function

Products containing "Pro":

GET /api/v4/core/PRD?source=productList&\
  $filter=contains(XPRD01,'Pro')

Generated SQL:

WHERE XPRD01 LIKE '%Pro%'

Result: "Widget Pro", "Professional Kit", "Pro Series"

endwith() Function

Products ending with "XL":

GET /api/v4/core/PRD?source=productList&\
  $filter=endwith(XPRD01,'XL')

Generated SQL:

WHERE XPRD01 LIKE '%XL'

Result: "T-Shirt XL", "Box XL"

Case Sensitivity

Case-Insensitive Search

Text search functions are case-insensitive by default:

$filter=contains(XPRD01,'widget')

Matches: "Widget", "WIDGET", "widget", "WiDgEt"

Database-Specific:

MySQL: Case-insensitive by default (depends on collation)
Oracle: Case-sensitive by default

For case-sensitive requirements, contact Q01 platform team for custom function configuration.

Performance Considerations

Index Usage

Fast - Starts with (prefix search):

# ✅ Can use index
$filter=startwith(XPRD01,'Widget')

Slow - Contains or ends with:

# ⚠️ Cannot use index (full table scan)
$filter=contains(XPRD01,'Widget')
$filter=endwith(XPRD01,'Widget')

Why? Indexes are ordered from left to right. Leading wildcard prevents index usage.

Full-Text Index

For large-text search on >100k records, consider:

Database full-text indexes (MySQL FULLTEXT, Oracle TEXT)
External search engines (Elasticsearch, Solr)
Dedicated search microservice

Multi-Field Search

Search Across Multiple Fields

Search in name OR description:

GET /api/v4/core/PRD?source=productList&\
  $filter=(contains(XPRD01,'widget') OR contains(XPRD04,'widget'))

Generated SQL:

WHERE (contains(XPRD01,'widget') OR contains(XPRD04,'widget'))

Common Search Patterns

Pattern 1: Autocomplete (Prefix Search)

Fast, index-friendly:

GET /api/v4/core/PRD?source=productAutocomplete&\
  $filter=startwith(XPRD01,'Wid')&\
  $select=PRD_ID,XPRD01&\
  $num_rows=10&\
  $order=XPRD01 ASC

Use: Autocomplete dropdowns, type-ahead search.

Pattern 2: Global Search (Contains)

Slower, more flexible:

GET /api/v4/core/PRD?source=productSearch&\
  $filter=contains(XPRD01,'widget')&\
  $num_rows=25

Use: Full-text search box, "Search anywhere" feature.

Pattern 3: Combined Filters

Search + additional filters:

GET /api/v4/core/PRD?source=productSearch&\
  $filter=contains(XPRD01,'Pro') AND XPRD05 eq 'Electronics' AND XPRD06 eq true&\
  $order=XPRD02 DESC

Use: Filtered search results (search within category).

Pattern 4: Multiple Terms (OR)

Search for multiple keywords:

GET /api/v4/core/PRD?source=productSearch&\
  $filter=(contains(XPRD01,'Widget') OR contains(XPRD01,'Gadget') OR contains(XPRD01,'Tool'))

Use: Multi-keyword search.

JavaScript Implementation

Search Component

class ProductSearch {
  constructor(apiBase, token) {
    this.apiBase = apiBase;
    this.token = token;
    this.debounceTimer = null;
  }

  /**
   * Autocomplete search (prefix match)
   * Fast, uses index
   */
  async autocomplete(prefix, limit = 10) {
    const url = new URL(`${this.apiBase}/api/v4/core/PRD`);
    url.searchParams.append('source', 'productAutocomplete');
    url.searchParams.append('$filter', `startwith(XPRD01,'${prefix}')`);
    url.searchParams.append('$select', 'PRD_ID,XPRD01,XPRD02');
    url.searchParams.append('$num_rows', limit);
    url.searchParams.append('$order', 'XPRD01 ASC');

    const response = await fetch(url, {
      headers: { 'Authorization': `Bearer ${this.token}` }
    });

    return response.json();
  }

  /**
   * Global search (contains match)
   * Slower, more flexible
   */
  async globalSearch(searchTerm, options = {}) {
    const url = new URL(`${this.apiBase}/api/v4/core/PRD`);
    url.searchParams.append('source', options.source || 'productSearch');

    // Build multi-field search
    const filters = [];
    filters.push(`contains(XPRD01,'${searchTerm}')`); // Name
    if (options.searchDescription) {
      filters.push(`contains(XPRD04,'${searchTerm}')`); // Description
    }

    url.searchParams.append('$filter', `(${filters.join(' OR ')})`);

    // Add additional filters
    if (options.category) {
      const currentFilter = url.searchParams.get('$filter');
      url.searchParams.set('$filter', `${currentFilter} AND XPRD05 eq '${options.category}'`);
    }

    // Pagination
    url.searchParams.append('$num_rows', options.limit || 25);
    url.searchParams.append('$offset', options.offset || 0);

    // Sorting
    url.searchParams.append('$order', options.$order || 'XPRD01 ASC');

    const response = await fetch(url, {
      headers: { 'Authorization': `Bearer ${this.token}` }
    });

    return response.json();
  }

  /**
   * Debounced search for live typing
   */
  debouncedSearch(searchTerm, callback, delay = 300) {
    clearTimeout(this.debounceTimer);
    this.debounceTimer = setTimeout(async () => {
      const results = await this.autocomplete(searchTerm);
      callback(results);
    }, delay);
  }
}

// Usage
const search = new ProductSearch(apiBase, token);

// Autocomplete
const suggestions = await search.autocomplete('Wid', 10);
console.log(suggestions.data); // ["Widget Pro", "Widget Basic", ...]

// Global search
const results = await search.globalSearch('widget', {
  searchDescription: true,
  category: 'Electronics',
  limit: 25
});
console.log(results.data);

React Search Component

function ProductSearchBox() {
  const [searchTerm, setSearchTerm] = useState('');
  const [results, setResults] = useState([]);
  const [loading, setLoading] = useState(false);
  const searchService = new ProductSearch(apiBase, token);

  function handleSearchChange(e) {
    const term = e.target.value;
    setSearchTerm(term);

    if (term.length >= 2) {
      setLoading(true);
      searchService.debouncedSearch(term, (response) => {
        setResults(response.data);
        setLoading(false);
      });
    } else {
      setResults([]);
    }
  }

  return (
    <div className="search-box">
      <input
        type="text"
        placeholder="Search products..."
        value={searchTerm}
        onChange={handleSearchChange}
      />
      {loading && <div className="spinner">Loading...</div>}
      {results.length > 0 && (
        <ul className="autocomplete-results">
          {results.map(product => (
            <li key={product.PRD_ID}>
              <a href={`/products/${product.PRD_ID}`}>
                {product.XPRD01} - ${product.XPRD02}
              </a>
            </li>
          ))}
        </ul>
      )}
    </div>
  );
}

Search Optimization Strategies

Strategy 1: Prefix Search for Autocomplete

Use prefix search (LIKE 'text%') for autocomplete:

Index-friendly
Fast response (<100ms)
Good user experience

// ✅ Good - prefix search
$filter=startwith(XPRD01,'Wid')

Strategy 2: Full-Text Index for Global Search

For global search on large datasets:

Add database full-text index
Use dedicated search column
Consider external search engine

-- MySQL Full-Text Index
ALTER TABLE TB_ANAG_PRD00 ADD FULLTEXT INDEX ft_product_search (XPRD01, XPRD04);

Strategy 3: Field Projection

Reduce response size in search results:

# ✅ Good - only needed fields
$select=PRD_ID,XPRD01,XPRD02,XPRD12

# ❌ Bad - returns all fields
# No $select parameter

Strategy 4: Debouncing

Prevent excessive API calls during typing:

// ✅ Good - debounce search (300ms delay)
debouncedSearch(searchTerm, callback, 300);

// ❌ Bad - search on every keystroke
onChange={(e) => search(e.target.value)}

SQL Injection Protection

Core APIs Sanitize Input

Core APIs automatically escape LIKE patterns, but always validate input on client side:

function sanitizeSearchTerm(term) {
  // Remove special characters that could break SQL
  return term.replace(/['";\\]/g, '');
}

async function search(userInput) {
  const safeTerm = sanitizeSearchTerm(userInput);
  const url = new URL(`${apiBase}/api/v4/core/PRD`);
  url.searchParams.append('$filter', `contains(XPRD01,'${safeTerm}')`);
  // ...
}

Note: Core APIs use parameterized queries internally, preventing SQL injection.

Best Practices

✅ DO:

Use prefix search for autocomplete:

# ✅ Fast - uses index
$filter=startwith(XPRD01,'Widget')

Debounce live search:

// ✅ Good - 300ms debounce
debouncedSearch(searchTerm, callback, 300);

Limit autocomplete results:

# ✅ Good - small result set
$num_rows=10

Use field projection:

# ✅ Good - minimal fields
$select=PRD_ID,XPRD01,XPRD02

❌ DON'T:

Don't use contains search on large tables without index:

# ❌ Slow - full table scan on 100k+ records
$filter=contains(XPRD01,'widget')

Don't search on every keystroke:

// ❌ Bad - excessive API calls
onChange={(e) => search(e.target.value)}

Don't forget pagination:

# ❌ Bad - returns unlimited results
$filter=contains(XPRD01,'widget')

# ✅ Good
$filter=contains(XPRD01,'widget')&$num_rows=25

Summary

✅ Use text search functions with % wildcard for text search
✅ Prefix search (LIKE 'text%') is fast and index-friendly
⚠️ Contains search (LIKE '%text%') is slow without full-text index
✅ Debounce live search (300ms delay)
✅ Limit autocomplete results ($num_rows=10)
✅ Use field projection to reduce response size
✅ Consider full-text indexes for large datasets

Key Takeaways:

Prefix search for autocomplete (index-friendly)
Full-text index for global search (large datasets)
Debounce to prevent excessive API calls
Always paginate search results

Next: Query Performance →

Complex Filters - Combining LIKE with AND/OR
Field Projection - Reducing response size
Query Performance - Index optimization

Full-Text Search

Overview​

Text Search Functions​

startwith() Function​

contains() Function​

endwith() Function​

Case Sensitivity​

Case-Insensitive Search​

Performance Considerations​

Index Usage​

Full-Text Index​

Multi-Field Search​

Search Across Multiple Fields​

Common Search Patterns​

Pattern 1: Autocomplete (Prefix Search)​

Pattern 2: Global Search (Contains)​

Pattern 3: Combined Filters​

Pattern 4: Multiple Terms (OR)​

JavaScript Implementation​

Search Component​

React Search Component​

Search Optimization Strategies​

Strategy 1: Prefix Search for Autocomplete​

Strategy 2: Full-Text Index for Global Search​

Strategy 3: Field Projection​

Strategy 4: Debouncing​

SQL Injection Protection​

Core APIs Sanitize Input​

Best Practices​

✅ DO:​

❌ DON'T:​

Summary​

Related Concepts​

Overview

Text Search Functions

startwith() Function

contains() Function

endwith() Function

Case Sensitivity

Case-Insensitive Search

Performance Considerations

Index Usage

Full-Text Index

Multi-Field Search

Search Across Multiple Fields

Common Search Patterns

Pattern 1: Autocomplete (Prefix Search)

Pattern 2: Global Search (Contains)

Pattern 3: Combined Filters

Pattern 4: Multiple Terms (OR)

JavaScript Implementation

Search Component

React Search Component

Search Optimization Strategies

Strategy 1: Prefix Search for Autocomplete

Strategy 2: Full-Text Index for Global Search

Strategy 3: Field Projection

Strategy 4: Debouncing

SQL Injection Protection

Core APIs Sanitize Input

Best Practices

✅ DO:

❌ DON'T:

Summary

Related Concepts