Develop > Controller layer > Application developer > Catalog subsystem > Catalog search methods
Catalog search limitations
If you choose to implement catalog search functionality for the site, you should be aware of the limitations explained in this section.
General Catalog search limitations
- Case-insensitive searches are not permitted on the CATENTDESC long description column. This is because of potential performance degradation when using the upper() function on the columns CLOB data type.
- The CATENTDESC long description column is not intended for searching. To facilitate searching, use the short description and keyword columns. The data type of these columns is VARCHAR(254), which allows for better search performance than CLOB.
Search for inventory quantity in a non-ATP Extended Site store
In an Extended Site setup where the same catalog asset store is shared by multiple extended site stores, the catalog search performed in non-ATP stores returns incorrect search results. This behavior occurs when the search criteria includes an inventory quantity. The criteria is enabled by setting "qtyAvailable" and "qtyAvailableOperator" in CatEntrySearchListDataBean or other beans which extend CatEntrySearchListDataBean. For example, suppose you perform a search on 'qtyAvailable > 10', the search results will include some catalog entries with quantity <= 10.
This error occurs because there is no support for inventory in the default extended site model. Each extended site store has to define its own inventory for all the catalog entries it displays including the entries which belong to the catalog asset store. Hence, if a catalog entry X, which belongs to catalog asset store, has a different inventory for each extended site store, it will have a corresponding record in STOREINV view for each of the extended site store. When the search engine generates the search query, it does not include a filter condition on the STOREINV.STORE_ID column. As a result, all the records where quantity > 10 is included in the search result because the STORE_ID column is ignored.
For example, if the search criteria includes a filter on an inventory in a non-ATP store, the search uses the STOREINV view and retrieves those catalog entries present in the view using the following default query (see the filter condition highlighted in bold) :
select distinct CATENTRY.CATENTRY_ID, CATENTDESC.NAME from CATENTRY, CATENTDESC, CATGPENREL, STOREINV where ((((((((((upper(CATENTDESC.NAME) like '%INFOPRINT%')) or ((upper(CATENTDESC.SHORTDESCRIPTION) like '%INFOPRINT%'))) and CATGPENREL.CATALOG_ID = 10201) and STOREINV.STOREQUANTITY > 12))) and (((CATENTRY.BUYABLE = 1 and CATENTRY.MARKFORDELETE <> 1 and CATENTDESC.PUBLISHED = 1 and CATENTDESC.LANGUAGE_ID = -1))) and ((CATENTRY.CATENTTYPE_ID = 'ProductBean') or (CATENTRY.CATENTTYPE_ID = 'PackageBean') or (CATENTRY.CATENTTYPE_ID = 'BundleBean')))) and (CATENTRY.CATENTRY_ID = (CATGPENREL.CATENTRY_ID) and CATENTRY.CATENTRY_ID = (CATENTDESC.CATENTRY_ID) and STOREINV.CATENTRY_ID = (CATENTRY.CATENTRY_ID))) order by CATENTDESC.NAME asc
The preceding query may not work properly in an extended site setup where multiple extended site stores extend the same catalog asset store because inventory is not supported. Hence, each extended site store should have it's own inventory for a given catalog entry so that the same catalog entry can have different quantity for different extended site stores in the STOREINV view. Also, there is a filter on STOREINV.STOREQUANTITY and a join on STOREINV.CATENTRY_ID. Suppose an extended site store A has zero inventory and an extended site store B has 10 inventory for a CatalogEntry X, if you perform a search in extended site store A on inventory > 0, you still see CatalogEntry X since there is no filter by STOREINV.STORE_ID.
To workaround this limitation, customers need to customize the Catalog search to include additional filter condition (see the filter condition highlighted in bold) :
select distinct CATENTRY.CATENTRY_ID, CATENTDESC.NAME from CATENTRY, CATENTDESC, CATGPENREL, STOREINV where ((((((((((upper(CATENTDESC.NAME) like '%INFOPRINT%')) or ((upper(CATENTDESC.SHORTDESCRIPTION) like '%INFOPRINT%'))) and CATGPENREL.CATALOG_ID = 10201) and STOREINV.STOREQUANTITY > 12) and STOREINV.STORE_ID = 10302)) and (((CATENTRY.BUYABLE = 1 and CATENTRY.MARKFORDELETE <> 1 and CATENTDESC.PUBLISHED = 1 and CATENTDESC.LANGUAGE_ID = -1))) and ((CATENTRY.CATENTTYPE_ID = 'ProductBean') or (CATENTRY.CATENTTYPE_ID = 'ItemBean') or (CATENTRY.CATENTTYPE_ID = 'PackageBean') or (CATENTRY.CATENTTYPE_ID = 'BundleBean')))) and (CATENTRY.CATENTRY_ID = (CATGPENREL.CATENTRY_ID) and CATENTRY.CATENTRY_ID = (CATENTDESC.CATENTRY_ID) and STOREINV.CATENTRY_ID = (CATENTRY.CATENTRY_ID))) order by CATENTDESC.NAME asc
This STORE_ID is the current store's id (in this case the extended site store) the search is performed on.
Related tasks
Add catalog search to the site
Related reference