Consultas multitablas para cálculo de tops

[sataca]

Hola,

estoy programando una web (PHP+MySQL), tipo foro. Donde los usuarios proponen un tema, y estos se pueden comentar y valorar.

A partir de los comentarios, lecturas, valoraciones y fecha del tema se sacan unos tops.
El de fecha y lecturas, no hay problema, ya que son campos en la misma tabla TEMAS.

Pero para sacar los más valorados, y los más comentados, he de consultar en dos tablas y aplicarle LIMIT y ORDER. Lo que tengo hecho ahora funciona, pero en la bd hay pocos registros. Me temo que tal como lo tengo ahora, en cuanto se vayan añadiendo registros, sea terriblemente lento el sistema, al no tener LIMIT en ningún laod

El caso de los comentarios. Tengo una tabla llamada RESPUESTAS, que guarda el id_tema (relacionado con el campo id de TEMAS), el comentario, el id_usuario, el estado (si es público o no) y la fecha.
Lo que hago para obtener los temas más comentados es lo siguiente:

Código PHP:

  SELECT distinct(a.id_tema) as id_tema FROM RESPUESTAS a,TEMAS b WHERE b.estado=1"; 
   

Esta consulta la guardo en un array y luego hago esto:

Código PHP:

  SELECT COUNT(id) AS valor,id_tema FROM RESPUESTAS WHERE id_tema='".$id_tem."'" 
   

, donde $id_tem es cada uno de los temas encontrados en a primera consulta.

Seguro que una de los consultas sobra, y lo más grave es que no veo la forma de añadir un LIMIT, y tampoco me deja aplicar "ORDER BY valor ASC"

¿Se os ocurre como optimizar esta consulta y aplicarle un LIMIT y un TOP??



Y el otro caso, el de los tops de temas más valorados. Tengo el mismo problema, no veo como aplicar un LIMIT ni me deja ordenar a través del campo que obtengo en el SUM.

Para esto tengo la tebla TEMAS, la vista TEMAS_VALORADOS, y la tabla VALORACIONES donde guardo el id_tema, el id_usuario y el voto (1 ó -1).

La vista TEMAS_VALORADOS es la siguiente:

Código PHP:

  SELECT DISTINCT VALORACIONES.id_tema AS id_tema FROM VALORACIONES 
   

Este caso lo que hago es lo siguiente, le paso un id_categoria para filtrar:

Código PHP:

  SELECT a.id_tema,b.id_categoria FROM TEMAS_VALORADOS a,TEMAS b WHERE b.id_categoria='".$id_categoria."' AND a.id_tema=b.id"; 
   

De aqui obtengo un listado de temas, que lo meto en un array y hago lo mismo que en los más comentados:

Código PHP:

  SELECT SUM(a.valoracion) AS valor, a.id_tema FROM VALORACIONES a,TEMAS b WHERE a.id_tema='".$id_tem."'" 
   

Y me ocurre lo mismo, si la aplico un ORDER BY valor ASC (ó DESC) no me hace ni puto caso.



Bueno, seguro que me estoy haciendo un lio y es mucho más fácil de lo que creo, pero no veo la forma.

En resumen, necesito aplicar un LIMIT y un ORDER en estas consultas.

Muchas gracias y saludos

[javi_cassi]

Hola Sataca.
Te comento las cosas qeu veo.

El caso de los comentarios. Tengo una tabla llamada RESPUESTAS, que guarda el id_tema (relacionado con el campo id de TEMAS), el comentario, el id_usuario, el estado (si es público o no) y la fecha.
Lo que hago para obtener los temas más comentados es lo siguiente:

SELECT distinct(a.id_tema) as id_tema FROM RESPUESTAS a,TEMAS b WHERE b.estado=1";


SELECT COUNT(id) AS valor,id_tema FROM RESPUESTAS WHERE id_tema='".$id_tem."'"

Tienes esas dos consultas, pues como bien dices, una sobra.
Te explico:

-segun veo tienes dos tablas : RESPUESTAS y TEMAS. Y lo que deseas hacer es ver qué temas (y cuántos) son los más votados.
Como bien dices, en la priemra consulta que haces, en el momento en que hay varios registros, la cosa ira mal. Por qué, porque no haces ninugna unión entre las tablas.
La tabla TEMAS tendrás una primary-key, que supongo será id_tema (por ejempl)
Y la tabla RESPUESTAS tendrá otra primary key que será: (usuario, id_tema, fecha) (por ejemplo). pero a su vez id_tema será una FOREIGN KEY(porque no podrá elegir temas que no estén en la tabla TEMAS)

Por lo tanto la selecrt qeu buscas será del siguiente modo:

select distinct(b.descripcion_tema)
from respuestas a, temas b
where b.id_tema=a.id_tema

' and b.estado= 1' No sé que pretendes con este campo, pero si los usas será por algo, así qeu tendras qeu añadirlo a la consulta.

De esta forma obtendras la descripcion de todos los temas que han sido votados.
Para saber el numero de temas que hay, tienes dos opciones, una vez tengas los resultados de la select anterior los cuentas, o bien haces otra select exactamente igual:

select count(distinct(b.descripcion_tema))
from respuestas a, temas b
where b.id_tema=a.id_tema

' and b.estado= 1' No sé que pretendes con este campo, pero si los usas será por algo, así qeu tendras qeu añadirlo a la consulta.

EDITO: He releido las cosas, y supongo que con la condicion comentada antes pretendes sacar las que tienen puntuacion = 1 (osease, las mejores) Eso implica que el campo estado ha de pertenecer a la tabla RESPUESTAS, y no a TEMAS (como habia indicado con el alias. El alias es la letrita con la qeu nombramos a las tablas, por si no lo sabes)

Si deseas ordenar, bastara con añadirle al final de la select la clausula ORDER BY
ej: ORDER BY b.descripcion_tema.


(como ves la segunda consulta qeu propones no sirve de nada) Ralentizas infinito.





Respecto a la vista que comectas. Para qué haces una vista de un campo de una única tabla.

Si quieres optimizar, create la vista en la que ya tienes los datos de lso temas mas valorados.

En este caso, la select qeu tienes que usar para la vista seria algo asi (depende de tus primarys keys, etc etc etc)


Select a.id_tema, a.desc_tema, b.valoracion
from temas a, valoraciones b
where b.id_tema=a.ide_tema
order by b.valoraciones


Más o menos te queda claro?????

Si no, me dices, y e lo explico con mas calma.



Sólo una cosa, has de trabajr un poco más con el tema del SQL, porqeu el problema lo tienes con la generacion y creacion de las SLELECT'S.

Así qeu ya sabes, dedícale una horita, y verás que obtendrás buenos resultados.

[sataca]

Excelente respuestas.... la verdad es que soy un mandril con las querys y debería ponerme en serio con ellas........ a ver si lo hago de una vez.

Lo de usar una vista, tienes toda la razón. Aqui la uso en plan "mira, he aprendido a hacer vistas pero las uso sin ningun sentido ni creterio":

Mañana me miraré con calma tu respuesta, pero creo que es justo lo que necesito.


saludos

[javi_cassi]

Perfecto entonces.
Como te comentaba, fíjate bien en como haces las JOIN s de las tablas
(por cierto, la selects que te he hecho funcionan para ORACLE, si usas otras bbdd serán 90% similares, pero cambiarán algunas cosillas)

Respecto a la vista, efectivamente, habías creado una vista para nada.
La que yo tepropongo está bien, pero al ser tan pequeña la select casi ni es necesaria, pero bueno, eso allá tu.

Y especto a las SELECTS, pues sí, te diría y aconsejaría que les dedicases tiempo, porque son cosa muy muy importante. Y una vez que les tomas el pulso suelen ser muy muy fáciles (aunque ahroa te parezcan difíciles de enteder, etc et cetc)

Para lo qeu quieras, acá estamos, pero ESTUDIA :)

[sataca]

Pues la verdad es que no he conseguido nada.

El caso es, por ejemplo en los temas más valorados. Un usuario solo puede valorar una vez, asi que en la tabla VALORACIONES guardo el id_usuario,id_tema y la valoracion. Y hago un SUM(valoracion).

No veo la manera de hacerlo en una sola consulta.
Por ejemplo SELECT DISTINCT (
a.id_tema
), b.titulo, SUM( a.valoracion )
FROM VALORACIONES a, TEMAS b
WHERE a.id_tema = b.id
Me devuelve un solo tema (cuando en realidad hay 2) y la valoracion es el total de la tabla.

Y esta SELECT DISTINCT (
a.id_tema
), b.titulo
FROM VALORACIONES a, TEMAS b
WHERE a.id_tema = b.id me devuelve los 2 temas que hay, pero no su valoración.

No sé si me explico bien....

Te pongo la estructura de la BD para ver si entiendes el problema (no hagas caso a las foreign keys, he de retocarlas):

CREATE TABLE IF NOT EXISTS `TEMAS` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_usuario` int(10) unsigned NOT NULL,
`id_categoria` int(10) unsigned NOT NULL,
`titulo` text NOT NULL,
`entradilla` text NOT NULL,
`texto` blob NOT NULL,
`fecha` datetime NOT NULL,
`estado` tinyint(45) NOT NULL,
`destacado` tinyint(4) NOT NULL,
`tags` text NOT NULL,
`leido` int(11) NOT NULL,
PRIMARY KEY USING BTREE (`id`,`id_usuario`,`id_categoria`),
KEY `FK_OPINIONES_1` (`id_usuario`)
)
CREATE TABLE IF NOT EXISTS `VALORACIONES` (
`id` bigint(20) NOT NULL auto_increment,
`id_tema` bigint(20) NOT NULL,
`id_usuario` bigint(20) NOT NULL,
`valoracion` int(11) NOT NULL,
`fecha` datetime NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE IF NOT EXISTS `RESPUESTAS` (
`id` int(10) unsigned NOT NULL auto_increment,
`id_tema` int(10) unsigned NOT NULL,
`id_usuario` int(10) unsigned NOT NULL,
`titulo` text NOT NULL,
`texto` blob NOT NULL,
`fecha` datetime NOT NULL,
`estado` tinyint(4) NOT NULL,
PRIMARY KEY (`id`,`id_tema`,`id_usuario`),
KEY `FK_RESPUESTAS_1` (`id_tema`,`id_usuario`),
KEY `FK_RESPUESTAS_2` (`id_usuario`)
)

[sataca]

Creo que ya lo he resuelto, me faltaba el group by:

SELECT DISTINCT (
a.id_tema
), COUNT( a.id )
FROM RESPUESTAS a, TEMAS b
WHERE b.id = a.id_tema
GROUP BY id_tema



SELECT DISTINCT (
a.id_tema
), SUM( a.valoracion ) , b.titulo
FROM VALORACIONES a, TEMAS b
WHERE a.id_tema = b.id
GROUP BY id_tema

[javi_cassi]

Ok!!!

Como ya decias que estaba listo lo h mirao un poco por encima.
y Sí, le faltaba el group by, que ha de usar siempre que muestras funciones de grupo.

buen trabajo. Ya sabes, a segui curradno ;)