SQL - Problema montar un query

Problema montar un query

Publicado por kyo (8 intervenciones) el 01/08/2013 20:24:08

Llevo 4 días comiéndome la cabeza, a ver si logro explicarme. Tengo la siguiente tabla para una especie de servicio de mensajería:

[ id ] [ usuario_origen ] [ usuario_destino ] [ fecha ] [ mensaje ] [ visto_destino ]

Objetivo: QUIERO SACAR LOS ÚLTIMOS 10 ID CON LOS QUE HA CONTACTADO UN USUARIO.
Hay tres escenarios posibles:
- Usuario activo es receptor
- Usuario activo es emisor
- Usuario activo es receptor y emisor (en diferentes registros).

El problema es que el user activo puede estar en ambos lados ,usuario_origen y usuario_destino , pero desde que aparezca en uno de esos lados ya el otro usuario no debe volver a tenerse en cuenta!!!!

Condiciones: Sea fácil usar el LIMIT para ir haciendo búsquedas por bloques, los 10 últimos, los 20 últimos, ....

Actualmente tengo una solución que sabía que era 'mala' pero efectiva y resulta que no, es la siguiente:

1ª) Hago un conteo total de los ids únicos:

consulta_idsUnicos =" SELECT id FROM ".$tablabd_usuarios." WHERE id in
(SELECT usuario_origen
FROM ".$tablabd_mensajes."
WHERE usuario_destino = ".$id_usuario_activo." GROUP BY usuario_origen)

UNION

(SELECT usuario_destino
FROM ".$tablabd_mensajes."
WHERE usuario_origen = ".$id_usuario_activo." GROUP BY usuario_destino);";

2º) Uso ese total para añadir un LIMIT a la misma consulta y así conseguir los últimos:

1º] $comienzoLimit = $total_ids_unicos - $total_fichas_mostrar;

2º] $consulta_fichas = " SELECT id FROM ".$tablabd_usuarios." WHERE id in
(SELECT usuario_origen
FROM ".$tablabd_mensajes."
WHERE usuario_destino = ".$id_usuario_activo." GROUP BY usuario_origen)

UNION

(SELECT usuario_destino
FROM ".$tablabd_mensajes."
WHERE usuario_origen = ".$id_usuario_activo." GROUP BY usuario_destino)

LIMIT $comienzoLimit, $total_fichas_mostrar;";

PENSABA QUE LO TENÍA RESUELTO, mal resuelto pero resuelto, pero no. Lo que pasa es que tengo los ids ordenados por el PRIMER mensaje enviado y no por los últimos mensajes recibidos.

Así que me salen los últimos contactos NUEVOS que ha tenido el usuario y no los últimos contactos sean los que sean.

Si hay un usuario que le mandó un mensaje hace un mes y vuelve a enviar uno ahora mismo, este usuario no está posicionado en el último puesto sino que aparece ordenado cuando envió el primer mensaje hace un mes.

No sé si se pueden aprovechar las consultas anteriores o hacer borrón y cuenta nueva!!! Necesito ayuda llevo muchos días perdidos :(

GRACIAS!

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Problema montar un query

Publicado por kyo (8 intervenciones) el 01/08/2013 20:45:30

Por cierto, acepto otra estructura de la tabla si fuera necesario! Saludos

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 1

Val: 2.542

Problema montar un query

Publicado por Isaias Islas Gonzalez (1921 intervenciones) el 01/08/2013 20:56:16

¿Version de motor de base de datos?

Valora esta respuesta

Comentar

Problema montar un query

Publicado por kyo (8 intervenciones) el 01/08/2013 21:19:43

upsss me pillas.

uso localhost será una de las últimas versiones. Por cierto, estoy hablando de programación php ?¿ :/

Valora esta respuesta

Comentar

Pos: 2

Val: 806

Problema montar un query

Publicado por leonardo_josue (1173 intervenciones) el 02/08/2013 23:10:47

Hoa kyo:

A qué te refieres con que usas localhost??? jejejejejeje sinceramente creo que no tienes idea de lo que colocaste. En redes, localhost es un nombre reservado y corresponde a la IP 127.0.0.1...

http://es.wikipedia.org/wiki/Localhost

No tiene nada que ver con DBMS o lo que e lo mismo motores de base de Datos:

http://es.wikipedia.org/wiki/Sistemas_gestores_de_bases_de_datos

Si estás trabajando con PHP, podría apostar una ronda de cervezas a que estás trabajando con MySQL... sin embargo no es correcto que incluyas código de PHP ni de ningún otro lenguaje de programación en este foro. Debes enfocarte sólo en el código SQL. No me quedó muy clara tu explicación, igual y es conveniente que nos coloques algunos datos de ejemplo para saber exactamente qué es lo que necesitas.

Partiendo de la premisa:

QUIERO SACAR LOS ÚLTIMOS 10 ID CON LOS QUE HA CONTACTADO UN USUARIO.

A qué te refieres con ID's??? tu tabla tiene un campo ID por lo que podría suponer que se trata de este campo... aunque más bien, lo que quieres es obtener el los últimos 10 usuarios con los que se ha contactado un usuario en específico... correcto?

supongamos este ejemplo:

mysql> SELECT id, usuario_origen, usuario_destino, fecha

    -> FROM tabla;

+------+----------------+-----------------+------------+

| id   | usuario_origen | usuario_destino | fecha      |

+------+----------------+-----------------+------------+

|    1 | uno            | dos             | 2013-07-28 |

|    2 | tres           | cuatro          | 2013-07-28 |

|    3 | cuatro         | uno             | 2013-07-29 |

|    4 | uno            | uno             | 2013-07-30 |

|    5 | cinco          | tres            | 2013-08-01 |

|    6 | siete          | siete           | 2013-08-01 |

|    7 | uno            | tres            | 2013-08-01 |

|    8 | dos            | cuatro          | 2013-08-02 |

|    9 | uno            | cinco           | 2013-08-02 |

|   10 | cuatro         | uno             | 2013-08-02 |

+------+----------------+-----------------+------------+

10 rows in set (0.00 sec)

Supongamos que queremos buscar los mensajes del usuario UNO, tendríamos algo como esto:

mysql> SELECT id, usuario_origen, usuario_destino, fecha

    -> FROM tabla

    -> WHERE

    ->    usuario_origen = 'uno' OR

    ->    usuario_destino = 'uno'

    -> ORDER BY fecha DESC;

+------+----------------+-----------------+------------+

| id   | usuario_origen | usuario_destino | fecha      |

+------+----------------+-----------------+------------+

|    9 | uno            | cinco           | 2013-08-02 |

|   10 | cuatro         | uno             | 2013-08-02 |

|    7 | uno            | tres            | 2013-08-01 |

|    4 | uno            | uno             | 2013-07-30 |

|    3 | cuatro         | uno             | 2013-07-29 |

|    1 | uno            | dos             | 2013-07-28 |

+------+----------------+-----------------+------------+

6 rows in set (0.00 sec)

Ahora buen, supongamos que queremos obtener los últimos tres "contactos" que ha tenido el usuario... en este caso serian los usuariso CINCO, CUATRO y TRES... esto podríamos obtenerlo así:

mysql> SELECT IF(usuario_origen = 'uno', usuario_destino, usuario_origen)

    -> FROM tabla

    -> WHERE

    ->    usuario_origen = 'uno' OR

    ->    usuario_destino = 'uno'

    -> ORDER BY fecha DESC

    -> LIMIT 3;

+-------------------------------------------------------------+

| IF(usuario_origen = 'uno', usuario_destino, usuario_origen) |

+-------------------------------------------------------------+

| cinco                                                       |

| cuatro                                                      |

| tres                                                        |

+-------------------------------------------------------------+

3 rows in set (0.00 sec)

Es decir, en el IF lo que hago es verificar si el usuario es el origen o el destino, para considerar el opuesto... en caso de que sea ambos, pues de cualquier manera aparecerá en la lista.

No sé si esto te pueda servir. sino, postea algunos datos de ejemplo y con gusto te ayudamos a afinar la consulta.

Saludos
Leo.

Valora esta respuesta

Comentar

Problema montar un query

Publicado por kyo (8 intervenciones) el 02/08/2013 23:35:01

Hola Leo, gracias por la molestia responder aún con mi 'torpeza' en la pregunta.

Cierto, mi pregunta es orientada a programacion php pero realmente está centrada en la consulta de mysql, por ello me dió x pregintar aquí ;)

Y bueno creo que acertaste con la solución, desconocía la posibilidad de usar ese IF dentro del SELECT.

Tengo q probarlo mañana y te confirmo. Sinceramente gracias.

Valora esta respuesta

Comentar

Problema montar un query

Publicado por kyo (8 intervenciones) el 03/08/2013 13:24:43

Buenos días,

Leo he utilizado la consulta que me propusiste anteriormente, adaptando una cosita para el php (desde mi desconocimiento):

SELECT usuario_origen, usuario_destino,
IF (usuario_origen = ".$id_usuario_activo.", ....

Simplemente para poder acceder a los datos porque de otra forma no sé hacerlo.
El caso es que así, no obtengo el resultado que tú has tenido, y al final tengo usuarios con los que ha contactado REPETIDOS.

Puesto a entender la lógica de tu propuesta no sé como esa consulta es capaz de decir algo así como:
"voy a buscar los registros en los que el usuario activo esté en origen o en destino, PERO ADEMÁS cogeré al usuario que está al otro lado y , si se da un resultado igual o inverso que incluya a estos dos usuarios, no lo voy a seleccionar otra vez"

Creo que ese 'PERO ADEMÁS' formulado anteriormente no es tan fácil de solucionar

Estoy pensando optar por la opción 'bruta', y es añadir una columna más denominada por ejemplo COMUNICACION y que se componga de la siguiente forma:

[COMUNICACION] => 'usuario_menor-usuario_mayor"

Sea cual sea la comunicación establecida me quedará algo del siguiente estilo:

[id] [usuario_origen] [usuario_destino] [comunicacion]

[1] [uno] [dos] [uno-dos]
[2] [tres] [cuatro] [tres-cuatro]
[3] [cinco] [tres] [tres-cinco]
[4] [dos] [uno] [uno-dos]
[5] [dos] [uno] [uno-dos]
[6] [uno] [dos] [uno-dos]

Como los ids de usuarios son únicos, la columna resultante también será única. Y será igual ya sea el usuario activo origen o destino.

Hasta que encuentre una solución directa, creo que utilizaré esa columna para hacer DISTINC y ordenarlo por fecha.

Muchísimas gracias por la ayuda y seguiré dándole vueltas a ver.... :)

Valora esta respuesta

Comentar

Problema montar un query

Publicado por kyo (8 intervenciones) el 03/08/2013 14:18:18

Pues estoy en el mismo problema después de lo anterior, añado el campo que dije y hago esta consulta:

$consulta_fichas =" SELECT comunicacion FROM ".$tablabd_mensajes."
WHERE
(usuario_origen = ".$id_usuario_activo." or
usuario_destino = ".$id_usuario_activo.")
GROUP BY comunicacion
ORDER BY id DESC
LIMIT 30;";

El caso es el mismo, con el DISTINCT o el GROUP BY me toma el orden de la primera vez que apareció ese campo en la BASE DE DATOS!!

Pero lo que yo quiero es como empezar a leer la base de datos por detrás !!!!

Que me haga lo mismo pero me distinga la ÚLTIMA VEZ que aparece ese campo en la base de datos!

arrrrghhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh!! "!$%"·%"·$$"·$!! me estoy volviendo loco! jajajaj ;)

Valora esta respuesta

Comentar

Pos: 2

Val: 806

Problema montar un query

Publicado por leonardo_josue (1173 intervenciones) el 05/08/2013 15:47:06

Hola de nuevo:

Y por qué no simplemente agregas un DISTINCT a la consulta que puse:

mysql> SELECT id, usuario_origen, usuario_destino, fecha

    -> FROM tabla;

+------+----------------+-----------------+------------+

| id   | usuario_origen | usuario_destino | fecha      |

+------+----------------+-----------------+------------+

|    1 | uno            | dos             | 2013-07-28 |

|    2 | tres           | cuatro          | 2013-07-28 |

|    3 | cuatro         | uno             | 2013-07-29 |

|    4 | uno            | uno             | 2013-07-30 |

|    5 | cinco          | tres            | 2013-08-01 |

|    6 | siete          | siete           | 2013-08-01 |

|    7 | uno            | cuatro          | 2013-08-01 |

|    8 | dos            | cuatro          | 2013-08-02 |

|    9 | uno            | cinco           | 2013-08-02 |

|   10 | cuatro         | uno             | 2013-08-02 |

+------+----------------+-----------------+------------+

10 rows in set (0.00 sec)

mysql> SELECT IF(usuario_origen = 'uno', usuario_destino, usuario_origen)

    -> FROM tabla

    -> WHERE

    ->     usuario_origen = 'uno' OR

    ->     usuario_destino = 'uno'

    -> ORDER BY fecha DESC

    -> LIMIT 3;

+-------------------------------------------------------------+

| IF(usuario_origen = 'uno', usuario_destino, usuario_origen) |

+-------------------------------------------------------------+

| cinco                                                       |

| cuatro                                                      |

| cuatro                                                      |

+-------------------------------------------------------------+

3 rows in set (0.00 sec)

mysql> SELECT

    ->     DISTINCT IF(usuario_origen = 'uno', usuario_destino, usuario_origen)

    -> FROM tabla

    -> WHERE

    ->     usuario_origen = 'uno' OR

    ->     usuario_destino = 'uno'

    -> ORDER BY fecha DESC

    -> LIMIT 3;

+-------------------------------------------------------------+

| IF(usuario_origen = 'uno', usuario_destino, usuario_origen) |

+-------------------------------------------------------------+

| cinco                                                       |

| uno                                                         |

| cuatro                                                      |

+-------------------------------------------------------------+

3 rows in set (0.00 sec)

Haz la prueba y nos comentas

Saludos
Leo.

Valora esta respuesta

Comentar

Problema montar un query

Publicado por kyo (8 intervenciones) el 05/08/2013 18:31:53

Pero así conseguiré 'leer por detrás la bd'?? (es q el DISTINCT o GROUP BY me estaba detectando la primera vez q aparece en la bd y no la última vez q aparece:(. )

tus ultimos resultados no los cuadro :/ ...

Valora esta respuesta

Comentar

Pos: 2

Val: 806

Problema montar un query

Publicado por leonardo_josue (1173 intervenciones) el 05/08/2013 19:50:29

Hola de nuevo:

Pero así conseguiré 'leer por detrás la bd'??

Esto lo haces con el ORDER BY fecha DESC, es decir, ordenas tus registros del más reciente al más antiguo, por lo tanto estás "leyendo" la B.D. de manera inversa a como se dieron de alta... ¿no es esto lo que necesitas?... ¿en donde está el problema entonces?.

Igual y es conveniente que nos pongas tus datos y qué es lo que está mal... desde mi punto de vista el ejercicio que pongo (a menos con los datos "inventados") funciona según lo que pides, pero estos datos son ficticios, postea TU PROPIO EJEMPLO y dinos qué está mal.

Saludos
Leo.

Valora esta respuesta

Comentar

Problema montar un query

Publicado por KYO (8 intervenciones) el 07/08/2013 21:16:32

Buenas Leo, no te he respondido antes porque quería volver a probarlo antes de confirmar lo que suponía.

Quiero puntualizar, aunque supongo que no, que este 'error' (deficiencia de resultado esperado) espero que no tenga que ver con que yo lo estoy usando en programación PHP y quizás el fallo sea en la sintaxis de la escritura de mi query etc...

Por esto me vas a permitir escribir lo que yo tengo hecho en php:

$consulta_fichas = " SELECT DISTINCT comunicacion,
IF(usuario_origen = ".$id_usuario_activo.", usuario_destino, usuario_origen)
FROM ".$tablabd_mensajes."
WHERE
usuario_origen = ".$id_usuario_activo." OR
usuario_destino = ".$id_usuario_activo."
ORDER BY fecha DESC
LIMIT 20;";

Se diferencia con lo que tú me habías puesto en que después del DISTINCT tengo que añadir el campo que quiero que 'seleccione' para después poder tratarlo.

Al final estoy usando el campo comunicación como te expliqué en un post anterior ( comunicacion => usuario_menor-usuario_mayor ) si logro arreglar todo esto ya veré si lo quito y vuelvo a lo del inicio.

La tabla que tengo es esta:

[id] [usuario_origen] [usuario_destino] [comunicacion] [fecha]

[01] [62] [83] [62-83] ... (ordenados por fecha y por id)
[02] [62] [80] [62-80]
[03] [80] [62] [62-80]
[04] [62] [80] [62-80]
[05] [80] [62] [62-80]
[06] [83] [62] [62-83]
[07] [62] [83] [62-83]
[08] [83] [62] [62-83]
[09] [62] [83] [62-83]
[10] [80] [62] [62-80]
[11] [80] [62] [62-80]
[12] [80] [62] [62-80]
[13] [80] [62] [62-80]
[14] [85] [62] [62-85]
[15] [86] [62] [62-86]
[16] [62] [87] [62-87]
[17] [62] [80] [62-80]
[18] [80] [62] [62-80]

Mi usuario activo es el 62 y quiero sacar el campo COMUNICACION ordenado por los últimos contactos que ha tenido el cliente:

Si hago la anterior búsqueda SIN el DISTINCT tengo el siguiente resultado (lo esperado):

62-80
62-80
62-87
62-86
62-85
62-80
62-80
62-80
62-80
62-83
62-83
etc...

Si uso el DISTINC, para evitar las repeticiones, tengo el siguiente resultado (que no es el esperado):

62-87
62-86
62-85
62-80
62-83

El dato "62-80" debería aparecer en primer lugar, es decir, último contacto realizado. Pero no, aparece ordenado en el momento que aparece por PRIMERA VEZ dentro de la base de datos.

Ese es mi dilema! ;)

Valora esta respuesta

Comentar

ordenar datos sql

Referencias cruzadas