Is there a way to disable Type Checking for any type while doing the flexible search query?

[Former Member]

want to write and run a flexible query query similar to below SQL query.

SELECT p.p_code AS pcode1, p1.p_code AS pcode2, p2.p_code AS pcode3 FROM products AS p LEFT JOIN products AS p1 ON p1.p_baseproduct = p.PK LEFT JOIN products AS p2 ON p2.p_baseproduct = p1.Pk WHERE FIND_IN_SET(p.p_code, prodCodes)

Flexible Query I Wrote:

SELECT {p.code AS pcode1}, {p1.code AS pcode2}, {p2.code AS pcode3} FROM {Product AS p LEFT JOIN Product AS p1 ON {p1.baseProduct} = {p.pk} LEFT JOIN Product AS p2 ON {p2.baseProduct} = {p1.pk}} WHERE {p.code} IN (?prodCodes) AND ({p.approvalStatus = ?approvedStatusPK OR {p.approvalStatus} IS NULL) AND ({p1.approvalStatus = ?approvedStatusPK OR {p1.approvalStatus} IS NULL) AND ({p2.approvalStatus = ?approvedStatusPK OR {p2.approvalStatus} IS NULL)

But it is giving error as

de.hybris.platform.servicelayer.search.exceptions.FlexibleSearchException: cannot search unknown field 'TableField(name='baseProduct',langPK='null',type=Product)' within type Product unless you disable checking , infoMap=TypeInfoMap for type = 8796098658386

Actually wants to flat the table structure based on different product types we have in products table?

can anyone please help me in that?

[Huskar · Accepted solution]

try this. select distinct {p.pk} from {Product as p join VariantProduct as var ON {var.baseProduct}={p.pk}}. Because baseProduct is atribute of VariantProduct and not Product.

Above query will fetch all base product which are base product of any product.

Hope this help.

[Former Member · Accepted solution]

Thanks