CODEWITHSUNDEEP
mysqlsqlpaginationmariadb
Sandro Benevides
Sandro Benevides

Reputation: 631

How can I use SQL_CALC_FOUND_ROWS when having multiple selects

I'm working on a pagination code that uses SQL_CALC_FOUND_ROWS() but when I limit the results per page to a number less than the total results, "SELECT FOUND_ROWS() as total" will return the number of elements per page and not the total of found rows. Since I'm doing a select from the second select, this result makes total sense but I don't know how to solve this. How can I pass the total results from inside to the external select?

My code(please, ignore quotation marks for php escaping):


SELECT SQL_CALC_FOUND_ROWS userid,contaTipo,userNome,nomeFantasia,sexo,cidade,estado,bairro,imovelN,logradouro,avaliacao,imagem,formasPagamento,estabelecimento,profissao 

FROM 

(SELECT vw_Busca.userid as userid, vw_Busca.contaTipo as contaTipo, vw_Busca.userNome as userNome, 
vw_Busca.nomeFantasia as nomeFantasia,vw_Busca.sexo as sexo, vw_Busca.cidade as cidade, vw_Busca.estado as estado, vw_Busca.bairro as bairro, 
vw_Busca.imovelN as imovelN,vw_Busca.logradouro as logradouro,tipoProfissionalPF.tipo as profissao, tipoProfissionalPJ.tipo as estabelecimento,
vw_userRating.total as avaliacao, GROUP_CONCAT(especialidades.especialidade SEPARATOR ', ') as especs,
vw_Busca.imagem as imagem, GROUP_CONCAT( DISTINCT userPagamento.formaPagamento SEPARATOR ', ') as formasPagamento

    FROM vw_Busca 

LEFT JOIN usersEspec ON usersEspec.userid=vw_Busca.userid 
LEFT JOIN especialidades ON especialidades.id=usersEspec.especialidade 
LEFT JOIN userPagamento ON userPagamento.userid=vw_Busca.userid 
LEFT JOIN profissionais ON profissionais.userid=vw_Busca.userid
LEFT JOIN tipoProfissionalPF ON tipoProfissionalPF.id=profissionais.profissao 
LEFT JOIN empresaDados ON empresaDados.userid=vw_Busca.userid
LEFT JOIN tipoProfissionalPJ ON tipoProfissionalPJ.id=empresaDados.tipoProfissionalPJ
LEFT JOIN vw_userRating ON vw_userRating.userid=vw_Busca.userid 

WHERE vw_Busca.cidadeId='$cidade' AND (vw_Busca.userNome LIKE '%".$termo."%' OR 
    vw_Busca.nomeFantasia LIKE '%".$termo."%' 
    OR vw_Busca.tags LIKE '%".$termo."%')

    GROUP BY userid LIMIT $inicio,$qtd) as mainTable

     ORDER BY mainTable.avaliacao DESC

Upvotes: 1

Views: 917

Answers (1)

Kishore Kirdat
Kishore Kirdat

Reputation: 501

It appears that your outer select is just ordering results returned from the inner query. Since your inner query has already applied a LIMIT, your outer query is actually performing sort on partial results, which seems incorrect to me. So I guess you can remove the outer select altogether which would solve your problem.

By the way, just noticed that the SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 as per the documentation

Upvotes: 1

Related Questions