Mysql - un Group by non trié sur plusieurs tables ...

[Anegocier]

Bonjour,

J'ai testé de nombreuses façons mais les infos ne remontent toujours pas triées ...

J'ai 3 tables (membre, relation et lien)

BUT : Je voudrais le lien ayant le plus de hits pour chaque membre

Table membre : la structure est simple (id_membre)
Table relation : la structure est simple (id_membre, id_lien)
Table lien : La structure (id_lien, hits)

J'ai testé :
SELECT c.id_liens, c.hits, max(c.hits) as total
FROM user a, relation b, lien c
WHERE a.id_membre = b.id_membre and b.id_lien = c.id_lien
group by a.id_membre
order by total DESC;

le résultat fonctionne presque, je récupère bien un id_lien par membre mais pas celui avec le plus de hits ^^

Avez-vous une solution a mon problème, je planche depuis plusieurs heures sans résultat

Merci d'avance

 

[Anegocier]

Bonjour,

Le problème est toujours là, mes recherches ne donnent rien pour le moment ...
Si li y avait un spécialiste de mysql sur ce forum pour me donner un coup de main, je suis preneur ...

Merci d'avance

pour détailler :

exemple table user (2 users) :
User
1
2

exemple table relation :
User, lien
1,1
1,2
1,5
2,3
2,8

Exemple table lien :
Lien, hits
1,10
2,100
3,4
5,1
8,500

Le resultat devra etre
lien
2,100 pour le user 1 et
8,500 pour le user 2

 

[Anegocier]

Bonjour,
Je ne demande pas forcement la requête exacte mais une piste de travail ...
j'ai tourné la requête dans pas mal de sens et je n'arrive toujours pas a mon résultat ?
Si un spécialiste des requêtes veut bien prendre un peu de temps à trouver une solution ou à m'aiguiller vers celle-ci, merci d'avance ....

 

[YoyoS]

Salut !

Bon déjà pourquoi 3 tables ? Ca ne se justifie pas à première vue

Une table user (userid):
1
2

Une table lien (url,userid,hits)
"http://...", 1, 98734
"http://dfsdf",2, 89

Ca suffit largement avec une clé étrangère vers l'utilisateur si un lien n'aura qu'un seul utilisateur. Avec ta structure un lien peut avoir plusieurs utilisateurs, c'est fait exprès ou tu aimes te compliquer la tâche pour rien :mrgreen: ?

De plus tu ne nous donnes pas la structure et les types de colonnes de tes tables. Tu pourrais très bien faire un MAX sur un champ texte ou entier que ça changerait complètement le résultat.

Bref je pense qu'il y a de gros problèmes de conception qui t'empêche de faire ta requête correctement ! Donne nous plus d'infos et/ou modifie ton schéma s'il ne répond pas à tes besoins avant de pouvoir commencer à t'aider sur ta requête.

Bonne journée

 

[Anegocier]

Bonjour,

Avec ta structure un lien peut avoir plusieurs utilisateurs, c'est fait exprès ou tu aimes te compliquer la tâche pour rien :mrgreen: ?

Effectivement, un lien peut être affecté à plusieurs utilisateurs d'ou ma table de relation user/lien ...

Donne nous plus d'infos avant de pouvoir commencer à t'aider sur ta requête.

User :
id_user int(11)

User_lien :
id_user int(11)
id_liens int(11)

Liens :
id_liens int(11)
hits int(11)

J'ai simplifié les tables et retiré les autres champs non nécéssaire, comme url, type, statut de la table liens ou pseudo ds la table User par exemple, la table User_lien est par contre fidèle à mon modèle

Je pense que la difficulté de ma requête vient justement de ma table relation, je ne parviens pas à faire un group by trié ...

J'ai également essayé le max mais pas de résultat pour le moment :

SELECT a.id_user, c.id_liens, c.url, c.hits
FROM user a, user_liens b, liens c
WHERE c.hits =
(SELECT MAX(d.hits) FROM liens d WHERE a.id_user = b.id_user and b.id_liens = d.id_liens )
group by a.id_user;

Merci de ton retour sur mon problème

 

[YoyoS]

Une autre question se pose alors. Comment tu veux avoir le nombre de hit max pour chaque lien par utilisateur si chaque lien peut avoir plusieurs utilisateurs ?

Donc comment tu veux grouper par utilisateur ?
Imagine que le lien 333 avec le plus de hits soit associé à tous les utilisateurs
Tu veux comme résultats
lien1, hits500, user1
lien1, hits500, user2
lien1, hits500, user3
lien1, hits500, user4
lien1, hits500, user5
lien1, hits500, user6
lien1, hits500, user7
lien1, hits500, user8
lien1, hits500, user9
etc

?

 

[Anegocier]

Question intéréssante ^^

Effectivement, dans ce cas , j'aimerai avoir l'unicité sur le lien ...

J'ai l'impression que ma requête se complique ...

NB : Cependant, il y a peu de chance d'obtenir le cas précédent, d'autres critères sont présents
comme le statut du membre mais cela peut arriver ...

Donc si on prend en compte ce dernier cas,
j'aimerai avoir l'unicité par membre (et l'unicité par lien si plusieurs membres avec le meme lien) avec le hits le plus élevé ...

 

[YoyoS]

Oui donc c'est pas possible. C'est bien ce qui me semblait
Je vois pas comment tu peux avoir l'unicité si tu ne sais pas quel utilisateur choisir pour un lien.

 

[Anegocier]

Si on procédait a l'envers en partant du lien ?

Ne serait-il pas possible d'avoir l'unicité du lien ayant le plus de hits pour un seul membre ?
cela répondrai également à ma demande initiale en intégrant ce fait ...

 

[YoyoS]

C'est le "pour un seul membre" qui n'est pas possible

En quoi le lien 1 avec 500hits doit être affiché pour le userid1 ou le userid2 ? On en sait rien.

Si on a l'unicité du lien on aura donc un seul résultat alors qu'il appartient à userid[0à9]:

1) résultat impossible pour l'unicité du lien comme plusieurs userid associés
lien1 hits500 user ?

2) résultat possible pour l'unicité du user comme on veut que le lien associé max hits
lien1 hits500 user1
lien1 hits500 user2
lien1 hits500 user3
lien1 hits500 user4
lien1 hits500 user5
lien1 hits500 user6
etc

T'es donc obligé d'avoir l'option 2 dans ton cas. Je vois pas pourquoi tu veux autre chose alors que c'est impossible

 

[Anegocier]

Effectivement, ma première demande était mal formulée ...

j'ai essayé de me reposer le problème et d'obtenir la solution littérale :
1 - Je lis et récupère le hits pour chaque liens
2 - de ce résultat, je ne prend que les liens présents dans la table relation
3 - de ce résultat, je ne prend que les liens dont le user est VIP (type = 2)
4 - J'ai a priori l'unicité des liens que je voudrais triés ...

et je n'affiche pas le user, car en effet, un lien peut appartenir à plusieurs users

Si je prend un exemple :
Exemple, j'ai 3 users (1, 2 et 3)

la table relation (user / lien) avec par exemple
1,1
1,2
1,3
2,4
2,2
3,3

la table liens (lien / hits) avec
1,10
2,100
3,1000
4,10000

je dois obtenir

lien / hits
3,1000
4,10000

 

[YoyoS]

Donc vu le résultat tu sous entends que l'utilisateur 2 et 3 sont VIP (type = 2) ? Ca donnerait ça:

SELECT liens.lien,MAX(liens.hits) AS total FROM liens 
INNER JOIN relation ON (relation.lien = liens.lien)
INNER JOIN users ON (users.userid = relation.user)
WHERE users.type = 2
GROUP BY users.userid

 

[Anegocier]

Merci, je teste la requête et je te tiens au courant ^^

 

[YoyoS]

Par contre tu rajoutes dans ta table relation ceci (des comptes VIP se partagent le même lien) :

(user / lien)
3,4
2,3

Et tu obtiens les résultats :

userid/lien/total
2 4 10000
3 3 10000

Donc y a un souci

 

[Anegocier]

C'est un des meilleurs résultats que j'ai obtenu pour le moment ...

Avant, j'avais 2 liens pour 2 users (VIP) avec la bonne URL mais sans les bons HITS
maintenant, j'ai 2 liens pour mes 2 users avec les bons HITS mais pas la bonne URL pour 1 des users ???

j'ai user = 1, ok
j'ai hits correct par contre, j'ai une autre url (un autre id_liens ???) en face du bon HITS ^^

En fait, avant, j'avais a peu pres la meme chose, de mémoire, j'aias la meme url et le meme id_liens
avec ta requête, j'ai le HIT du mauvais lien correct ...

ma requête précédente :

SELECT c.id_liens, c.hits, max(c.hits) as total
FROM user a, relation b, lien c 
WHERE a.id_membre = b.id_membre and b.id_lien = c.id_lien 
group by a.id_membre
order by total DESC;

Si j'affiche max(c.hits), c'est comme toi

 

[YoyoS]

Voila la bonne:

 SELECT r.user,l.lien,l.hits 
FROM liens l INNER JOIN relation r ON (r.lien = l.lien)
 WHERE 
  (r.user,l.hits) 
IN 
  (
    SELECT u.userid,MAX(l.hits) AS total FROM users u
    INNER JOIN relation r ON (r.user = u.userid)
    INNER JOIN liens l ON (l.lien = r.lien)
    WHERE u.type = 2
    GROUP BY u.userid
  )

J'en ai chié

La prochaine fois fait un export de la structure de tes tables que je puisse l’exécuter direct dans phpmyadmin et faire mes tests

 

[Anegocier]

Je test et je te tiens au courant, merci de ton aide ...

Cela fonctionne bien sauf un cas que j'ai pu testé :
le user 1 et le user 2 ont un lien commun, ce lien possède le plus grand hit
resultat, il est affiché 2 liens au lieu d'un sachant que c'est le meme id_lien ^^

Sinon, c'est le meilleur résultat que j'ai pour le moment obtenu

 

[Anegocier]

J'ai retiré de ta requête r.user que je n'utilise pas et ajouté un distinct, cela permet de retirer les doublons

SELECT DISTINCT l.lien,l.hits 
FROM liens l INNER JOIN relation r ON (r.lien = l.lien)
WHERE 
  (r.user,l.hits) 
IN 
  (
    SELECT u.userid,MAX(l.hits) AS total FROM users u
    INNER JOIN relation r ON (r.user = u.userid)
    INNER JOIN liens l ON (l.lien = r.lien)
    WHERE u.type = 2
    GROUP BY u.userid
  )

MERCI pour ton aide, je n'aurai jamais trouvé cette solution ...

 

[YoyoS]

De rien, j'aime les casse-têtes

 

[Anegocier]

Bonjour,

Je reviens vers toi YoyoS pour intégrer une petite finition à la requête ...
En effet, actuellement celle ci pose un problème dans le cas suivant :

Exemple, si un membre possède plusieurs lien ayant le meme nombre de crédits
La requête les affiche tous, cela fonctionne bien dans les autres cas
c'est à dire, affiche le lien ayant le plus de crédits pour chaque membre

j'ai essayé avec des distinct et en voulant modifier le group by ou en ajoutant un limit mais rien ^^


Si tu as le temps de m'indiquer une piste, je suis preneur ...

Merci d'avance

 

[YoyoS]

Salut

Ca me semble normal comme résultat, comment savoir quel lien prendre alors si il en a plusieurs différents pour un même user avec le même nombre de hits ?

A toi de gérer lors de l'utilisation des données. Si tu vois plusieurs liens pour un même utilisateur avec le même nombre de hits, tu prends celui que tu veux.

Sinon il faudra malheureusement split la requête en plusieurs morceaux.

 

[Anegocier]

Salut,

Pour le lien à prendre, sachant que le critère principal de mon site est le nombre de hits,
je peux utiliser n'importe quel lien, à priori le 1er, j'ai essayé d'intégrer aussi une requête dans le select "first"
mais sans résultat, bien sûr

Ou bien faire un limit dans la sous requête du milieu avec le group by mais cela semble incompatible ?!?

 

[YoyoS]

C'est illogique mais bon. Donc tu dis à un des 2 mecs a aussi gagné par ex aequo : va te pendre. c'est ça ?

Moi j'en profiterais plutôt pour faire une condition spéciale lors de l'affichage afin d'afficher les 2 utilisateurs liés à ce lien.

Sinon comme j'ai dit on split la requête.

    SELECT DISTINCT l.lien,l.hits
    FROM users u
    INNER JOIN relation r ON (r.user = u.userid)
    INNER JOIN liens l ON (l.lien = r.lien)
    WHERE u.type = 2
    GROUP BY u.userid
    ORDER BY l.hits DESC

Ensuite tu récupères un utilisateur "au pif" qui est lié à ce lien, vu que c'est la tendance.

Tu peux tenter ça aussi :

    SELECT r.`user`,l.lien,l.hits
    FROM users u
    INNER JOIN relation r ON (r.user = u.userid)
    INNER JOIN liens l ON (l.lien = r.lien)
    WHERE u.type = 2
    GROUP BY l.lien,l.hits
    ORDER BY l.hits DESC

Comme çà t'as l'ordre décroissant des hits, t'as plus qu'à prendre les premiers pour chaque lien, le tout avec un utilisateur associé "au pif".

 

[Anegocier]

Pour répondre à ta question sur le fait qu'il aille se pendre

C'est le même utilisateur (membre) qui a 2 liens ou plus avec le meme nombre de Hits.
et j'affiche donc un de SES liens au hasard, a lui de voir lequel il préfère en affectant plus de crédits à l'un ou l'autre de ses liens

Le problème lors de l'affichage, je ne pense pas avoir le membre mais juste les liens, je regarde en détail et je te tiens au courant

 

[YoyoS]

Ouep tente la dernière requête proposée, c'est la meilleur solution je pense
Tu peux rajouter un LIMIT 10 pour avoir les 10 liens les plus populaires par exemple.

J'ai pas réussi à reproduire ton souci sinon. On doit pas avoir les mêmes data et requête.

 

[YoyoS]

C'est bon j'ai pu reproduire ton souci.

J'ai retiré de ta requête r.user que je n'utilise pas et ajouté un distinct, cela permet de retirer les doublons

SELECT DISTINCT l.lien,l.hits 
FROM liens l INNER JOIN relation r ON (r.lien = l.lien)
WHERE 
  (r.user,l.hits) 
IN 
  (
    SELECT u.userid,MAX(l.hits) AS total FROM users u
    INNER JOIN relation r ON (r.user = u.userid)
    INNER JOIN liens l ON (l.lien = r.lien)
    WHERE u.type = 2
    GROUP BY u.userid
  )

MERCI pour ton aide, je n'aurai jamais trouvé cette solution ...

J'ai repris cette requête et ajouté un group by r.user et ça a l'air de fonctionner non ?

SELECT DISTINCT l.lien,l.hits 
FROM liens l INNER JOIN relation r ON (r.lien = l.lien)
WHERE 
  (r.user,l.hits) 
IN 
  (
    SELECT u.userid,MAX(l.hits) AS total FROM users u
    INNER JOIN relation r ON (r.user = u.userid)
    INNER JOIN liens l ON (l.lien = r.lien)
    WHERE u.type = 2
    GROUP BY u.userid
  )
GROUP BY r.user

Par contre il va prendre un lien au hasard si un utilisateur en à plusieurs comme précisé aux posts précédents.

 

[Anegocier]

Tu es le meilleur ^^

Je viens d'intégrer ta modification et cela fonctionne bien
tu peux voir sur la page de test que je t'ai envoyé en MP ...

Pour UN membre, le nombre de lien affiché ne doit être que de UN, donc c'est impeccable

MERCI encore de ta disponibilité et de ton expertise sur mon problème.

(des jours que j'étais bloqué sur cette requête ...)

De rien, j'aime les casse-têtes

Si je peux t'aider ;-)

A bientôt et encore merci