Retrouver le plus récent enregistrement ajouté par catégorie

[Bleys]

Bonjour,

Je vais vous expliquer la situation littéralement, puis je vous fourni le schéma de test pour plus de compréhension de mon soucis.

C'est une situation tout à fait classique, imaginons que la base gère un ensemble de produits rangés dans une catégorie. On a donc un ensemble de catégories, un ensemble de produits et des produits classés dans une (ou plusieurs catégorie).

Pour représenter cela on peut avoir le schéma suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
DROP TABLE IF EXISTS _test_catprd;
DROP TABLE IF EXISTS _test_cat;
CREATE TABLE _test_cat(
    cat_id INTEGER(11),
    cat_name VARCHAR(255),
    PRIMARY KEY (cat_id)
) ENGINE=InnoDB,
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;
DROP TABLE IF EXISTS _test_prd;
CREATE TABLE _test_prd(
    prd_id INTEGER(11),
    usr_id INTEGER(11),
    createDate INTEGER(11),
    prd_name VARCHAR(255),
    PRIMARY KEY (prd_id)
    -- usr_id est une foreign key ... son détail n'est pas nécessaire ici
) ENGINE=InnoDB,
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;
 
CREATE TABLE _test_catprd(
    cat_id INTEGER(11),
    prd_id INTEGER(11),
    PRIMARY KEY (cat_id, prd_id),
    CONSTRAINT _test_catprd_cat_fk FOREIGN KEY (cat_id) REFERENCES _test_cat (cat_id) ON DELETE NO ACTION,
    CONSTRAINT _test_catprd_prd_fk FOREIGN KEY (prd_id) REFERENCES _test_prd (prd_id) ON DELETE NO ACTION
) ENGINE=InnoDB,
CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'
;

Littéralement, ma question est "comment récupérer, pour chaque catégorie les derniers produits ajoutés ?"

Je veux que le select me retourne :
cat_id
prd_id
usr_id associé au prd
createDate

Idéalement je cherche à faire ça en une requête sans sous-requête. J'étais parti sur quelque chose comme :

Code :

1
2
3
4
5
6
 
SELECT catprd.cat_id, catprd.prd_id, prd.usr_id, prd.createDate
FROM _test_catprd catprd
INNER JOIN _test_prd prd ON prd.prd_id=catprd.prd_id
WHERE prd.createDate=MAX(prd.createDate)
GROUP BY catprd.cat_id

cette requête ne fonctionne pas, mais illustre ce que je cherche à faire ... (enfin je pense)

J'ai donc besoin de vous. Quelle requête me retournera ce que je cherche ? Mon inquiétude sur le groupage est surtout "comment être sûr que le prd_id et le usr_id retournés sont bien des couples valides" ?

J'imagine qu'une auto-jointure doit permettre de résoudre ce simple problème (d'apparence pour moi en tout cas). Mais je ne suis pas assez familier avec les patterns SQL pour pondre la requête moi-même.

Je cherche une requête pour le SGBD MySQL.


Merci pour votre aide.

[dehorter olivier]

première chose : Merci d'avoir mis autant de détails


je penses que cette discussion récente ou celle-ci peuvent t'aider

[Bleys]

Bonsoir,

Merci pour ta contribution.

Donc si je comprends bien, je suis obligé d'utiliser une sous requête. Du coup, ma requête ressemblerait à quelque chose comme :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT DISTINCT catprd.cat_id, prd.prd_id, prd.usr_id
FROM _test_catprd catprd
INNER JOIN (
  SELECT catprd.cat_id, MAX(prd.createDate)
  FROM _test_catprd catprd
  INNER JOIN _test_prd prd ON prd.prd_id=catprd.prd_id
  GROUP BY catprd.cat_id
) lastPrd ON lastPrd.cat_id=catprd.cat_id
INNER JOIN _test_prd prd ON prd.createDate=lastPrd.createDate
;

Je ne sais pas trop ce qu'il va se passer si deux produits pour une même catégorie ont la même date de création ... d'où mon distinct. Je vais tout de même jouer un ou deux jeux de tests pour ça.


Merci encore pour ton aide,

Et si quelqu'un voit comment faire ça en une requête ... je suis toujours preneur.



@ Plus

[skuatamad]

Envoyé par Bleys

Mon inquiétude sur le groupage est surtout "comment être sûr que le prd_id et le usr_id retournés sont bien des couples valides" ?

Il faut passer par une sous-requête

Envoyé par Bleys

Idéalement je cherche à faire ça en une requête sans sous-requête.

Pouquoi sans sous-requête, même avec une (ou des) sous-requête, c'est toujours une seule requête.

Qu'est ce que ça donne ?

Code :

1
2
3
4
5
6
7
SELECT catprd.cat_id, catprd.prd_id, prd.usr_id, prd.max_createDate
  FROM _test_catprd catprd
  JOIN (SELECT prd_id, usr_id, max(createDate) AS max_createDate
          FROM _test_prd
         GROUP BY prd_id, usr_id
       ) prd
    ON prd.prd_id = catprd.prd_id

[edit]J'avais pas vu ton post, essaie ma requête, je ne vois pas pourquoi tu repasses 2 fois sur chacune des tables.
Et compte tenu de l'écriture de ton 1er GROUP BY dans ta requête exemple je te conseille de lire :
http://cedric-duprez.developpez.com/...fier-group-by/

[Bleys]

Bonsoir,

Pouquoi sans sous-requête, même avec une (ou des) sous-requête, c'est toujours une seule requête.

Effectivement, mais la sous-requête présente ici est un select sur des tables que j'ai déjà en cours de sélection ... des tables qui dans mon cas réel sont assez conséquentes. Donc on va dire ... c'était d'un point de vue performances ... Si la méthode existe sans faire de SELECT, ça m'arrange.

Code :

Hmmm ... je ne l'ai pas exécuté mais attention, ce n'est pas exactement ce que je veux. Dans ta sous-requête tu fais un group by prd_id, usr_id ... or moi le critère usr_id n'a aucune influence. Je doute donc que cette requête me retourne ce que je souhaite. Qui plus est dans la table _test_prd, les couples prd_id, usr_id sont nécessairement uniques ... donc le group by est inutile, donc la requête non appropriée dans ce cas là.


Cela dit, merci pour ta contribution

[skuatamad]

Envoyé par Bleys

comment être sûr que le prd_id et le usr_id retournés sont bien des couples valides

Envoyé par Bleys

or moi le critère usr_id n'a aucune influence

Peux tu ajouter des données à tes tables pour illustrer ton besoin ?

Envoyé par Bleys

Qui plus est dans la table _test_prd, les couples prd_id, usr_id sont nécessairement uniques

Cependant il n'existe aucune contrainte d'unicité sur le couple (prd_id, usr_id), et la Primary key n'est définie que sur prd_id...

Un exemple des données dans les tables et du résultat souhaité serait le bien venu.

[Bleys]

Cependant il n'existe aucune contrainte d'unicité sur le couple (prd_id, usr_id), et la Primary key n'est définie que sur prd_id...

Non effectivement, j'ai écrit trop vite à force d'y penser je m'embrouille.

En fait quand je dis que le critère usr_id n'a aucune influence, je veux dire qu'il ne peut pas être utilisé pour du groupage ou quoi que ce soit (critère de where ou de having). Quand je dis "récupérer le bon couple" j'entends que je souhaite récupérer le bon usr_id associé au prd_id concerné.

Prends le problème comme je l'ai expliqué littéralement, on est dans quelque chose d'assez classique. usr_id me sert juste à savoir qui a créée le produit ... aucun intérêt dans la recherche ici.

Un exemple :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
INSERT INTO _test_cat
  (cat_id, cat_name)
VALUES
  (1, 'cat1'),
  (2, 'cat2'),
  (3, 'cat3'),
  (4, 'cat4')
;
 
INSERT INTO _test_prd
  (prd_id, prd_name, usr_id, createDate)
VALUES
  (1, 'prd1', 1, 110),
  (2, 'prd2', 2, 115),
  (3, 'prd3', 3, 120),
  (4, 'prd4', 5, 185),
  (5, 'prd5', 2, 140),
  (6, 'prd6', 3, 150),
  (7, 'prd7', 1, 160),
  (8, 'prd8', 2, 170),
  (9, 'prd9', 3, 180),
  (10, 'prd10', 1, 190)
;
 
INSERT INTO _test_catprd
  (cat_id, prd_id)
VALUES
  (1, 1),
  (1, 3),
  (1, 4),
  (1, 5),
  (1, 7),
  (2, 2),
  (2, 6),
  (2, 8),
  (2, 9),
  (2, 10)
;

Le résultat attendu sont les couples :

Code :

1
2
3
4
5
 
(cat_id, prd_id, usr_id, createDate)
----------------------------------------
(1,4,5,185),
(2,10,1,190),

Encore merci pour ton investissement

[skuatamad]

Effectivement je comprends mieux ta requête, il manquait juste une condition de jointure :

Code :

1
2
3
4
5
6
7
8
9
SELECT catprd2.cat_id, prd2.prd_id, prd2.usr_id, prd2.createDate
  FROM _test_catprd catprd2
  JOIN _test_prd prd2 ON catprd2.prd_id = prd2.prd_id
  JOIN (SELECT catprd.cat_id, max(createDate) AS max_createDate
          FROM _test_catprd catprd
          JOIN _test_prd prd ON catprd.prd_id = prd.prd_id
         GROUP BY catprd.cat_id
       ) lastPrd 
    ON lastPrd.cat_id = catprd2.cat_id AND lastPrd.max_createDate = prd2.createDate

Je ne sais pas trop ce qu'il va se passer si deux produits pour une même catégorie ont la même date de création ... d'où mon distinct.

Le DISTINCT ni changera rien pusique les prd_id seront différents.
C'est au client de dire ce qu'il souhaite voir affiché (le max(prd_id) ou le min(prd_id)), en attendant afficher tous les prd_id ayant la même date me semble cohérent.

Concernant les perfs tu peux créer un index sur _test_prd(prd_id,createDate) pour booster la sous-requête :

Code :

CREATE INDEX idx_test_prd_date ON _test_prd (prd_id,createDate)

[Bleys]

Bonjour,

Bon, si je comprends bien, en une seule requête ça ne semble pas possible ... tant pis.

Ok merci pour ton aide, pour le coup des "multiples produits à la même date" le fait est qu'il me faut le dernier enregistré (LE, une seule et unique entrée), je vais donc me baser sur le prd_id.

Pour l'index (prd_id, createDate) c'est ce que je vais mettre en place oui.

Merci pour ta contribution


(Je laisse en non résolu, au cas ou quelqu'un passe par là et connaisse la solution en un seul coup (sans sous requête)).

[Waldar]

Envoyé par Bleys

Bon, si je comprends bien, en une seule requête ça ne semble pas possible ... tant pis.
[...]
au cas ou quelqu'un passe par là et connaisse la solution en un seul coup (sans sous requête).

Juste un petit point de vocabulaire, la solution de skuatamad est bien une seule requête.

Si vous voulez masquer la sous-requête incluse dans le from, il suffit de l'encapsuler dans une vue (je n'ai lu la solution technique qu'en diagonale, je prends donc la dernière requête : je vous laisse adapter si ce n'est pas la bonne) :

Code :

1
2
3
4
5
6
7
CREATE VIEW v_test_prd_max_createdate
AS
  SELECT catprd.cat_id, max(createDate) AS max_createDate
    FROM _test_catprd AS catprd
    JOIN _test_prd AS prd
      ON catprd.prd_id = prd.prd_id
GROUP BY catprd.cat_id

Après votre requête devient :

Code :

1
2
3
4
5
6
7
SELECT catprd2.cat_id, prd2.prd_id, prd2.usr_id, prd2.createDate
  FROM _test_catprd catprd2
  JOIN _test_prd prd2
    ON catprd2.prd_id = prd2.prd_id
  JOIN v_test_prd_max_createdate lastPrd 
    ON lastPrd.cat_id         = catprd2.cat_id
   AND lastPrd.max_createDate = prd2.createDate

[Bleys]

Bonjour,

Effectivement, le bon terme est plutôt "sans requête imbriqués".

La solution de la VIEW, si je ne m'abuse, n'est finalement qu'une manière de noyer le poisson dans l'eau (pour ma demande on est bien d'accord, je ne dis pas qu'une vue n'a pas d'intérêt). Car, arrêtez-moi si je me trompe, lorsque l'on interroge une vue, la requête définissant cette vue est exécutée en live. Donc identique à l'utilisation de requêtes imbriquées ... non ?

Quoi qu'il en soit, pour mon soucis initial, j'ai modifié ma façon de procéder en "dénormalisant la BDD". J'ai une SP bien identifiée et obligatoire pour l'ajout d'un produit dans une catégorie. J'ai donc ajouté une colonne à ma table _test_cat qui contient une référence sur le dernier produit ajouté => recherche plus rapide.

Merci pour l'intervention.

[Waldar]

Envoyé par Bleys

Car, arrêtez-moi si je me trompe, lorsque l'on interroge une vue, la requête définissant cette vue est exécutée en live. Donc identique à l'utilisation de requêtes imbriquées ... non ?

Tout à fait, mais qu'avez-vous contre les requêtes imbriquées au juste ?
C'est une syntaxe acceptable et largement utilisée en SQL.

[Bleys]

Envoyé par Waldar

Tout à fait, mais qu'avez-vous contre les requêtes imbriquées au juste ?
C'est une syntaxe acceptable et largement utilisée en SQL.

oh rien ... je n'ai rien contre les requêtes imbriquées ... Je les utilise sans soucis.

Le problème étant là que, pour faciliter la compréhension de ma question, j'ai nécessairement simplifié les choses. En situation, il s'avère que cette requête est déjà une requête imbriquée et que les tables visées sont "relativement" (tout est relatif ...) conséquentes.

Question performances donc, si il était possible de faire ça directement via une jointure spécifique (pattern SQL, je pensais à de l'auto-jointure) ... et bien c'était toujours plus avantageux que d'exécuter une requête complète.

[CinePhil]

J'ai lu rapidement mais ceci est à proscrire :

Quoi qu'il en soit, pour mon soucis initial, j'ai modifié ma façon de procéder en "dénormalisant la BDD".

Qu'entends-tu par ceci ?

les tables visées sont "relativement" (tout est relatif ...) conséquentes.

Combien de millions de lignes ?

Avec des tables correctement indexées, les requêtes proposées ne devraient pas poser de problème de performance.

[Bleys]

Bonjour,

J'ai lu rapidement mais ceci est à proscrire :
Citation:
Quoi qu'il en soit, pour mon soucis initial, j'ai modifié ma façon de procéder en "dénormalisant la BDD".
Qu'entends-tu par ceci ?

Par rapport à l'exemple pris ici, dans ma table _test_cat, j'ai rajouté une colonne qui me permet d'obtenir immédiatement le dernier _test_prod ajouté à cette catégorie (la procédure "Ajouter tel produit à telle catégorie" étant clairement identifié) ... une foreign key sur un prd_id tout simplement.

Niveau taille de la table ... je ne sais pas trop comment te répondre dans le sens où, comme je le dis un peu précédemment, en réalité la requête est déjà une sous-requête et fait intervenir je pense une bonne dizaine de INNER JOIN (avec certaines tables en double). Certaines de ces tables contenant 800k entrées. Alors oui je suis d'accord ce n'est pas énorme (d'où mon tout est relatif), mais cette quantité va croître au fur et à mesure du temps et, pour le moment, mes ressources sont malheureusement limitées (une seule et unique base pour maître et slave, nombre de serveurs limités, ...).

Ainsi, l'autre gros avantage que j'ai à ajouter cette référence est que je ne suis plus dépendant de la croissance des tables (cette requête s'exécutera théoriquement pratiquement toujours en un même temps et ce même après un long temps de production).

Je suis bien évidemment ouvert à toute remarque. Si je suis ici avec ma question, c'est que clairement je manque d'expertise dans le domaine et que je recherche ici des avis plus experts.

[CinePhil]

C'est une dénormalisation qui peut s'envisager mais :
1) Il faut être sûr que les tables concernées par la dénormalisation ne seront jamais mises à jour par une autre procédure que celle prévue pour assurer la cohérence des données (insertion par un autre programme ou directement dans la BDD par exemple).

2) En principe, on dénormalise après avoir essayé toutes les autres solutions, notamment une meilleure indexation, et après avoir constaté un problème de performance, soit par prototypage, soit par constat sur la BDD réellement en production.