Problem in SQL statement

Question

I have a problem with my code SQL. Well, I have this function that is in the class Usuario:

public function CS_GetUsuarios($sql_aux = false)
{
    global $objConexao;

    $strSql =   "SELECT U.*, A.*, G.*,
                (
                SELECT COALESCE(SUM(V.countView), 0) FROM usuario_visitas V WHERE V.codUsuario = U.codUsuario
                ) as total_visitas,
                (
                SELECT COALESCE(SUM(F.countVotos), 0) FROM usuario_votos F WHERE F.codUsuario = U.codUsuario
                ) as total_votos
                FROM usuario U
                LEFT JOIN usuario_visitas A ON U.codUsuario = A.codUsuario
                LEFT JOIN usuario_fotos G ON G.cod_usuario = U.codUsuario
                WHERE U.indAtivo = 'S' AND G.indExibir = 'S'
                $sql_aux";

    $vetDados = $objConexao->query($strSql)->fetchAll();
    return $vetDados;
}

And on the index I’m calling the same:

$vet_dados_top = Usuario::CS_GetUsuarios("AND U.tipo_conta = '1' GROUP BY U.codUsuario ORDER BY total_visitas DESC LIMIT 12");

Well, my problem is that, it just returns me the data complete users who have visits (record) in the table usuario_visitas. When the user has not received any visitors in the profile it specifically returns the codUsuario = null, which, in my logic, I programmed for him to bring all the records, even those users who have no visit.

Giving a var_dump() in $vet_dados_top the result is more or less this:

User who has visit

array (size=121)
  'codUsuario' => string '1182652231' (length=10)
  0 => string '1182652231' (length=10)
  'strLogin' => string 'fulano' (length=7)
  1 => string 'fulano' (length=7)
  'strSenha' => string 'XXXXXXXXXXXXX' (length=32)
  2 => string 'XXXXXXXXXXXXX' (length=32)
  [...]

User who has no visits

array (size=121)
  'codUsuario' => null
  0 => string '1182652232' (length=10)
  'strLogin' => string 'ciclano' (length=5)
  1 => string 'ciclano' (length=5)
  'strSenha' => string 'XXXXXXXXXXXX' (length=32)
  2 => string 'XXXXXXXXXXXX' (length=32)
  [...]

The table usuario_visitas does not have the codes of all users, only the ones that were visited. Visited, was added to the table.

Where I’m missing the thought?

Answer 1

In Where, you enter the clause of table G.

Also change the order of coalesce, within the Sum().

and when passing the parameters, do not enter the group by, there is no aggregation function in the query to be grouped.

Try to do the following:

"SELECT U.*, A.*, G.*, U.codUsuario as codigoUsuario,
            (
            SELECT SUM(COALESCE(V.countView, 0)) FROM usuario_visitas V WHERE V.codUsuario = U.codUsuario
            ) as total_visitas,
            (
            SELECT SUM(COALESCE(F.countVotos, 0)) FROM usuario_votos F WHERE F.codUsuario = U.codUsuario
            ) as total_votos
            FROM usuario U
            LEFT JOIN usuario_visitas A ON U.codUsuario = A.codUsuario
            LEFT JOIN usuario_fotos G ON G.cod_usuario = U.codUsuario AND G.indExibir = 'S'
            WHERE U.indAtivo = 'S' 
            $sql_aux";

I added another column searching for the value of U.codUsuario and renaming it to codeUsuario, in your php, search for the code valueUsuario and see if it’s correct

Answer 2

...
"SELECT U.*, A.*, G.*,
                (
                SELECT COALESCE(SUM(V.countView), 0) FROM usuario_visitas V WHERE V.codUsuario = U.codUsuario
                ) as total_visitas,
                (
                SELECT COALESCE(SUM(F.countVotos), 0) FROM usuario_votos F WHERE F.codUsuario = U.codUsuario
                ) as total_votos
                FROM usuario U
                LEFT JOIN usuario_visitas A ON U.codUsuario = A.codUsuario
                LEFT JOIN usuario_fotos G ON G.cod_usuario = U.codUsuario
                      AND G.indExibir = 'S'     
                WHERE U.indAtivo = 'S'
                $sql_aux";
...