FlexibleSearch using enum in WHERE clause

Question

I am working with Hybris and trying to query all the products that have an approval status of APPROVED within Java code. It should look like below, but I cannot get it to work in either HAC, or in Java code.

What is the correct way to do it?

SELECT {p:pk} FROM {Product as p join EnumerationValue as enum on enum.pk = p.approvalStatus } WHERE {enum:code[en]} = 'APPROVED'

---

Thank you everyone, here is the final answer that works, see comments in other replies for some more questions I have and hopefully some wonderfully wise replies:

        final StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("SELECT {p:").append(ProductModel.PK).append("} ");
        stringBuilder.append("FROM {").append(ProductModel._TYPECODE).append(" as p join ")
                .append(ArticleApprovalStatus._TYPECODE).append(" as enum on {enum.pk} = {p.").append(ProductModel.APPROVALSTATUS).append("} join ")
                .append(CatalogVersionModel._TYPECODE).append(" as cv on {cv.pk} = {p.").append(ProductModel.CATALOGVERSION).append("} } ");
        stringBuilder.append("WHERE ( {enum:code} = ?approvalStatus1 or {enum:code} = ?approvalStatus2} and {cv:")
                .append(CatalogVersionModel.VERSION)
                .append("} = ?version");

        final String unapprovedString = ArticleApprovalStatus.UNAPPROVED.toString().toLowerCase();
        params.put("approvalStatus1", unapprovedString);
        params.put("approvalStatus2", ArticleApprovalStatus.UNAPPROVED.toString().toLowerCase());

Answer 1

You need to use the type of the enum. Also, the enum code is case-sensitive; it depends on what is defined in the items.xml.

SELECT * FROM {Product 
JOIN ArticleApprovalStatus ON {Product.approvalStatus} = {ArticleApprovalStatus.pk}}
WHERE {ArticleApprovalStatus.code} = 'approved'