Como puedo ordenador esto?

[yakukubu]

Hola.. a ver si alguien me orienta en como hacer esta sentencia:

Tengo estas tablas:

Código:

book
----
id
title

vote
----
id
book_id
value

author
----
id
name

book_author
----
book_id
author_id

Lo que tengo es el "id" del autor, y lo que tengo que hacer es que me retorne los libros de este author, ordenados por los votos que tiene...

Por ejemplo, algo así para ordenar en funcion de los votos

Código:

ORDER BY (SUM(v.value) / COUNT(v)) GROUP BY v.book_id ..

y algo así para saber los libros de un autor:

Código:

SELECT b FROM Entity\Book b JOIN b.authors a WHERE a.id = {$author->id}

Pero no se como unirlo todo en una misma sentencia...

Estoy usando Doctrine 2 (se ve en la segunda sentencia), pero bueno.. primero tendré que saber como hacerlo en sql nativo para luego buscar la solución en Doctrine 2...

Gracias!

[quimfv]

No parece que busques votos sino una nota media

SUM(v.value) / COUNT(v)

Esto te da la nota media de cada libro

Código MySQL:

SELECT book_id, SUM(v.value) / COUNT(*) as nota FROM vote v GROUP BY v.book_id;
 

esto de da la lista de titulos del autor elegido

Código MySQL:

SELECT b.id,b.title 
FROM book_autor ba 
INNER JOIN book b 
ON ba.book_id=b.id 
WHERE ba.author_id = {$author->id}
 

Ahora lo juntamos

Código MySQL:

Select b2.title, b1.nota 
FROM
    (SELECT book_id, SUM(v.value) / COUNT(*) as nota 
      FROM vote v GROUP BY v.book_id) as b1 
INNER JOIN
    (SELECT b.id,b.title 
     FROM book_autor ba 
     INNER JOIN book b 
        ON ba.book_id=b.id WHERE ba.author_id = {$author->id}) as b2
ON b2.id=b1.book_id
ORDER BY b1.nota DESC
 

Si fueran votos seria mas facil pero luego no tendria sentido el campo value de vote.

[yakukubu]

Hola quimfv, muchas gracias.

Sí, quizás no me expresé bien, hay que ordenarlo tal y como hiciste, según la nota media en función de los votos.

Solo voy ha hacer un pequeño cambio en tú código, en vez de:

Código:

INNER JOIN

utilizaré

Código:

RIGHT JOIN

Ya que cabe la posibilidad de que hayan libros sin votos, y también quiero listarlos.

..y ya que estamos.. ¿te parece bien la estructura de datos? o ¿quizás lo hice demasiado complicado?

Gracias de nuevo

[quimfv]

Correcta tu modificación.

La estructura me parece bien... soporta una relación n:m entre autores y libros... no todos los libros tienen un solo autor ....

Bien no creo que se tenga que tocar nada...

No se como controlas el numero de veces que puede votar/valorar un mismo usuario... es dificil si no se loguean previamente...

[yakukubu]

Hola quimfv.

Efectivamente los usuarios deben loguearse para poder votar.

Gracias por todo, lástima que no te puedo dar más karma, por lo visto te debí dar hace poco.. jeje