Filter table as per the multiple dropdown in cascade

I have multiple dropdown selects in cascade filtering data for a datatable.

sede(not binded), escuela (loads) > curso (loads) > version.

My query in PostgreSql has no problem processing the four dropdown values. It brings the results expected.

The problem arises when I want to filter only by 1 or 2 or 3 selects out of 4, it brings nothing since the query only processes 4 values, it doesn't accepts null or empty values.

Here is my Postgres query:

                     DB::SELECT("SELECT DISTINCT
                                ced_inscripcion.cein_rut AS Rut,
                                antecedentes_alumnos.anta_nombres AS Nombre,
                                antecedentes_alumnos.anta_paterno AS Apellido,
                                ced_inscripcion.cein_estado AS Estado,
                                escuelas.esc_descripcion AS Escuela,
                                ced_cursos.cecu_nombre AS Curso,
                                sedes.sede_nombre AS Sede,
                                antecedentes_alumnos.anta_fono1 Telefono, 
                                antecedentes_alumnos.anta_telefono_emergencia Telefono_eme,  
                                antecedentes_alumnos.anta_e_mail Correo,
                                ced_inscripcion.cein_fecha_inscripcion AS Fecha_Inscripcion
                            FROM ced_inscripcion
                            RIGHT JOIN 
                                ea_antecedentes ON ced_inscripcion.cein_rut = ea_antecedentes.eaa_rut 
                            INNER JOIN
                                antecedentes_alumnos ON ea_antecedentes.eaa_rut = antecedentes_alumnos.anta_rut
                            LEFT JOIN 
                                alumnos ON antecedentes_alumnos.anta_rut = alumnos.alum_antalu_rut
                            LEFT JOIN 
                                sedes ON alumnos.alum_cod_sede = sedes.sede_codigo 
                            INNER JOIN 
                                ced_cursos ON ced_inscripcion.cein_cecu_codigo = ced_cursos.cecu_codigo 
                            INNER JOIN 
                                escuelas ON ced_cursos.cecu_esc_codigo = escuelas.esc_codigo
                            WHERE
                                 sedes.sede_codigo = COALESCE(:sede,0)  
                            AND 
                                 escuelas.esc_codigo = COALESCE(:escuela,0)  
                            AND        
                                 ced_inscripcion.cein_cecu_codigo = COALESCE(:curso,0)  
                            AND
                                ced_inscripcion.cein_cvcu_version = COALESCE(:version,0)",
                                
                                ['version'=>$version, 'curso' =>$curso, 'sede'=>$sede, 'escuela' =>$escuela]
                            );

How can I make the AND operator inside the query conditional ? So it process and brings results for example using,

  sede = 2, escuela = 4, curso = NULL, version = NULL

Thanks in Advance!

Solution

Please note that you may not get any results when selecting certain filters because the final query includes 'sede = 2' and 'curso = null', which may not match any records based on the selected filter values. You need to exclude the values that are not selected from the query. Here is the updated version for you. or you can get an idea from that

$bindings = [];
$query = "SELECT DISTINCT
    ced_inscripcion.cein_rut AS Rut,
    antecedentes_alumnos.anta_nombres AS Nombre,
    antecedentes_alumnos.anta_paterno AS Apellido,
    ced_inscripcion.cein_estado AS Estado,
    escuelas.esc_descripcion AS Escuela,
    ced_cursos.cecu_nombre AS Curso,
    sedes.sede_nombre AS Sede,
    antecedentes_alumnos.anta_fono1 Telefono, 
    antecedentes_alumnos.anta_telefono_emergencia Telefono_eme,  
    antecedentes_alumnos.anta_e_mail Correo,
    ced_inscripcion.cein_fecha_inscripcion AS Fecha_Inscripcion
FROM ced_inscripcion
RIGHT JOIN 
    ea_antecedentes ON ced_inscripcion.cein_rut = ea_antecedentes.eaa_rut 
INNER JOIN
    antecedentes_alumnos ON ea_antecedentes.eaa_rut = antecedentes_alumnos.anta_rut
LEFT JOIN 
    alumnos ON antecedentes_alumnos.anta_rut = alumnos.alum_antalu_rut
LEFT JOIN 
    sedes ON alumnos.alum_cod_sede = sedes.sede_codigo 
INNER JOIN 
    ced_cursos ON ced_inscripcion.cein_cecu_codigo = ced_cursos.cecu_codigo 
INNER JOIN 
    escuelas ON ced_cursos.cecu_esc_codigo = escuelas.esc_codigo
    WHERE  1=1 ";

if($sede != NULL){
    $query .= " AND sedes.sede_codigo = :sede ";
    $bindings['sede'] = $sede;
}
if($escuela != NULL){
    $query .= " AND escuelas.esc_codigo = :escuela ";
    $bindings['escuela'] = $escuela;

}
if($curso != NULL){
    $query .= " AND ced_inscripcion.cein_cecu_codigo = :curso ";
    $bindings['curso'] = $curso;
}
if($version != NULL){
    $query .= " AND ced_inscripcion.cein_cvcu_version = :version ";
    $bindings['version'] = $version;
}
DB::SELECT($query, $bindings);