SAP COMMERCE FLEXIBLESEARCH QUICK REFERENCE
============================================

BASIC STRUCTURE
  SELECT {p.pk}                           Always include pk — SELECT * doesn't work
  FROM {Product! AS p}                    ! = include subtypes (usually what you want)
  WHERE {p.code} = 'MY_CODE'
  ORDER BY {p.code} ASC
  LIMIT 100

TYPE NOTATION
  {Product}         Exact type only — excludes subtypes
  {Product!}        Type + all subtypes — use this in most cases
  {Product AS p}    Alias — required for multi-table queries

JOINS
  JOIN {CatalogVersion AS cv} ON {p.catalogVersion} = {cv.pk}
  LEFT JOIN {type} ON ...     -- preserves rows with no match

LOCALIZED FIELDS
  {p.name:i}        Inner join on localized table (null if no translation)
  {p.name:o}        Left outer join (null-safe — rows returned even if no translation)
  WHERE {p.name:o} LIKE '%search%'

CATALOG VERSION FILTER (standard pattern)
  WHERE {p.catalogVersion} IN (
    {{SELECT {cv.pk} FROM {CatalogVersion AS cv}
      WHERE {cv.version} = 'Staged'
      AND {cv.catalog} IN (
        {{SELECT {c.pk} FROM {Catalog AS c} WHERE {c.id} = 'myCatalog'}}
      )
    }}
  )

ENUM FILTER (enums need a JOIN)
  Wrong:  WHERE {p.approvalStatus} = 'approved'    -- silent wrong results
  Correct:
    JOIN {ArticleApprovalStatus AS s} ON {p.approvalStatus} = {s.pk}
    WHERE {s.code} = 'approved'

UNION SYNTAX (double braces required)
  {{ SELECT {p.pk} FROM {Product AS p} WHERE ... }}
  UNION
  {{ SELECT {v.pk} FROM {VariantProduct AS v} WHERE ... }}

SUBQUERIES
  WHERE {p.pk} IN ({{ SELECT {rel.target} FROM {CategoryProductRelation AS rel}
                      WHERE {rel.source} IN ({{ ... }}) }})

PAGINATION
  LIMIT 100 OFFSET 0       First page
  LIMIT 100 OFFSET 100     Second page
  High limits (>1000) risk OutOfMemoryError in HAC

COMMON PITFALLS
  - SELECT * does not work — must select {pk} minimum
  - {Type} vs {Type!} — subtypes excluded without !
  - Enum equality filtering returns incorrect results — always JOIN to code
  - Collection/Map attributes cannot be used in WHERE — use Groovy for those
  - LIKE requires % wildcards: {p.code} LIKE 'TEST%' not LIKE 'TEST'
  - Case sensitive by default — use LOWER({p.code}) for case-insensitive
  - Dates: stored as Long internally, compare with dateformat helper
  - NULL checks: {p.description} IS NULL or IS NOT NULL