Detectar @menciones en una consulta sql

[50l3r]

Saludos

Necesito saber de que manera poder buscar en un campo varchar menciones de usuario. Por ejemplo.

A @john le ha gustado #juego_de_tronos

Con un like '%@john%' no me valdria dado que podria sacarme menciones como:


A @john_ander le ha gustado #juego_de_tronos


¿Alguien sabria un metodo eficaz? Gracias

[leonardo_josue]

Hola 50l3r:

Hace algún tiempo otro forista comentaba algo muy similar a lo que estás planteando... el problema corresponde más buen a un error de diseño de tus tablas... no es correcto guardes TEXTOS como lo que haces, si no lo que tienes que hacer es guardar ACCIONES asociadas a los usuarios... es decir, en lugar de tener una tabla así:

Código MySQL:

mysql> SELECT * FROM tabla;
+------+---------------------------------------------+
| id   | comentario                                  |
+------+---------------------------------------------+
|    1 | A @john le ha gustado #juego_de_tronos      |
|    2 | A @john_ande le ha gustado #juego_de_tronos |
|    3 | @john a comentado algo                      |
|    4 | @john_ande a comentado algo                 |
|    5 | @leo es amigo de @john                      |
|    6 | @leo es amigo de @john_ande                 |
|    7 | @john                                       |
|    8 | @john_ande                                  |
+------+---------------------------------------------+
8 rows in set (0.00 sec)
 

deberías tener un modelo con al menos tres tablas:

Código MySQL:

usuarios
idUsuario|nombreUsuario
1|@john
2|@john_ande
 
Acciones
1|Me gusta
2|Comentario
3|otra
 
AccionesUsuario
idAccionUsuario|idAccion|idUsuario
1|1|1
2|1|2
3|2|1
4|2|2
5|3|1
6|3|2
 

De esta evitas el uso de consultas tipo LIKE, las cuales por cierto SON TERRIBLEMENTE INEFICIENTES, lo cual puede comprometer el rendimiento del servidor.

Ahora bien, esto es por el lado del modelo de BD, sin embargo, SI TE ES IMPOSIBLE CAMBIAR TU MODELO, entonces la consulta se vuelve un poco más compleja. De entrara, permitir que se pueda capturar CUALQUIER cosa en un campo tipo TEXTO, el usuario a buscar puede aparecer al menos en 4 formas distintas (como en la tabla que pongo de ejemplo), es decir sólo, al inicio de la frase, en medio de la frase o al final de la frase... suponiendo que el separador siempre sea el espacio en blanco, podrías hacer algo como esto:

Código MySQL:

mysql> SELECT * FROM tabla;
+------+---------------------------------------------+
| id   | comentario                                  |
+------+---------------------------------------------+
|    1 | A @john le ha gustado #juego_de_tronos      |
|    2 | A @john_ande le ha gustado #juego_de_tronos |
|    3 | @john a comentado algo                      |
|    4 | @john_ande a comentado algo                 |
|    5 | @leo es amigo de @john                      |
|    6 | @leo es amigo de @john_ande                 |
|    7 | @john                                       |
|    8 | @john_ande                                  |
+------+---------------------------------------------+
8 rows in set (0.00 sec)
 
mysql> SELECT * FROM tabla
    -> WHERE
    ->    comentario = '@john' OR
    ->    comentario LIKE '@john %' OR
    ->    comentario LIKE '% @john %' OR
    ->    comentario LIKE '% @john';
+------+----------------------------------------+
| id   | comentario                             |
+------+----------------------------------------+
|    1 | A @john le ha gustado #juego_de_tronos |
|    3 | @john a comentado algo                 |
|    5 | @leo es amigo de @john                 |
|    7 | @john                                  |
+------+----------------------------------------+
4 rows in set (0.00 sec)
 

Lo cual es terrible

esto se complica aun más si el separador no es solo el espacio en blanco, y pudieras tener cosas como:

Código MySQL:

mysql> SELECT * FROM tabla WHERE id > 8;
+------+---------------------------------------+
| id   | comentario                            |
+------+---------------------------------------+
|    9 | A @john, @john_ande y @leo son amigos |
|   10 | A @john-@john_ande-@leo son amigos    |
|   11 | A @john|@john_ande|@leo son amigos    |
+------+---------------------------------------+
3 rows in set (0.00 sec)
 

Es decir utilizar un separador distinto al espacio, o permitir valores multivariados...


Insisto, si puedes cambiar tu modelo de BD, HAZLO y evitate dolores de cabeza.

Saludos
Leo.

[50l3r]

Muchas gracias leonardo_josue por tu tiempo.

Te enseño mi modelo de tabla:



Y contenido




Como ves, tengo un usuarioid que es el usuario al que le correponde ese campo. Pero imaginate que mi usuario nombra a otro, es decir, yo @50l3r inserto esto:

que bien nos lo pasamos jugando al #fifa_14_pc e @john...

Podria colocar infinidad de @usuarios y #metatags en un timeline

[gnzsoloyo]

Es que estás partiendo, como dice @leonardo_josue, de un error de diseño de sistema y de base de datos. Ese tipo sistemas no se administra de la forma en que lo estás haciendo.
Lee con atención lo que te explica, y trata de comprender mejor el fundamento de diseño de datos que te propone.
desde ya, el planteo que tu estas haciendo no es funcional para nada. Si de verdad quieres obtener un esquema útil, deberás modificarlo todo.

[50l3r]

Para un usuario el sitema de tablas es simple, pero en el momento que entra en juego varios nombres y metatags. ¿Como podria diseñarse la tabla?

[gnzsoloyo]

La tabla solamente, no. deberás rediseñar muy probablemente la base entera, y ya Leo te ha explicado claramente cómo es la idea.
¿QUé es lo que no se entiende?

[50l3r]

El timeline no es solamente de acciones. Un usuario podra escribir en su timeline lo que quiera y esto puede contener mas de un usuario.

Quiero decir que, yo poseo una tabla usuarios, una tabla timeline y asocio mis timelines con el usuario correspondiente, pero en el momento que entra en juego un texto aleatorio como:

hey @john, vamos a jugar a #cal_of_duty_4 que tengo ganas de matar a @fran.

no me sirve tener una tabla timeline_usuarios que tenga TimelineId y UsuarioId, porque despues no se sabria como poder hacer coincidir los usuarios con el texto puesto.

Es un tema dificil de explicar

[gnzsoloyo]

no me sirve tener una tabla timeline_usuarios que tenga TimelineId y UsuarioId, porque despues no se sabria como poder hacer coincidir los usuarios con el texto puesto.

Completamente errado.
En un esquema como el que te propone Leo, la acción se maneja por identificadores, pero eso no implica que no existan otros atributos, como por ejemplo, los mensajes intercambiados, las invitaciones, los eventos, etc.
Sino, ¿cómo crees que se manejan todos los sitios de redes sociales? ¿Por timelines como los que quires usar?
No. Se contruyen con un correcto esquema que soporte los timelines sin violar el paradigma relacional.
Si lo que quieres es una base d edatos que funcione bien, y pueda responder a tus necesidades, entonces no puedes tratarla como colección de datos. Tienes que diseñarla correctamente, pero en base a lo que que el sistema necesita.
Para proponerte un modelo, primero deberíamos entender bien la lógica de tu sistema, las entidades, las relaciones y los procesos de datos que se necesitan. Y para eso necesitaríamos bastante información...

Ahora bien, paa no alejarnos del problema, veamos lo que preguntabas al principio:

Necesito saber de que manera poder buscar en un campo varchar menciones de usuario. Por ejemplo.

A @john le ha gustado #juego_de_tronos

Con un like '%@john%' no me valdria dado que podria sacarme menciones como:


A @john_ander le ha gustado #juego_de_tronos

Bueno, eso no lo podrás jamás obtener con tu esquema, sino con una base de datos relacional bien diseñada... como te lo propone Leo.
Lo que queremos que entiendas es que la base debe almacenar ciertos datos en estado "puro", con lo que luego tu aplicación pueda construir :"A @john_ander le ha gustado #juego_de_tronos"
Es decir, no es la frase lo que se debe almacenar, es el hecho, la acción de que el usuario dijera qué le gusta. El resto se construye por programación.
¿Se va entendiendo?

No es un capricho nuestro. Se llama experiencia en BBDD.

Voy a ir un poco más lejos.
Esto que sigue es el esquema de una base de datos para una red social del juego "Gear of War", y es funcional:


Y eso, soportaría sin problema el manejo de timelines como los que quieres.

[50l3r]

Muchas gracias por vuestro tiempo.

Necesito tener la base de datos optimizada lo mejor posible, asi que cualquier mejora que me estais dando si es lo que necesito lo realizaré.

Nose si entendisteis bien, que el sistema del timeline no se dedica solo a tener eventos tipo:

a @john le gusta #jueg_de_tronos
a @john no le gusta #juego_de_tronos

Sino que al igual que en twitter, se puede colocar cualquier texto a eleccion del usuario

No veas @fran, a @john no le ha gustado @juego_de_tronos, no veo porque no.

¿Como enlazaria en un diseño de base de datos, un texto libre con sus correspondientes id de usuarios?

[gnzsoloyo]

¿Como enlazaria en un diseño de base de datos, un texto libre con sus correspondientes id de usuarios?

Eso lo haces por programación, en base a la lógica que implementas en la aplicación.
Los controladores de acceso a datos (clases donde realizas las peticiones a la base) lo que recolectas son los datos. Nada más. Es decir, allí obtienes los ID y nombres de "@john", y de "@fran", eventualmente con los ID de lo que le gusta y lo que no.
Es en los controladores de las vistas (formularios), o bien en los de mensajes, donde tomas el mensaje estandarizado para los casos (obviamente debes codificar los mensajes y los strings de composición de los mensajes a mostrar), como por ejemplo "A %1% no le gusta %2%.". Es entonces que antes de mostrar ese mensaje, en el codebehind de la vista, se produce el reemplazo de los parámetros por los valores obtenidos de la base.
Todo esto es lo manejan los programadores, y se realiza programando, pero programación es OFF-TOPIC en este foro (sólo tratamos lo referente a Bases de Datos y SQL, como puedes leer en las normas del foro).
Te recomiendo consultar en el Foro de tu lenguaje.
Es una metodología de trabajo muy común, conocida y hay muchos expertos en ese tema. Yo la conozco porque en la empresa que trabajo la aplicación principal está desarrollada en base al modelo MVC, que es el que te menciono.

[50l3r]

Claro, es verdad!! No se me habia ocurrido el remplazo de parametros en el string.

Lo que puedo realizar es lo siguiente:

timeline:
TimelineId, UsuarioId, TimelinePadre, Timeline, TimelineFecha

timeline_usuarios: (ej: @john)
TimelineId, UsuarioId

timeline_juego: (ej: #fifa_14_pc)
TimelineId, JuegoId


Y que el texto del timeline dentro de la tabla sea asi sea tal que asi:

a U[%1] le gusta J[%1] = a @john le gusta #fifa_14_pc

En la tabla timeline_usuarios estaria el id de timeline y el id de usuario, asi igual con los juegos.

¿Puede ser esta una idea viable?

[gnzsoloyo]

Y que el texto del timeline dentro de la tabla sea asi sea tal que asi:

a U[%1] le gusta J[%1] = a @john le gusta #fifa_14_pc

NO, NO, NO, NO....

Eso sigue siendo un diseño de tablas que no sólo no es funcional, sino que lo único que obtendrás es datos-basura...

Aplica un modelo de datos correctamente diseñado. Lo que planteas NO SIRVE para hacer consultas. Y por más vueltas que le des NO VA A SERVIR.

¿Cuál es el problema que tienes para hacer lo que te planteamos? ¿No lo entiendes el modelo relacional, no tienes práctica en diseño de bases de datos, o sencillamente no quieres modificar un diseño que ya existe, por más malo que sea (cosa que sin duda ES)?

[50l3r]

Iniciado por gnzsoloyo

Eso lo haces por programación, en base a la lógica que implementas en la aplicación.
Los controladores de acceso a datos (clases donde realizas las peticiones a la base) lo que recolectas son los datos. Nada más. Es decir, allí obtienes los ID y nombres de "@john", y de "@fran", eventualmente con los ID de lo que le gusta y lo que no.
Es en los controladores de las vistas (formularios), o bien en los de mensajes, donde tomas el mensaje estandarizado para los casos (obviamente debes codificar los mensajes y los strings de composición de los mensajes a mostrar), como por ejemplo "A %1% no le gusta %2%.". Es entonces que antes de mostrar ese mensaje, en el codebehind de la vista, se produce el reemplazo de los parámetros por los valores obtenidos de la base.
Todo esto es lo manejan los programadores, y se realiza programando, pero programación es OFF-TOPIC en este foro (sólo tratamos lo referente a Bases de Datos y SQL, como puedes leer en las normas del foro).
Te recomiendo consultar en el Foro de tu lenguaje.
Es una metodología de trabajo muy común, conocida y hay muchos expertos en ese tema. Yo la conozco porque en la empresa que trabajo la aplicación principal está desarrollada en base al modelo MVC, que es el que te menciono.

Me estas diciendo que en base de datos guarde: "A %1% no le gusta %2%." y te lo estoy repitiendo "a U[%1] le gusta J[%1]" y dices que no.

Me parece que esto ya no es un post de ayuda sino de criticas incontrolables hacia lo que piensas que tengo, es un modelo no relacional.

Tengo una base de datos de 25 tablas, con sus respectivas entidades correctamente relacionadas entre si.

Comentarios, Enlaces, Juegos, Categorias, Grupos de usuarios, Usuarios, Modos de juego, Plataformas. Todo perfectamente diseñado y enlazado con consultas lo mas optimizadas posibles que he podido.

Nos estamos llendo por las ramas. Lo unico que estoy solicitando es ayuda sobre como solventar esta situacion, que diseño tengo que seguir para la tabla de timeline nada mas, porque las demas tablas ya las tengo diseñadas correctamente.

[gnzsoloyo]

Me estas diciendo que en base de datos guarde: "A %1% no le gusta %2%." y te lo estoy repitiendo "a U[%1] le gusta J[%1]" y dices que no.

No. Lo que te estoy diciendo es que lo que guardas en una tabla que podríamos llamar "user_like", sería el ID del user (por ejemplo "@john"), y también el ID de qué le gusta (por ejemplo "@game_of_thrones"), con algún otro atributo que pueda necesitase.
pero en ningún momento te estoy diciendo que guardas en una tabla algo como "A %1% no le gusta %2%.". Eso no lo digo.
Lo que dije es:

Es en los controladores de las vistas (formularios), o bien en los de mensajes, donde tomas el mensaje estandarizado para los casos (obviamente debes codificar los mensajes y los strings de composición de los mensajes a mostrar), como por ejemplo "A %1% no le gusta %2%.". Es entonces que antes de mostrar ese mensaje, en el codebehind de la vista, se produce el reemplazo de los parámetros por los valores obtenidos de la base.

Es decir, los valores obtenidos son atómicos, no "timelines"
Ahora bien, ¿donde se guardan esos mensajes estandarizados?
Si quieres usar una tabla de mensajes para guardarlos, es tu decisión. Yo los guardaría en un XML, mucho más sencillo y simple de codificar, pero eso es tu decisión como desarrollador, no la mía.
Lo que sí quiero que quede claro es que en la tabla sólo van los IDs de quienes participan de esa acción de "gustar". No la cadena de texto del "timeline", porque esa se debe construir en forma dinámica.

Lo unico que estoy solicitando es ayuda sobre como solventar esta situacion, que diseño tengo que seguir para la tabla de timeline nada mas, porque las demas tablas ya las tengo diseñadas correctamente.

Ya te dijimos que para solventarlo de modo que puedas hacer consultas y que estas seanm efectivas y eficientes, no puedes guardar los timelines como cadenas. SI lo haces, olvidate de efectividad y eficiencia. No existirá.

respecto a lo bien construido o no... si no vemos el diagrama, y sólo tenemos como evidencia ese "timeline" y lo que implica de acuerdo a tu descripción, nuestra experiencia nos hace inferir que hay errores de diseño. Si no los hubiera, esos datos de "timeline" no existirían guardados así como lo haces.

¿Se entiende el razonamiento?

[50l3r]

Olvida que los mensajes son estandarizados. Estoy hablando desde un principio en que cada persona pueda formar su propio texto como en twitter mismo. Eso no lo puedo guardar en un xml porque evidentemente es imposible.

La gente escribe en un textbox cualquiera y nombra a @gente y a #juegos. Como si de un sistema de comentarios se tratase.

[gnzsoloyo]

Eso que describess ES un sistema de comentarios, más allá de que ese comentario contenga identificadores...
Entonces debes analizar el mensaje para obtener el dato... eso está un poco más allá de la base de datos y lo único que te serviría para obtener menciones dentro de campos VARCHAR, de modo seguro es usar indices FULLTEXT, que sólo aplican a las tablas de tipo MyISAM.
Otra solución práctica no le veo. No puedes usar LIKE ni REGEXP(), porque te dará una enorme cantidad de resultados falsos.
En ese punto y si lo que quieres es obtener las menciones de un identificador dado, dentro de un mensaje, no veo otro camino.

Indices FULLTEXT y tablas MyISAM.

[50l3r]

Ahora que ya logre hacerme entender, ¿Podrias decirme de que manera podria alojar los datos en mi base de datos? ¿el metodo que te comente anteriormente me serviria?

No importa que sea mas o menos complejo de realizar, como dices, lo que busco es la mayor optimizacion

[gnzsoloyo]

Necesitaría conocer el sistema mas o menos completo, para darte consejos de diseño de datos. No puedo hablar en el aire, y en sobre lo poco que se entiende de tu estructura de base.

En cuanto a lo que necesitas para usar búsquedas como las que describo, no hay mucho más de lo que ya dije: Las tablas deben ser si o sí MyISAM (no pueden usarse InnoDB), se deben definir indices FULLTEXT sobre ese campo, y en las consultas se usa MATCH() AGAINST(), cuya descripción puedes leer detalladamente en el manual.


MySQL 5.0 Reference Manual :: 12 Funciones y operadores :: 12.7 Funciones de búsqueda de texto completo (Full-Text)

Te recomiendo leer con cuidado el tema de restricciones de ese tipo de busquedas, así como comprender correctamente el concepto de "relevancia" que se aplica: Es más relevante, mientras menos veces aparece el texto en la tabla.

[50l3r]

Iniciado por gnzsoloyo

Necesitaría conocer el sistema mas o menos completo, para darte consejos de diseño de datos. No puedo hablar en el aire, y en sobre lo poco que se entiende de tu estructura de base.

En cuanto a lo que necesitas para usar búsquedas como las que describo, no hay mucho más de lo que ya dije: Las tablas deben ser si o sí MyISAM (no pueden usarse InnoDB), se deben definir indices FULLTEXT sobre ese campo, y en las consultas se usa MATCH() AGAINST(), cuya descripción puedes leer detalladamente en el manual.


MySQL 5.0 Reference Manual :: 12 Funciones y operadores :: 12.7 Funciones de búsqueda de texto completo (Full-Text)

Te recomiendo leer con cuidado el tema de restricciones de ese tipo de busquedas, así como comprender correctamente el concepto de "relevancia" que se aplica: Es más relevante, mientras menos veces aparece el texto en la tabla.

Me suena de haber utilizado esta funcion una vez, pero tengo dudas.

¿Al igual que en el like, si realizo una busqueda por @jhon, me lo encontraria por ejemplo en este comentario?

#fifa_14 fue marcado por finalizado como @jhon.

Es decir, el ".", "," despues de lo buscado.


Fueraparte de la busqueda, al insertarlo bien como me dices, deberia de hacerlo de esta manera no?

Luego, lo que hago es coger el texto y modificarlo para generar los correspondientes enlaces.