Debugging Catalog Filtering with Contracts

If the catalog filter does not work as expected, do the following checks:

Turn on the logs on the class SolrRESTSearchProductEntiltementExpressionProvider and check what the getExpression method returns:

Check if the expected expression has been created:

select EXPRESSION_ID, TRADING_ID, CATFILTER_ID, MEMBER_ID, cast(QUERY as varchar(1000)) from EXPRESSION

Check what contract ids are returned from the following queries:

Active reseller contracts belong to user_id, org_id or default
SELECT T.trading_id, T.account_id, T.starttime, T.endtime FROM PARTICIPNT P, TRADING T, STORECNTR S WHERE T.trading_id = P.trading_id and T.trading_id = S.contract_id 
and S.store_id =?store_id?  and P.partrole_id = 2 and P.termcond_id is null and (P.member_id = ?member_id? or P.member_id = ?org_id? or P.member_id is null) and T.state = 1 
and T.markfordelete = 0 

Active reseller contracts belong to all ancestor org of this org
SELECT T.trading_id, T.account_id, T.starttime,  T.endtime FROM PARTICIPNT P, TRADING T, STORECNTR S, mbrrel M WHERE T.trading_id = P.trading_id and T.trading_id = S.contract_id and S.store_id = ?store_id? and P.partrole_id = 2 and P.termcond_id is null and P.member_id = M.ancestor_id and M.descendant_id = ?org_id? and T.state = 1 and T.markfordelete = 0 

Active reseller contracts belong to the member group of this user
SELECT T.trading_id, T.account_id,  T.starttime, T.endtime FROM PARTICIPNT P, TRADING T, STORECNTR S, mbrgrpmbr M WHERE T.trading_id = P.trading_id and T.trading_id = S.contract_id and S.store_id = ?store_id? and P.partrole_id = 2 and P.termcond_id is null and P.member_id = M.mbrgrp_id and M.member_id = ?member_id? and M.exclude='0' and T.state = 1 and T.markfordelete = 0 

Check the following tables with more details:

select * from CONTRACT;
select * from TRADING;

select * from CATFILTER;
select * from PRODUCTSET;

select * from PRSETCEREL;

Check if the productset temporary table is correctly populated by the di-preprocess script:

SELECT * FROM TI_PRODUCTSET_0 where catentry_id in (?)

SELECT PRSETCEREL.CATENTRY_ID, PRODUCTSET.PRODUCTSET_ID  
FROM PRSETCEREL, PRODUCTSET, TI_CATENTRY_0  CE
WHERE PRODUCTSET.PRODUCTSET_ID = PRSETCEREL.PRODUCTSET_ID  
AND PRODUCTSET.MARKFORDELETE = 0  
AND PRSETCEREL.CATENTRY_ID=CE.CATENTRY_ID
ORDER BY CATENTRY_ID

Check if the product is properly indexed:

select * from TI_CATENTRY_0 where catentry_id in (1000005836, 1000005837)

SELECT DISTINCT CATENTRY.CATENTRY_ID, CATENTRY.CATENTTYPE_ID FROM CATENTRY, CATGPENREL WHERE CATENTRY.MARKFORDELETE=0 AND CATENTRY.CATENTRY_ID=CATGPENREL.CATENTRY_ID AND CATGPENREL.CATALOG_ID=11101 order by catentry.catentry_id;

Check if the query populated the PRODUCTSET with the expected productset id:

SELECT 
CATENTRY.CATENTRY_ID,CATENTRY.MEMBER_ID,CATENTRY.CATENTTYPE_ID,CATENTRY.PARTNUMBER,CATENTRY.MFPARTNUMBER,
CATENTRY.MFNAME, CATENTRY.BUYABLE, CATENTRY.STARTDATE, CATENTRY.ENDDATE,STORECENT.STOREENT_ID,
CATENTDESC.NAME,CATENTDESC.SHORTDESCRIPTION,CATENTDESC.LONGDESCRIPTION,CATENTDESC.THUMBNAIL,CATENTDESC.FULLIMAGE,
CATENTDESC.KEYWORD, CATENTDESC.PUBLISHED,CATENTSUBS.SUBSCPTYPE_ID,CATENTSUBS.DISALLOW_REC_ORDER,
TI_APGROUP.CATGROUPS APCATGROUP,
TI_APGROUP.CATPATHS APCATPATH,
TI_DPGROUP.CATGROUP DPCATGROUP,
TI_PRODUCTSET.PRODUCTSET,
TI_OFFERPRICE.PRICE_USD, TI_OFFERPRICE.PRICE_EUR, TI_OFFERPRICE.PRICE_CAD, TI_OFFERPRICE.PRICE_CNY,
TI_OFFERPRICE.PRICE_JPY, TI_OFFERPRICE.PRICE_KRW, TI_OFFERPRICE.PRICE_BRL, TI_OFFERPRICE.PRICE_TWD,
TI_OFFERPRICE.PRICE_PLN, TI_OFFERPRICE.PRICE_RUB, TI_OFFERPRICE.PRICE_RON, TI_OFFERPRICE.PRICE_EGP,
TI_OFFERPRICE.PRICE_GBP, TI_OFFERPRICE.PRICE_TRY, TI_OFFERPRICE.PRICE_ILS,
TI_LISTPRICE.LISTPRICE_USD, TI_LISTPRICE.LISTPRICE_EUR, TI_LISTPRICE.LISTPRICE_CAD, TI_LISTPRICE.LISTPRICE_CNY,
TI_LISTPRICE.LISTPRICE_JPY, TI_LISTPRICE.LISTPRICE_KRW, TI_LISTPRICE.LISTPRICE_BRL, TI_LISTPRICE.LISTPRICE_TWD,
TI_LISTPRICE.LISTPRICE_PLN, TI_LISTPRICE.LISTPRICE_RUB, TI_LISTPRICE.LISTPRICE_RON, TI_LISTPRICE.LISTPRICE_EGP,
TI_LISTPRICE.LISTPRICE_GBP, TI_LISTPRICE.LISTPRICE_TRY, TI_LISTPRICE.LISTPRICE_ILS,
TI_ADATTR.ATTRIBUTES CATENTRY_ATTRIBUTES,
TI_DPCATENTRY.CATENTRY_PARENT,
TI_GROUPING.GROUPING,
TI_COMPONENTS.COMPONENTS CATENTRY_COMPONENTS,
TI_DCCATENTRY.CATENTRY_CHILD,
TI_CATALOG.CATALOG PARENT_CATALOG_ID,
TI_CATGPENREL.SEQUENCE CATENTRY_SEQUENCE,
TI_ENTGRPPATH.ENTGRPPATH,
TI_SEOURL.SEO_TOKEN,
TI_CATENTDESCOVR.NAME_OVR, TI_CATENTDESCOVR.SHORTDESC_OVR, TI_CATENTDESCOVR.KEYWORD_OVR, TI_CATENTDESCOVR.PUBLISHED_OVR,
TI_DPGRPNAME.CATGRPNAME CATGRPNAME,
TI_CASTB1.CAS_F1 CAS_F1ATTR, TI_CASTB1.CAS_F2 CAS_F2ATTR, TI_CASTB1.CAS_F3 CAS_F3ATTR,TI_CASTB1.CAS_F4 CAS_F4ATTR,TI_CASTB1.CAS_F5 CAS_F5ATTR,
TI_CASTB1.CAS_F6 CAS_F6ATTR,TI_CASTB1.CAS_F7 CAS_F7ATTR, TI_CASTB1.CAS_F8 CAS_F8ATTR,TI_CASTB1.CAS_F9 CAS_F9ATTR,TI_CASTB1.CAS_F10 CAS_F10ATTR,
TI_CASTB1.CAS_F11 CAS_F11ATTR,TI_CASTB1.CAS_F12 CAS_F12ATTR,TI_CASTB1.CAS_F13 CAS_F13ATTR,TI_CASTB1.CAS_F14 CAS_F14ATTR,TI_CASTB1.CAS_F15 CAS_F15ATTR,
TI_CASTB1.CAS_F16 CAS_F16ATTR,TI_CASTB1.CAS_F17 CAS_F17ATTR, TI_CASTB1.CAS_F18 CAS_F18ATTR,TI_CASTB1.CAS_F19 CAS_F19ATTR,TI_CASTB1.CAS_F20 CAS_F20ATTR,
TI_CASTB1.CAS_F21 CAS_F21ATTR,TI_CASTB1.CAS_F22 CAS_F22ATTR,TI_CASTB1.CAS_F23 CAS_F23ATTR,TI_CASTB1.CAS_F24 CAS_F24ATTR,TI_CASTB1.CAS_F25  CAS_F25ATTR,
TI_CASTB1.CAS_F26  CAS_F26ATTR,TI_CASTB1.CAS_F27  CAS_F27ATTR,TI_CASTB1.CAS_F28  CAS_F28ATTR,TI_CASTB1.CAS_F29  CAS_F29ATTR,TI_CASTB1.CAS_F30  CAS_F30ATTR,
TI_CAITB1.CAI_F1, TI_CAITB1.CAI_F2, TI_CAITB1.CAI_F3, TI_CAITB1.CAI_F4, TI_CAITB1.CAI_F5, TI_CAITB1.CAI_F6, TI_CAITB1.CAI_F7, TI_CAITB1.CAI_F8, TI_CAITB1.CAI_F9, TI_CAITB1.CAI_F10,
TI_CAFTB1.CAF_F1, TI_CAFTB1.CAF_F2, TI_CAFTB1.CAF_F3, TI_CAFTB1.CAF_F4, TI_CAFTB1.CAF_F5, TI_CAFTB1.CAF_F6, TI_CAFTB1.CAF_F7, TI_CAFTB1.CAF_F8, TI_CAFTB1.CAF_F9, TI_CAFTB1.CAF_F10,
TI_ATTR.ATTRS ATTRS,
TI_ATTR.ATTRI ATTRI,
TI_ATTR.ATTRF ATTRF,
TI_ATTR.SRCHS SRCHS,
TI_ATTR.SRCHI SRCHI,
TI_ATTR.SRCHF SRCHF,
XCATENTRYWC.DISPPRICEINCARTONLY,
XCATENTRYWC.SEARCHABLE,
XCATENTRYOMSDESC.BVRATING,
XCATENTRYOMSDESC.BVNUMBEROFRATING,
XSEARCHABLEPRODUCTS.IN_STOCK,
XSEARCHABLEPRODUCTS.FSA,
XSEARCHABLEPRODUCTS.PURCHASABLE,
CATENTDESC.AUXDESCRIPTION1,
CATENTDESC.AUXDESCRIPTION2
FROM CATENTRY
INNER JOIN TI_CATENTRY_0 TI_CATENTRY ON (CATENTRY.CATENTRY_ID=TI_CATENTRY.CATENTRY_ID)
LEFT OUTER JOIN STORECENT ON (CATENTRY.CATENTRY_ID=STORECENT.CATENTRY_ID)
LEFT OUTER JOIN CATENTDESC ON (CATENTDESC.CATENTRY_ID=CATENTRY.CATENTRY_ID AND CATENTDESC.LANGUAGE_ID=-1)
LEFT OUTER JOIN CATENTSUBS ON (CATENTSUBS.CATENTRY_ID=CATENTRY.CATENTRY_ID)
LEFT OUTER JOIN TI_DPGROUP_0 TI_DPGROUP ON (CATENTRY.CATENTRY_ID=TI_DPGROUP.CATENTRY_ID)
LEFT OUTER JOIN TI_DPGRPNAME_0_1 TI_DPGRPNAME ON (CATENTRY.CATENTRY_ID=TI_DPGRPNAME.CATENTRY_ID)
LEFT OUTER JOIN TI_APGROUP_0 TI_APGROUP ON (CATENTRY.CATENTRY_ID=TI_APGROUP.CATENTRY_ID)
LEFT OUTER JOIN TI_PRODUCTSET_0 TI_PRODUCTSET ON (CATENTRY.CATENTRY_ID=TI_PRODUCTSET.CATENTRY_ID)
LEFT OUTER JOIN TI_OFFERPRICE_0 TI_OFFERPRICE ON (CATENTRY.CATENTRY_ID=TI_OFFERPRICE.CATENTRY_ID)
LEFT OUTER JOIN TI_LISTPRICE_0 TI_LISTPRICE ON (CATENTRY.CATENTRY_ID=TI_LISTPRICE.CATENTRY_ID)
LEFT OUTER JOIN TI_DPCATENTRY_0 TI_DPCATENTRY ON (CATENTRY.CATENTRY_ID=TI_DPCATENTRY.CATENTRY_ID)
LEFT OUTER JOIN TI_GROUPING_0 TI_GROUPING ON (CATENTRY.CATENTRY_ID=TI_GROUPING.CATENTRY_ID)
LEFT OUTER JOIN TI_COMPONENTS_0 TI_COMPONENTS ON (CATENTRY.CATENTRY_ID=TI_COMPONENTS.CATENTRY_ID)
LEFT OUTER JOIN TI_DCCATENTRY_0 TI_DCCATENTRY ON (CATENTRY.CATENTRY_ID=TI_DCCATENTRY.CATENTRY_ID)
LEFT OUTER JOIN TI_CATALOG_0 TI_CATALOG ON (CATENTRY.CATENTRY_ID=TI_CATALOG.CATENTRY_ID)
LEFT OUTER JOIN TI_CATGPENREL_0 TI_CATGPENREL ON (CATENTRY.CATENTRY_ID=TI_CATGPENREL.CATENTRY_ID)
LEFT OUTER JOIN TI_ENTGRPPATH_0 TI_ENTGRPPATH ON (CATENTRY.CATENTRY_ID=TI_ENTGRPPATH.CATENTRY_ID)
LEFT OUTER JOIN TI_SEOURL_0_1 TI_SEOURL ON (CATENTRY.CATENTRY_ID=TI_SEOURL.CATENTRY_ID)
LEFT OUTER JOIN TI_CEDSOVR_0_1 TI_CATENTDESCOVR ON (CATENTRY.CATENTRY_ID=TI_CATENTDESCOVR.CATENTRY_ID)
LEFT OUTER JOIN TI_CASTB1_0_1 TI_CASTB1 ON (CATENTRY.CATENTRY_ID=TI_CASTB1.CATENTRY_ID)
LEFT OUTER JOIN TI_CAITB1_0_1 TI_CAITB1 ON (CATENTRY.CATENTRY_ID=TI_CAITB1.CATENTRY_ID)
LEFT OUTER JOIN TI_CAFTB1_0_1 TI_CAFTB1 ON (CATENTRY.CATENTRY_ID=TI_CAFTB1.CATENTRY_ID)
LEFT OUTER JOIN TI_ATTR_0_1 TI_ATTR ON (CATENTRY.CATENTRY_ID=TI_ATTR.CATENTRY_ID)
LEFT OUTER JOIN TI_ADATTR_0_1 TI_ADATTR ON (CATENTRY.CATENTRY_ID=TI_ADATTR.CATENTRY_ID)
LEFT OUTER JOIN XCATENTRYWC ON (CATENTRY.CATENTRY_ID=XCATENTRYWC.CATENTRY_ID)
LEFT OUTER JOIN XCATENTRYOMSDESC ON (CATENTRY.CATENTRY_ID=XCATENTRYOMSDESC.CATENTRY_ID)
LEFT OUTER JOIN XSEARCHABLEPRODUCTS ON (CATENTRY.CATENTRY_ID=XSEARCHABLEPRODUCTS.CATENTRY_ID)
WHERE  CATENTRY.CATENTTYPE_ID in ('ProductBean', 'ItemBean', 'PackageBean')

Check the Search ProductView Service accepts contract id and checkEntitlement flag properly:

http://localhost:80/search/resources/store/10001/productview/bySearchTerm/american?catalogId=20001&langId=-1&currency=USD&responseFormat=json&pageSize=12&pageNumber=1&profileName=findProductsBySearchTerm&checkEntitlement=true&contractId=30001&priceMode=mixed