Handling IS NULL SQL parameters in geoserver

Question

I'm creating a SQL layer from postgres database on geoserver using query and applying style on it.

I want to create two layer based on parameter passed into it, currently I'm handling this by creating two separate SQL layers.

First layer:

SELECT 
    activity_group_id, activity_name, application_status_code,
    village_code, geom
FROM 
    dbt.dbt_point_primary 
WHERE 
    village_code = '%vinCode%' 
    AND attributes = '%attribute%' 
    AND application_status_code = %statusCode%

Second layer:

SELECT 
    activity_group_id, activity_name, application_status_code,
    village_code, geom
FROM 
    dbt.dbt_point_primary 
WHERE 
    village_code = '%vinCode%' 
    AND attributes = '%attribute%' 
    AND application_status_code = %statusCode%
    AND activity_group_id = %activity_group_id%

While accessing this layer by getMap function I'm passing viewParams

&viewparams=vinCode:546748;statusCode:1;attribute:Farmer

and

&viewparams=vinCode:546748;statusCode:1;activity_group_id:19;attribute:Farmer

My concern here is instead of creating 2 separate layer for same expected layer can I handle this into only 1 SQL layer by using ISNULL function in the SQL layer.

Does anybody have any source to share?

Answer 1

You can check if your parameter is null and if not use it to filter the query

...
AND (%activity_group_id% IS NULL OR activity_group_id = %activity_group_id%)