Keyword filter with other fields

Question

Hello, I am developing a system where I need to perform a user filtering, and in this filtering will be used several simultaneous fields (name, Cpf, gender, age and etc.), the problem begins when I try to add a field with words-key, because I need to search in at least 3 different tables the words inserted, along with the fields mentioned above. Without using keywords, filtering works normally.

I am using the Codeigniter framework in version 3.1.10, and in models, I am using the query Builder.

I tried to create an array with the comma-separated string coming from the form, using the like ($this->db->like($array);), but it didn’t work, I also tried the group ($this->db->group_start();) and it didn’t work, which I even used in other filtering (no keywords) and works properly.

I tried to do it this way, but without success:

public function filter_candidatos($nome, $sobrenome, $cpf, $palavraChave, $funcoes, $idadeIni, $idadeFin, $sexo, $pcd, $cid, $escolaridade, $pais, $estado, $cidades, $conceito, $status){
    // FILTRO ADICIONANDO LOCALIZAÇÃO
    if(!empty($pais) || !empty($estado) || !empty($cidades)){
        $this->db->join("endereco as e", "e.id_endereco = u.id_endereco");
        (!empty($pais) ? $this->db->where("e.id_pais", $pais) : "");
        $this->db->where("e.id_estado", $estado);
        if(!empty($cidades)){
            $cidade = explode(",",$cidades);
            $this->db->where_in("e.cidade", $cidade);
        }
    }
    // FILTRO ADICIONANDO FUNÇÕES
    if(!empty($funcoes)){
        $funcao = explode(",",$funcoes);
        $this->db->join("funcao_usuario as fu", "fu.id_usuario = u.id_usuario");
        $this->db->where_in("fu.id_funcao", $funcao);
    }
    // FILTRO ADICIONANDO ESCOLARIDADE
    if(!empty($escolaridade)){
        $escolaridade = explode(",", $escolaridade);
        $this->db->where_in("u.escolaridade", $escolaridade);
    }
    // FILTRO ADICIONANDO RANGE DE IDADE
    if(!empty($idadeIni)){
        $this->db->where('u.dt_nascimento BETWEEN "'.$idadeFin.'" AND "'.$idadeIni.'"');
    }
    // FILTRO ADICIONANDO PALAVRAS CHAVE
    if(!empty($palavraChave)){ // AINDA NÃO ESTÁ ATIVO
        //$palavraChave = explode(",", $palavraChave);
        if(empty($funcoes)){
            $this->db->join("funcao_usuario as fu", "fu.id_usuario = u.id_usuario");
        }
        $this->db->join("funcao as f", "f.id_funcao = fu.id_funcao");
        $this->db->join("escolaridade as esc", "esc.id_usuario = u.id_usuario");
        $this->db->join("usuario_idioma as ui", "ui.id_usuario = u.id_usuario");
        $this->db->join("idioma as i", "i.id_idioma = ui.id_idioma");

        $palavraChave = explode(",", $palavraChave);
        $arrayFuncao = [];

        foreach($palavraChave as $palavras){
            array_push($arrayFuncao, "'f.nome' => $palavras");
        }

        var_dump($arrayFuncao);
        die();

        $this->db->or_like($arrayFuncao);

        /*$this->db->group_start();
            $this->db->where_in("f.nome", $palavraChave);
            $this->db->or_group_start();
                $this->db->where_in("esc.curso", $palavraChave);
                $this->db->or_group_start();
                    $this->db->where_in("i.nome", $palavraChave);
                $this->db->group_end();
            $this->db->group_end();
        $this->db->group_end();*/
        // campos a percorrer
        /*if( strpos(file_get_contents("texto.txt"),$_POST['palavra']) !== false) {
        echo "tem";
        }else{
        echo "não tem";
        }*/
    }
    // FILTRO BÁSICO
    $this->db->select("u.id_usuario, u.nome, u.sobrenome, u.dt_nascimento, u.pcd, u.conceito, u.dt_alteracao, u.anexo_curriculo");
    $this->db->from("usuario as u");
    $this->db->where("u.id_tipo_usuario", 3);
    $this->db->like("u.nome", $nome);
    $this->db->like("u.sobrenome", $sobrenome);
    $this->db->like("u.cpf", $cpf);
    $this->db->like("u.genero", $sexo);
    $this->db->like("u.pcd", $pcd);
    $this->db->like("u.cid", $cid);
    $this->db->like("u.conceito", $conceito);
    $this->db->like("u.status_2", $status);
    $this->db->order_by("u.nome", "ASC");

    return $this->db->get()->result_array();
}

In the researches I performed, the solutions I find always incline to use a foreach and bring the results found for each keyword, but I can’t apply this, because the filtering is not only keywords.

Someone has been through something similar, and can give me the path of stones to solve this problem?

Thanks for your attention!

Answer 1

To solve your problem your query must perform your SELECT going through the 3 tables, whether or not there is a filter (use LEFT JOIN if the parameters are not mandatory).

After having your entire select mounted, you just check whether it was sent the keyword filter and add in your search.

I have no experience with codeigniter, but it follows a basic example of what it would be:

$sql = SELECT * FROM table1 LEFT JOIN table2 ON (table1.reference=table2.reference) LEFT JOIN table3 ON (table1.reference=table3.reference);

if ($filtro){
   $sql .= "AND (table1.nome LIKE '%$filtro%' OR table2.cidade LIKE '%$filtro%')";
}

// execute sql.

This filter can and should be solved directly in SQL, resolvelo in a foreach would only increase the processing and memory usage of your application (In addition to greatly increase your code).