Do SELECT with average user ratings

Question

Do SELECT with average user ratings

Navigation

#1 by (1 votes)

1

Well, I'm developing a project and it has the list of people, I would like to list them based on the average of the evaluation, I tried to do with INNER JOIN and AVG, but with them they will not show users who have just registered. The SELECT that works for what I want is this:

SELECT p.idPessoa, p.nomePessoa, p.descricaoPessoa, f.nomeFoto, 
    (SELECT SUM(a.valorAvaliacao / 
               (SELECT COUNT(a.idAvaliacao) FROM tb_avaliacao as a 
               WHERE a.idCategoria = '$idcategoria' && a.idPessoa = '$idpessoa' 
               GROUP BY a.idPessoa AND a.idCategoria)) 
    FROM tb_avaliacao as a 
    WHERE a.idCategoria = '$idcategoria' && a.idPessoa = '$idpessoa' 
    GROUP BY a.idPessoa AND a.idCategoria) as valorAvaliacao 
 FROM 'tb_pessoa' as p
 INNER JOIN tb_pessoacategoria as pc ON pc.idPessoa = p.idPessoa
 INNER JOIN tb_foto as f ON pc.idPessoa = f.idPessoa and pc.idCategoria = f.idCategoria
 INNER JOIN tb_categoria as c ON c.idCategoria = pc.idCategoria
 WHERE c.idCategoria = '$idcategoria'
 GROUP BY p.idPessoa
 ORDER BY valorAvaliacao DESC, f.idFoto

Is there any way to do the same SELECT only without using the variable $ idpessoa? I need you to have a column that shows the average of the evaluations received, but without using INNER JOIN between tb_people and tb_avaliacao.

mysql sql query select

asked by anonymous 08.11.2018 / 13:02

1 answer

How to do Update in Linq? how do I put a Scrollbar in a window in python as if it were a web page, the more content I could get down through Scrollbar

score 1 · Accepted Answer

Since the query presented is correct and would / would bring the expected result, and the real problem is that there is no $idpessoa variable, you can correct it by referencing the "outside" table.

FROM tb_pessoa as p gives the table tb_pessoa o alias p , which can be used in a subquery . Therefore, the code below should meet expectations:

SELECT p.idPessoa, p.nomePessoa, p.descricaoPessoa, f.nomeFoto, 
    (SELECT SUM(a.valorAvaliacao / 
               (SELECT COUNT(a1.idAvaliacao) FROM tb_avaliacao as a1 
               WHERE a1.idCategoria = '$idcategoria' && a1.idPessoa = p.idPessoa 
               GROUP BY a1.idPessoa AND a1.idCategoria)) 
    FROM tb_avaliacao as a 
    WHERE a.idCategoria = '$idcategoria' && a.idPessoa = p.idPessoa
    GROUP BY a.idPessoa AND a.idCategoria) as valorAvaliacao 
 FROM 'tb_pessoa' as p
 INNER JOIN tb_pessoacategoria as pc ON pc.idPessoa = p.idPessoa
 INNER JOIN tb_foto as f ON pc.idPessoa = f.idPessoa and pc.idCategoria = f.idCategoria
 INNER JOIN tb_categoria as c ON c.idCategoria = pc.idCategoria
 WHERE c.idCategoria = '$idcategoria'
 GROUP BY p.idPessoa
 ORDER BY valorAvaliacao DESC, f.idFoto

Note that in the most internal select the alias of tb_pessoa to a1 .

In any case, I believe this query can be further worked on to improve performance, since it does many joins and subqueries - perhaps not all of these operations are necessary.