Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – 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.

multiple dropdown filter

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!

Tags:

2

Answers


  1. 0

    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 != 0){
    $query .= " sedes.sede_codigo = COALESCE(:sede,0) ";
    $bindings['sede'] = $sede;
}
if($escuela != 0){
    $query .= " escuelas.esc_codigo = COALESCE(:escuela,0) ";
    $bindings['escuela'] = $escuela;

}
if($curso != 0){
    $query .= " ced_inscripcion.cein_cecu_codigo = COALESCE(:curso,0) ";
    $bindings['curso'] = $curso;
}
if($version != 0){
    $query .= " ced_inscripcion.cein_cvcu_version = COALESCE(:version,0) ";
    $bindings['version'] = $version;
}
DB::SELECT($query, $bindings);
    Login or Signup to reply.
  2. 0

    Your current approach is if a parameter wasn’t set, you pass a zero instead, which produces a mismatch on the condition the param was responsible for. To flip that and make a missing param just cancel the condition, wrap it in parentheses and add an or :param is null – that way the combined conditions will evaluate to true that doesn’t break your sequence of AND.

    WHERE (sedes.sede_codigo is not distinct from :sede 
       or :sede is null)
  AND (escuelas.esc_codigo is not distinct from :escuela 
       or :escuela is null)
  AND (ced_inscripcion.cein_cecu_codigo is not distinct from :curso 
       or :cursor is null)
  AND (ced_inscripcion.cein_cvcu_version is not distinct from :version 
       or :version is null)

    You can also use is not distinct from x comparison instead of =coalesce(x,y) to be able to actually look up records where the table really does hold null values in the target field. Here it’s ORd with true in that case anyways, but the construct is worth mentioning.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search