Seleccionar valor de una tabla u otra para ordenar

[gr0uch0mars]

Tengo una base de datos de libros con dos tablas, una con la lista de libros que tengo, y la otra con la lista de títulos alternativos (traducciones) de esos libros:
- Books: ID, title (título original del libro)
- Books_titles: book_id, book_title (título traducido a otro idioma), title_lang (código de 2 letras)

Quiero poder sacar una lista de títulos de libros ordenados por título en un idioma específico, es decir, si la quiero ordenar por los títulos en Español, me ordene la tabla Books según los títulos (Books_titles.book_title) en español (title_lang = 'es'). Pero dado que no todos los libros en Books tienen un título traducido (es decir, no tienen entrada en Books_titles), si no hay entrada en Books_titles me tendría que coger para ordenar el campo Books.title.

Sé que es una mezcla engorrosa, pero no se me ocurre otra idea para poner los títulos traducidos, y tampoco sé cómo listar en un determinado idioma.

Por ahora tengo estos problemas:

===> No selecciona los libros que no tengan ese title_lang (y debería seleccionarlos, pero poniendo Books.title como título a ordenar):

SELECT books.id, IF(books_titles.title_lang = 'fr',books_titles.book_title,books.title) AS titulo
FROM `books`, `books_titles`
WHERE books_titles.`title_lang` = 'fr' AND books.id = books_titles.book_id
GROUP BY books.id
ORDER BY titulo

ó

SELECT books.*, IF(books_titles.title_lang = 'fr', books_titles.book_title, books.title) AS titulo
FROM books
JOIN books_titles ON books.id=books_titles.book_id AND books_titles.title_lang = 'fr'
GROUP BY books.id

ó

SELECT books.*, books_titles.book_title
FROM books
JOIN books_titles ON books.id=books_titles.book_id AND books_titles.title_lang = 'fr'
GROUP BY books.id

===> No obtiene el Books.ID ni Books.title (pone NULL), al no haber titulo en el idioma 'fi', pero saca los libros correctamente:

SELECT books.id, books.title, books_titles.book_title
FROM books
RIGHT JOIN books_titles ON books.id=books_titles.book_id AND books_titles.title_lang = 'fi'
GROUP BY books.id


===> Para cada título en books_titles hace el IF, y saca cuatro veces el Book.ID = '1' si hay cuatro traducciones del título en Books_titles:

SELECT books.id, books.title, IF(books_titles.title_lang = 'fi', books_titles.book_title, books.title) AS titulo
FROM books, books_titles
WHERE books.id = books_titles.book_id
ORDER BY titulo

Llevo mucho tiempo intentando encontrar una solución. Si alguien sabe cómo resolverlo, o tiene alguna idea de cómo almacenar los títulos traducidos de otra forma más fácil/lógica/útil, bienvenido sea.

Gracias.

[gnzsoloyo]

Quiero poder sacar una lista de títulos de libros ordenados por título en un idioma específico, es decir, si la quiero ordenar por los títulos en Español, me ordene la tabla Books según los títulos (Books_titles.book_title) en español (title_lang = 'es'). Pero dado que no todos los libros en Books tienen un título traducido (es decir, no tienen entrada en Books_titles), si no hay entrada en Books_titles me tendría que coger para ordenar el campo Books.title.

El problema es resolver un criterio doble de ordenamiento, en uno de los cuales puede que jamás se pudiese ordenar, por cuanto el campo parece que puede presentarse como NULL, cosa que es inmanejable para un ordenamiento (¿cómo ordenas la nada?).
Sin embargo existe una posibilidad: Ordenar sobre la base de los valores dados en el SELECT con el uso de funciones. Esto permitiría establecer una jerarquía de ordenamiento: Primero se ordenarán los registros que tengan algo en books_titles , y luego lo que no tenga correspondencia en books. Esto implica que los datos deben tomarse en realidad de books como base y verificar si la correpondencia es NULL:
Esta sentencia:

Código sql:

SELECT
  B.id,
  B.title,
  IF(BT.BT.title_lang IS NULL, B.title, BT.book_title) titulo
FROM books B LEFT JOIN books_titles BT ON B.id = BT.book_id
WHERE BT.title_lang IN ('fi') OR BT.title_lang IS NULL
ORDER BY titulo;
 

Debería devolver una tabla ordenada por titulo, poniendo title, si no hay coincidencias (el cruce de dos tablas donde la relación no se cumple devuelve NULL en el campo usado de clave), o book_title, si hay.
El uso de IN() permite listar uno o más valores en el paréntesis. En este caso será "fi" o ninguno (""), que no es NULL.
Es posible que ciertas variaciones en el ordenamiento deban resolverse por un SP, o bien creando dinámicamente en la aplicación la sentencia conveneinte.

[gr0uch0mars]

Gracias por la respuesta.

Lo acabo de probar. Me pasa lo mismo que con otra fórmula: si sólo hay 1 libro de 10 con un título traducido en francés (fr), por ejemplo, sólo me devuelve el registro que contiene un título en francés, el resto no aparece.

Me sigo estrujando la cabeza para que además me saque el resto de libros y me lo ordene bien.

¿Alguna sugerencia?

[gnzsoloyo]

Muéstranos un ejemplo en tabla de los datos contenidos en ambas. Muchas veces ayuda a entender por qué no los devuelve.
Otra cosa es que sería bueno conocer la estructura de las tablas (el CREATE TABLE...).

Finalmente, debes recordar que un campo NULL no se puede emparejar con nada, por lo que tanto Books.id como Books_titles.book_id deben contener valores reales (incluyendo el caracter vacío, que no es el NULL) para poder cruzar datos. Esto último debería ser resuelto con el uso de LEFT JOIN, en tanto Boooks contenga todos los libros, y Books_titles dependa de ella.
¿Esto es así?

La prueba, ¿la hiciste copiando lo que yo postee, o hiciste una sentencia propia?

Probemos esto:

Código sql:

SELECT
  B.id,
  B.title,
  IF(BT.BT.title_lang IS NULL, B.title, BT.book_title) titulo
FROM books B LEFT JOIN books_titles BT ON B.id = BT.book_id
WHERE BT.title_lang IN ('fi') OR BT.book_id IS NULL
ORDER BY titulo;
 

[gr0uch0mars]

Sobre el código que me diste, lo copié pero no devuelve los ítems que no tengan un título en ese idioma. (Además lo edité porque tenía un error en "BT.BT.title_lang"):

Código SQL:

IF(BT.BT.title_lang IS NULL...
 

por

Código SQL:

IF(BT.title_lang IS NULL...
 

Así es la Base de Datos (es sólo de pruebas):

Código SQL:

CREATE TABLE `books` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
 
INSERT INTO `books` VALUES(1, 'A Study in Scarlet');
INSERT INTO `books` VALUES(2, 'Scandal in Bohemia');
 
CREATE TABLE `books_titles` (
  `book_id` INT(10) UNSIGNED NOT NULL,
  `book_title` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  `title_lang` CHAR(2) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
INSERT INTO `books_titles` VALUES(2, 'Escándalo en Bohemia', 'es');
INSERT INTO `books_titles` VALUES(2, 'La Aventura del Escándalo en Bohemia', 'es');
INSERT INTO `books_titles` VALUES(2, 'Un scandale en Bohême', 'fr');
INSERT INTO `books_titles` VALUES(2, 'Kuningas hädässä', 'fi');
INSERT INTO `books_titles` VALUES(1, 'Estudio en Escarlata', 'es');
INSERT INTO `books_titles` VALUES(1, 'Estudio en Rojo', 'es');
INSERT INTO `books_titles` VALUES(1, 'Étude en rouge', 'fr');
 

Además, como un libro puede cambiar ligeramente de título (en este caso sí, aunque no debiera ser), la tabla books_titles tiene varias entradas para un mismo book_id y el mismo title_lang. Me doy con un canto en los dientes que pueda asociar un único título correctamente, porque es posible que al encontrar varios resultados coincidentes no sepa qué devolver como book_title.

Gracias ayudarme en este tema. A ver si encontramos la solución

Un saludo.

[gnzsoloyo]

Esto funciona:

Código sql:

SELECT
  B.id,
  B.title,
  IF(BT.title_lang IS NULL, B.title, BT.book_title) titulo
FROM books B LEFT JOIN books_titles BT ON B.id = BT.book_id
WHERE (BT.title_lang IN ('es') OR BT.book_id IS NULL) AND BT.book_title LIKE '%Es%'
ORDER BY titulo;
 

donde el texto contenido entre los signos de porcentual de LIKE '%%' es ua parte del texto buscado (los "%" son comodines en el LIKE)
Una pregunta que me asalta es ¿hay alguna posibilidad de usar tablas InnoDB? Para tu diseño sería un poco más seguro.

[gr0uch0mars]

Gracias por el avance.

Lo he probado y he comprobado que sólo funciona si pones español ('es' y %Es%), pero cambiando a francés ('fr'...) o finlandés ('fi'), del que sólo hay un registro, no devuelve ningún registro.

Tampoco entiendo por qué hay que usar un LIKE, y una simple igualdad no funciona (BT.book_title = 'es'). No sé cómo funciona ese LIKE ahí (es que me gustaría comprenderlo)

Un saludo.

[gnzsoloyo]

La consulta funciona si hay una relación entre las dos tablas. Esta relación esta dada por un campo (id en un caso, book_id en la otra). Pero si ingresas en books_titles un libro sin que exista una relación con books, ese libro no se listará. Debe existir el mismo valor de id que de book_id.
Este problema (meter un libro en books_titles que no exista en books) se podría solucionar usando tablas InnoDB en lugar de MyISAM, ya que las InnoDB poseen restricciones de clave foránea, lo que permitiría evitar una entrada huérfana en books_titles .

Se supone que en tu modelo, books conserva el listado de todos los libros, estén o no en books_titles. Si no es así, estás cometiendo un error de diseño en alguna parte, porque debería haber al menos una tabla que los contenga todos, como una tabla de stock en un almacen.

En cuanto a LIKE, su traducción literal sería "como", "parecido a" o "que contenga". No funciona una igualdad, porque una diferencia de un caracter es suficiente para que la comparación de FALSE. En otras palabras, "Estudio en Rojo" es diferente de "Estudio en Roj", en cambio "Estudio en Rojo" sí contiene a "Estudio en Roj".
LIKE se utiliza, junto con los comodines, para encontrar coincidencias parciales en una cadena, cuando no conoces exactamente la cadena completa, o quieres recuperar todas las que semejen.

[gr0uch0mars]

Sobre la relación entre las dos tablas, la tabla Books contiene TODOS los libros, y cuando introduzco una nueva fila en Books_titles la book_id que le asigno corresponde a un libro ya metido; por eso me extraña que, habiendo relación entre las dos tablas (B.id<->BT.book_id), no me saque los que no coincidan con el idioma a consultar.

Por otro lado, sobre lo de LIKE, sé lo que significa, pero para sacar un listado de todos los libros, a ser posible con el título en el idioma de interés, no veo por qué he de añadir que BT.book_titles sea LIKE '%Es%', pues esto sólo valdría para aquellos registros en cuyo título se incluyan las letras e y s, y eso estaría restringiendo el resultado (sacar un listado completo, de todos los libros de Books, incluyan '%Es%' o no en sus títulos)

[gnzsoloyo]

Sobre la relación entre las dos tablas, la tabla Books contiene TODOS los libros, y cuando introduzco una nueva fila en Books_titles la book_id que le asigno corresponde a un libro ya metido; por eso me extraña que, habiendo relación entre las dos tablas (B.id<->BT.book_id), no me saque los que no coincidan con el idioma a consultar.

Si la clave existe en ambos, y en la tabla Books_titles el idioma figura correctamente, debe mostrarlo. La única forma en que no devuelve datos es si el valor del idioma buscado no existe.
Ya lo probé con tus tablas de muestra, y funciona correctamente. Sólo queda verificar tus tablas...

Por otro lado, sobre lo de LIKE, sé lo que significa, pero para sacar un listado de todos los libros, a ser posible con el título en el idioma de interés, no veo por qué he de añadir que BT.book_titles sea LIKE '%Es%', pues esto sólo valdría para aquellos registros en cuyo título se incluyan las letras e y s, y eso estaría restringiendo el resultado (sacar un listado completo, de todos los libros de Books, incluyan '%Es%' o no en sus títulos)

Eso es una obviedad. Se supone que el valor a buscar lo pasarás a la sentencia por medio de un parámetro en la aplicación o en un SP. Creí que habías entendido que te estoy pasando un ejemplo. El cómo lo implementes dependerá de ti.

No me has respondido si puedes implementar o no tablas InnoDB.

[gr0uch0mars]

Iniciado por gnzsoloyo

Ya lo probé con tus tablas de muestra, y funciona correctamente. Sólo queda verificar tus tablas...

¿Si haces la consulta con las tablas y datos que te dejé en el 5º post

Código SQL:

SELECT
  B.id,
  B.title,
  IF(BT.title_lang IS NULL, B.title, BT.book_title) titulo
FROM books B LEFT JOIN books_titles BT ON B.id = BT.book_id
WHERE (BT.title_lang IN ('fi') OR BT.book_id IS NULL)
ORDER BY titulo
 

te salen estos dos resultados?

id|title|titulo
1|A Study in Scarlet|A Study in Scarlet
2|Scandal in Bohemia|Kuningas hädässä

A mí no, y es lo que intento conseguir (como para el registro 1 no existe un título en 'fi', que me devuelva como 'titulo' el books.title 'A Study in Scarlet', y luego ordene todo por 'titulo')

Iniciado por gnzsoloyo

No me has respondido si puedes implementar o no tablas InnoDB.

Pues con la nueva aplicación que uso para mi localhost (y con el que hago las pruebas de base de datos y web antes de subirlas al servidor), MAMP, no puedo crear bases de datos InnoDB, y la verdad es que no me había dado cuenta hasta que me lo has comentado. Si de verdad hay diferencia, probaré a buscar cómo hacer para tener soporte InnoDB.

Un saludo.