Requête SQL complexe

Bonjour à tous et merci d’avance pour votre temps. Je bloque sur une requête SQL pour la page d’accueil de mon site.

Pour faire simple, j’ai deux tables:

• books (id, title)
• books_chapters (id, book_id, title, created_at)

Sur ma page d’accueil, je souhaite affiche 30 livres (avec pagination mais là n’est pas le problème) en fonction des derniers des derniers chapitres ajoutés (dans la limite de 5 chapitres par livre).

Un petit exemple:

Cas 1/ J’ajoute un chapitre sur 30 livres différents, donc sur ma page d’accueil j’ai bien 30 livres avec pour chaque livre une liste d’un seul chapitre.

Cas 2/ J’ajoute 60 chapitres sur un livre, du coup ce livre remonte en 1er sur ma page d’accueil mais avec seulement les 5 derniers chapitres… + après 29 autres livres avec un nombre différent de chapitre en fonction du champ created_at

Vous pouvez voir ici un site dans un autre domaine qui fait ça (22 mangas par page) http://www.japanread.net/

Avez-vous une idée pour faire ça ?

PS: mon site est fait avec Symfony/Doctrine mais je peux très bien faire une requête SQL classique si c’est nécessaire.

Merci d’avance.

Si ça peut t’aider :

SELECT b.title, books_chapters.title as chapter, created_at
FROM books as b
JOIN books_chapters ON b.id = book_id
WHERE created_at > 99 'ici tu mets ton timestamp
ORDER BY (SELECT MAX(created_at)
FROM books_chapters
WHERE book_id = b.id
GROUP BY book_id) DESC, created_at DESC;

Pour les performances, je pense qu’ajouter une sous-requête pour dire seulement les 5 chapitres ne soient pas une bonne idée.

Je te conseille aussi d’ajouter un champ cache latest à la table books avec la dernière valeur d’ajout du chapitre pour éviter de faire une sous-requête.

SELECT b.title, books_chapters.title as chapter, created_at
FROM books as b
JOIN books_chapters ON b.id = book_id
WHERE created_at > 99 'ici tu mets ton timestamp
ORDER BY latest DESC, created_at DESC;

Données de test :

CREATE TABLE IF NOT EXISTS `books` (
  `id` int(6) unsigned NOT NULL,
  `title` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `books` (`id`, `title`) VALUES
  (1, "Naruto"),
  (2, "Naruto Shippuden"),
  (3, "Boruto"),
  (4, "Lee");

-- OK

CREATE TABLE IF NOT EXISTS `books_chapters` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `book_id` int(6) unsigned NOT NULL,
  `title` varchar(200) NOT NULL,
  `created_at` int(6) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `books_chapters` (`book_id`, `title`, `created_at`) VALUES
  (1, "NE PAS AFFICHER", 90),
  (1, "NE PAS AFFICHER", 90),
  (1, "NE PAS AFFICHER", 90),
  (2, "NE PAS AFFICHER", 90),
  (2, "NE PAS AFFICHER", 90),
  (2, "NE PAS AFFICHER", 90),
  (3, "Nouveau Boruto", 100),
  (4, "Les avantures de Lee", 101),
  (1, "1: Naruto rate l'examen", 102),
  (2, "1: Après 2 ans", 104),
  (1, "2: Naruto vole le parchemin", 106),
  (1, "3: Naruto réussi son multiclonage !!", 108),
  (2, "2: Visite d'Itachi à Konoha", 110),
  (1, "4: Naruto rejoind l'équipe 7", 112),
  (2, "3: Sasuke à l'hopital", 114);

Bonjour et merci d’avoir prit le temps de me répondre.

On fait le problème vient du fait que le nombre de derniers chapitres ajoutés par livre est variable (dans un maximum de 5) mais il faut toujours le même nombre de livre (pour pouvoir faire une pagination).

L’idée est de sélectionner les X derniers chapitres ajoutés jusqu’à avoir 30 livres. Mais attention par exemple si j’ajoute d’un coup 60 chapitres sur un livre, il faut que le livre apparaissent en premier sur la page mais qu’on ne voit que les 5 derniers chapitres + 29 autres livres…

Donc en gros j’ai toujours 30 livres par page mais il peut y avoir entre 30 et 150 (30*5) chapitres…

J’ai commencé à écrire une requête, et très vite, il est apparu que la demande était floue :

Pour un livre qui a 10 chapitres, dont 2 écrits très récemment, 1 écrit il y a 3 jours et les autres écrits il y a plus longtemps, sur quel critère tu décides d’afficher 1 chapitre, ou bien 2 … ou bien 5 ?

Merci de ton temps @elegance.

L’ORDER BY doit se faire par rapport à la date de création des chapitres mais le LIMIT sur les livres… mais il doit aussi avec une "sous limite" de 5 sur les chapitres (par livre GROUP BY).

Le lien dans mon 1er post illustre assez bien le besoin. C’est complexe a expliqué par écrit.

En cherchant "select 5 first rows within group" dans Google, je trouve ça : lien

puis ça :

SELECT city, population, country_code
 FROM 
 (
   SELECT city, population, country_code,
          ROW_NUMBER() over(partition by countries.country_code 
                            order by     countries.country_code, 
                                         cities.population DESC) RowNum
   FROM countries 
     INNER JOIN cities city on countries.country_code = cities.country_code
 ) tbl
 WHERE RowNum <= 10;

Ici, ça donne les 10 plus grandes villes de chaque pays. Manque le filtre pour avoir uniquement les 30 plus grands pays.

Pour ça, il faut ajouter une condition à la fin : and country_code in (liste des 30 country voulues)

Selon que tu as MySQL, ou SQLServer, ou autre chose, la syntaxe va différer.

Tu peux le faire en PHP, sinon tu rajoutes une sous-requête :

WHERE created_ad > 99 AND id IN (SELECT id
FROM books_chapters
WHERE book_id = b.id
ORDER BY created_ad DESC
LIMIT 5)

Mais je ne sais pas si les performances ne vont pas être réduite par la sous-requête.

Hello,

Perso j’aurais fait un truc du genre:

WITH last_books AS (
    SELECT b.id book_id,
           b.title book_title,
           MAX(c.created_at) last_created_at
    FROM books b
      INNER JOIN books_chapters c
        ON c.book_id = b.id
    GROUP BY b.id, b.title
    ORDER BY last_created_at DESC
    LIMIT 30
),
last_chapters AS (
    SELECT lb.book_id,
           lb.book_title,
           c.title chapter_title,
           c.created_at,
           lb.last_created_at,
           ROW_NUMBER() OVER (PARTITION BY lb.book_id ORDER BY c.created_at DESC, c.id DESC) rn
    FROM last_books lb
      INNER JOIN books_chapters c
        ON c.book_id = lb.book_id
)
SELECT lc.book_id,
       lc.book_title,
       lc.chapter_title,
       lc.created_at,
       lc.last_created_at
FROM last_chapters lc
WHERE lc.rn <= 5
ORDER BY lc.last_created_at DESC, lc.book_id DESC, lc.rn

Si tu travailles avec du MySQL il faut au moins MySQL 8+ ou MariaDB 10.1.2+ pour utiliser ça apparemment. J’ai une préférence pour ROW_NUMBER par rapport à (DENSE_)RANK parce que ça permet de fixer le nombre de résultats même si deux dates sont identiques.

J’essaie de limiter les livres sur lesquels on va chercher dans la première requête pour réduire le coût. Pour le WITH, on peut faire sans en remettant sous la forme de sous-requêtes mais je trouve ça plus lisible et ça évite parfois les répétitions.