JPA NamedQuery - Ignore Line when passed field is null

Question

So I have a NamedQuery where the fields are passed via an enityManager.createNamedQuery from a Repo.

So I have two fields, areaId and fieldId, areaId will always be present but fieldId will be null sometimes.

How do I omit(remove) the following line if :fieldId is null

and summary.bucket.fieldId.id = :fieldId --line to be removed

Below is my attempt using a case scenario but that doesn't work.

I'm open to the best approach or guidance on it please?

 @NamedQuery(name = "SummaryBySubstatus.getInfo",
            query = "select new com.model.group.summaryGroup(summary.bucket.area.id,"
                    summary.bucket.facilityProductInfo,
                    from SummaryBySubstatus as summary
                    where summary.bucket.area.id = :areaId
                    and summary.bucket.fieldId.id = :fieldId
                    ---Tried This way---
                    and(case when :fieldId != null then summary.bucket.fieldId.id = :fieldId end)"

Answer 1

and summary.bucket.fieldId.id = :fieldId

This will always evaluate to false when fieldId is null on compliant databases (not MySQL). Try

where summary.bucket.area.id = :areaId
and ( :fieldId IS NOT NULL AND summary.bucket.fieldId.id = :fieldId
or :fieldId IS NULL AND summary.bucket.fieldId.id = :fieldId )

And if you're using PostgreSQL, then there's a bug you need to bypass by calling

query.setParameter("fieldId", not_null_value_of_the_same_type);
query.setParameter("fieldId", the_real_value_that_can_be_null);

Or you could just create two queries.