selects y counts anidados

[destor77]

Buenas tengo las siguientes tablas:

users
id
nombre
apellido

equipo
id
user_id
nombre_equipo

compras
id
id_user
id_producto

producto
id_producto
precio
estado (1-usado, 0-no usado)

rankging
id_equipo
id_fecha
puntos

Bien lo que necesito obtener es todos los datos de los usuario y:
1- cantidad de productos comprados no usados
2- total de puntos obtenidos en la fecha
3- total de puntos (la suma de todos los puntos de todas las fechas)

Lo que nose como hacer es el tema de los counts para obtener los puntos totales y la cantidad de productos comprados sin unsar porque no son los detalles de un equipo solamente, sino que estos datos se tiene que mostrar para todos los equipos al mismo tiempo, asi que no se como sería el where para los counts.
Espero que se haya entendio la duda, sino trato de explicarla un poco mejor.
Salu2 y desdee ya muchas gracias......

[jurena]

No nos dices nada de si los resultados en las dos primeras son para equipo o para usuario. Sea lo que sea sólo tienes que agrupar o por equipo o por usuario y añadir un inner join con la tabla de equipos, si es para equipo.


1) Select e.nombre_equipo, COUNT(p.id_producto) totalprodnousados FROM equipo e INNER JOIN users u ON e.user_id = u.id INNER JOIN compras c ON c.id_user = u.id INNER JOIN producto p ON c.id_producto = p.id_producto WHERE p.estado = 0 GROUP BY e.id

2) imagino que id_fecha es un campo de tipo DATE… y que lo que buscas son los puntos de un día, el de hoy.
Select e.nombre_equipo, SUM(r.puntos) totpuntoshoy FROM equipo e INNER JOIN users u ON e.user_id = u.id INNER JOIN compras c ON c.id_user = u.id INNER JOIN producto p ON c.id_producto = p.id_producto INNER JOIN rangking r ON r.id_equipo = e.id WHERE p.id_fecha = CURDATE() GROUP BY e.id

3) Todos los puntos de un equipo
Select e.nombre_equipo, SUM(r.puntos)Totalpuntoshastahoy FROM equipo e INNER JOIN users u ON e.user_id = u.id INNER JOIN compras c ON c.id_user = u.id INNER JOIN producto p ON c.id_producto = p.id_producto INNER JOIN rangking r ON r.id_equipo = e.id GROUP BY e.id

No he probado nada

[destor77]

hola, perdona si me explique mal
esto es lo que tengo que devolver
Visualización: nombre de usuario, apellido, nombre y equipo. Además en la grilla se dispondrá de información extra como compras disponibles, último ingreso al sitio, puntos obtenidos en la última fecha, posición en última fecha y los puntos obtenidos en total del juego junto con la posición en la que se ubica.

Filtros:
- Se podrán ordenar los usuarios por los puntos obtenidos para poder ver los ganadores de la última fecha.
- Se podrá visualizar una tabla de posiciones de usuarios ordenando los mismos por puntaje total obtenido.
- También se podrán ordenar los usuarios según la cantidad de productos comprados.

Osea el campo id_fecha es un entero que hace de referencia de la tabla fechas (1,2,3,4,etc) cuya estructura es:
tabla_fecha
id_fecha
nombre
comienzo (date)
fin (date)
estado

Y basicamente sería algo como lo que me pusiste pero todo en una sola consulta y que al aplicar el filtro me haga el order by por lo que filtro....

quedo algo mas claro ahora?

salu2

[jurena]

nombre de usuario, apellido, nombre y equipo. Además en la grilla se dispondrá de información extra como compras disponibles (¿qué significa eso en las tablas que tienes, los productos comprados por el usuario y no usados?), último ingreso al sitio (me imagino que las fechas de inicio y fin), puntos obtenidos en la última fecha(eso por lo que tienes parece depender del id de esa tabla (si es que las insertaste siguiendo un orden de fecha), salvo que las fechas no se crucen, es decir, que no se monten las fechas una en otra; ¿cómo va lo de las fechas, servirá el orden del id o puede usarse el de la fecha de inicio, o el de fin?), posición en última fecha (¿te refieres a algo así como un ranking? para esto tendrás que usar programación y aunque se puede hacer con la base, complicaría mucho la cosa si hay que hacerlo todo en una consulta) y los puntos obtenidos en total del juego junto con la posición en la que se ubica.

Filtros:
- Se podrán ordenar los usuarios por los puntos obtenidos para poder ver los ganadores de la última fecha.
- Se podrá visualizar una tabla de posiciones de usuarios ordenando los mismos por puntaje total obtenido.
Estas dos ordenaciones parecen exigir dos consultas distintas o una consulta y una subconsulta. Piensa que sumas haciendo un where sobre la última fecha en una, y sin hacerlo en otra.
- También se podrán ordenar los usuarios según la cantidad de productos comprados.

¿Y por qué todo eso en una sola consulta? Parece que quieres resolver todo con una única consulta, y hacerlo exigiría subconsultas.

Acláranos estas cosas y trataremos de ayudarte.

Saludos, destor77.

[morior]

Permíteme un comentario, yo creo que lo tienes mal porque no has hecho un buen diseño de la base de datos. Yo quitaría la tabla de ránking y pasas los datos cada una a su tabla. En la tabla usuario le pones los puntos, y la fecha la pones en otra de las tablas, si se relaciona con los usuarios pues pon la fecha ahí también, si se relaciona con el equipo (porque ha jugado un partido o lo que sea) pues ponla en el equipo. Así te ahorras saltar tanto de tabla en tabla. Si lo miras bien, la tabla ránking no la necesitas porque la tabla usuario ya es un ránking natural. Te lo digo por experiencia, eso parece de un juego y es a lo que me dedico yo. Y bueno, yo el precio del producto se lo quitaría, seguro que luego tienes otra tabla donde relacionas el producto con su id donde pones las descripciones, imágenes, peculiaridades, precios... Yo la llamaría tabla de compra

users
id
nombre
apellido
puntos


equipo
id
user_id
nombre_equipo

compras
id
id_user
id_producto

compra
id_compra
id_producto // para relacionarlo con el producto
estado (1-usado, 0-no usado)

[destor77]

a ver si con esto se entiende mejor yo cuando entre al panel de administración del sitio y vaya a la opción user deberia salir algo asi:

id nombre equipo puntos ranking compras total puntos
1 ssss sdsds 23 66562 1 150
2 hhhh hhhhh 90 34 0 230
3 ggg hghgh 150 12356 10 500
4 eee gfgfgfg 23 55555 90 90
5 ffffff ewewe 23 456 0 80
121 zzz wwww 10 1 0 600
... ssss zzzzzz 60 950 0 70

La cuestion de hacer todo en una sola consulta es que si el usuario quiere ver la lista por compras tiene que ordenar todo el listado por la columna compras, y si lo hago en query separadas no podria aplicar el filtro.

Espero que se haya entendido un poco mas ahora.
SAlu2

[jurena]

Destor77,
eso se entiende, no te preocupes, pero yo veo problemas en la estructura, quizás porque no he entendido bien algo.
users
id
nombre
apellido

equipo
id
user_id ¿Qué entiendes por equipo? Si un usuario pertenece a un equipo y sólo puede pertenecer a uno, puedes tener el campo id_equipo en la tabla users; si puede pertenecer a varios equipos, tendrás que crear otra tabla llamada usersequipo con campos como id_usersequipo, id_user, id_equipo.
nombre_equipo

compras
id
id_user
id_producto (Si en una compra se pueden adquirir varios productos, tendrás que quitar este campo y añadir otra tabla comprasproducto con los campos id_compras, id_producto, y eliminar este id_producto de esta tabla)

producto
id_producto
precio
estado (1-usado, 0-no usado)

rankging
id_equipo
id_fecha
puntos Como morior te indica, tampoco veo yo clara la naturaleza de esta tabla. Cómo se asignan los puntos y por qué se asignan a un equipo y no al usuario. Explícanos algo más.

Respecto a lo otro que me dijiste sobre por qué hacer una consulta, te diré que tendrías la opción con tu programa de reunir en un array los datos y ordenar por los campos que quieras, pero eso es ya de programación, claro.

[destor77]

Juarena respondo tus preguntas

Iniciado por jurena

Destor77,

users
id
nombre
apellido

equipo
id
user_id ¿Qué entiendes por equipo? Si un usuario pertenece a un equipo y sólo puede pertenecer a uno, puedes tener el campo id_equipo en la tabla users; si puede pertenecer a varios equipos, tendrás que crear otra tabla llamada usersequipo con campos como id_usersequipo, id_user, id_equipo.
nombre_equipo

Un usario puede tener un equipo solamente, pero en la tabla equipos existen otros campos como arquero, def1,def2,def3,def4,medio1,medio2,medio3,medio4,me dio5,del1,del2,sup1,sup2,sup3. Por eso lo separamos en otra tabla porque tenerlo en una sola nos parecio redundante, en los campos que mencione recien van guardado los id de los jugadores que estan en otra tabla.

Iniciado por jurena

compras
id
id_user
id_producto (Si en una compra se pueden adquirir varios productos, tendrás que quitar este campo y añadir otra tabla comprasproducto con los campos id_compras, id_producto, y eliminar este id_producto de esta tabla)

producto
id_producto
precio
estado (1-usado, 0-no usado)

La tabla compras funciona como un historial de compras, ahí se guardarán todos los movimientos que tiene los usuarios, y en el mismo tambien se guarda el estado de la compra (si se completo o se cancelo) y por otro lado tengo la tabla productos como tu dices con el estado si fue usado o no.

Iniciado por jurena

rankging
id_equipo
id_fecha
puntos Como morior te indica, tampoco veo yo clara la naturaleza de esta tabla. Cómo se asignan los puntos y por qué se asignan a un equipo y no al usuario. Explícanos algo más.

Esta tabla es un historial de puntos que van ganando los equipos en cada fecha, se asigna al equipo porque son ellos los que los ganan, el usuario es el "dt" del equipo por eso no se lo asignamos a él. Lo puntos se asignan de acuerdo a como jugo cada jugador en el partido (basado en los partidos reales del futbol de brasil).

Iniciado por jurena

Respecto a lo otro que me dijiste sobre por qué hacer una consulta, te diré que tendrías la opción con tu programa de reunir en un array los datos y ordenar por los campos que quieras, pero eso es ya de programación, claro.

Si había pensado esto, pero estoy evaluando que es mas optimo si usar arreglos o las consulta sql. Para mas aclaración estoy armando un clon de "el gran dt" (http://www.grandt.clarin.com/html/login.html) de argentina para otro pais.

[eidavid]

mira yo con gusto te ayudo pero no entendi bien bien lo que ocupas,
mandame un correo al correo electronico mi correo es [email protected]

[jurena]

destor77,
mi duda ahora (creo que será la última) tiene que ver con esta aclaración tuya:

Un usario puede tener un equipo solamente, pero en la tabla equipos existen otros campos como arquero, def1,def2,def3,def4,medio1,medio2,medio3,medio4,me dio5,del1,del2,sup1,sup2,sup3. Por eso lo separamos en otra tabla porque tenerlo en una sola nos parecio redundante, en los campos que mencione recien van guardado los id de los jugadores que estan en otra tabla.

Esto te obliga a relacionar esa tabla equipos con los usuarios tantas veces como campos de posición en el juego haya (me refiero a arquero, def1, def2, def3, etc.). Esa estructura añade dificultades a cualquier consulta que tenga que ver con usuarios. Debería haber una tabla posicionjuego que tuviera los campos idposicionjuego, idjuego, iduser, idposicion,
y, claro, otra tabla con las posiciones del juego, donde incluirías, idposicion, posicion, cuyos valores serían arquero, def1, def2, def3, etc. Mediante índices únicos evitarás las repeticiones, y mediante la relación de esa tabla idposicionjuego podrás trabajar mucho mejor, creo, los datos.
Manda si quieres una estructura (recortada) con algunos datos y haremos pruebas.

[destor77]

Iniciado por jurena

destor77,
mi duda ahora (creo que será la última) tiene que ver con esta aclaración tuya:

Esto te obliga a relacionar esa tabla equipos con los usuarios tantas veces como campos de posición en el juego haya (me refiero a arquero, def1, def2, def3, etc.). Esa estructura añade dificultades a cualquier consulta que tenga que ver con usuarios. Debería haber una tabla posicionjuego que tuviera los campos idposicionjuego, idjuego, iduser, idposicion,
y, claro, otra tabla con las posiciones del juego, donde incluirías, idposicion, posicion, cuyos valores serían arquero, def1, def2, def3, etc. Mediante índices únicos evitarás las repeticiones, y mediante la relación de esa tabla idposicionjuego podrás trabajar mucho mejor, creo, los datos.
Manda si quieres una estructura (recortada) con algunos datos y haremos pruebas.

si yo fui partidario de esa opcion, pero como el modelo de la base de datos no fue realizado por mi, sino la gente que me contrato y al parecer no quieren hacer cambios en la misma, me veo obligado a usar ese model

[jurena]

Si existen dos tablas distintas, una equipo y otra equipos, y en la tabla equipo están todos los id de todos los usuarios que ocupan una posición en el juego, un id de user distinto por registro, podremos probar alguna consulta. Dinos si es así.