Make SELECT with average user reviews

Asked

Viewed 90 times

1

Well, I’m developing a project and it has a 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 will not show users who have just signed up. 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 $idpessoa variable? I need you to have a column that shows the average reviews received, but without using INNER JOIN between tb_person and tb_review.

Modelo do BD aqui

mysql sql query select

  • Why don’t you want to use the variable? How do I know if the user has just registered? If possible, it also leaves the structure of the bank because I believe it is necessary to improve this query.. must be very heavy processing..

  • Because this select should list users, I do not have the person’s id, what I really need is a column that shows the average ratings of each user, if no one has voted should still appear 0, but do this without giving INNER JOIN between tb_person and tb_rating

  • Then it would be nice to inform which values are being passed to the variables, which columns it needs, and better pass the table structures being used.

  • I edited and put together the comic book model

  • Put some table data as text. Also put a desired output format

1 answer

1


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

FROM tb_pessoa as p gives to the table tb_pessoa the alias p, that can be used in a subconsultation. Therefore, the code below should meet the expected:

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 select more internal renamed the alias of tb_pessoa for a1.

Anyway, I believe that this consultation can be further worked to improve performance, as it makes many junctions and sub-discharge - perhaps not all these operations are necessary.

Browser other questions tagged mysql sql query select

You are not signed in. Login or sign up in order to post.