How to make a select and a "Sub Select" faster (in PHP)

Question

I am facing problems with query for counting records. I have a table of clients (separated by owners and family members). And I need to count the records by status, but the status of the family will be the same as the holder.

I do it as follows (I’m using PDO):

//todos os titulares inativos
try{
    $titulares_i = $pdo->prepare("SELECT id FROM clientes WHERE status != :status && registro_empresa = :registro && titular = :titular && cancelado = :cancelado"); 
    $titulares_i->bindValue(':status', 'ativo');
    $titulares_i->bindValue(':registro', '$_COOKIE['registro']');
    $titulares_i->bindValue(':titular', 's');
    $titulares_i->bindValue(':cancelado', '');
    $titulares_i->execute();
    $titulares_i_row = $titulares_i->rowCount();

}catch(PDOExeception $e){
    print $e->getMessage();
}//todos os titulares inativos

The amount of holders I already have in the Rowcount, for count dependents am creating a bond with the holders.

$contagem_dependentes = 0;
//Laço para trazer os familiares dos titulares inativos
while ($tit_i_arr = $tit_i->fetch(PDO::FETCH_ASSOC)) {


        //todos os dependentes ativos (funcionários + dependentes)
    try{
        $dep_i = $pdo->prepare("SELECT id FROM clientes WHERE registro_empresa = :registro && titular = :titular && id_titular = :id"); 
        $dep_i->bindValue(':registro', $cookie_adm);
        $dep_i->bindValue(':titular', '');
        $dep_i->bindValue(':num_card', $id_titular);
        $dep_i->execute();
        $dep_i_row = $dep_i->rowCount();

    }catch(PDOExeception $e){
        print $e->getMessage();
    }//todos os dependentes ativos (funcionários + dependentes) 

 $contagem_dependentes += $dep_i_row;

}//Laço para trazer os dependentes dos titulares inativos

I know you have much better practices, but I do not know the whole operation of SQL, I’m studying about.

Look silly, because clearly it would be easier to make only 2 selects, but as the status of the family varies according to the holder,it was necessary to select for example the active holders and their dependents (then all enter as assets)

due to the thousands of records, it takes a few minutes to load, getting to give timeout normally..

Logically, I imagined something along those lines:

$parametros = $pdo->prepare("
SELECT id FROM clientes WHERE condicoes_aqui,
 lool_em_sql{
     SELECT id FROM clientes WHERE id_titular = id_titular_acima 
 }
  
");

But I have no idea how to do something like this, nor if it’s possible, I’ve done a lot of research, but I just find examples in other patterns and confused, which don’t apply to my case, I would be super grateful if someone would shed some light on.

Thank you!!!

Answer 1

If you want to make sub selects or selects where there is high complexity of the query maybe it is better to create a manual query since many frameworks they do not make complex selects and just nest consecutive selects and then put everything within arrays and returns the value.

For example a query that would be:

select * from table inner join table2 on table.id = table2.table_id where table.field = (select field from table where field = 'condition'

In his case I see that apparently you are using the pure PDO object of PHP, so you can easily make a raw query with it according to the very example of the document, you can find examples by clicking here.