Discussion :

[Couin]

Coucoutte !

Je suis confronté à une colle , pis qui colle bien lol

Une table (messages d'un tchat) , donc je voudrais , avec certaines conditions, récupérer une valeur mais de certaines lignes.

Pour simplifier la table, on a une colonne userid (l'id du membre qui poste le message), touserid (0 pour un message public, autre que 0 pour un message privé vers l'id destinataire), le message et messagetimestamp .

Je voudrais récupérer les valeurs de messagetimestamp (ORDER DESC) où touserid = 0 , pour la 1ere ligne puis la 51ème, puis 101ème etc etc etc

J'ai essayé tout un tas de requêtes dégottées sur le net (sans même les comprendre ) qui évidement, me donnent entre au choix aucun résultat, pas le bon résultat, ou un phpmyadmin qui part dans l'espace...

Dans les requêtes que j'ai pu trouver (sans explication de leur fonctionnement) , celle qui revient le plus souvent a cette forme là :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT *
       FROM (
           SELECT
              @row := @row +1 AS rownum, messagetimestamp 
			FROM (SELECT @row :=0) r, table_messages WHERE touserid = 0 ORDER BY messagetimestamp DESC
          ) ranked
      WHERE rownum % 50 = 0

@row := 0 ? ? ? numrow % 50 = 0 ? ? ?

Si quelqu'un a une piste un peu plus sérieuse que les trucs jetés à l'arrache sur beaucoup de forums US , ca serait bienviendu
En attendant, je continue les recherches de mon coté.

Merkouin !

[escartefigue]

Bonjour,

il faut calculer un rang basé sur le timestamp et du coup, une fonction OLAP telle que ROW_NUMBER(), RANK() ou DENSE_RANK().
Attention : ces fonctions ne sont applicables dans MySQL que depuis la V8.

[escartefigue]

exemple avec ce jeu d'essai (j'ai simplifié en mettant des valeurs numériques à la place des time-stamp, ce qui compte c'est d'avoir un chrono) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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
with T1(userid, touserid, ts) as
    (select 1, 0, 100   union all
     select 1, 1, 101   union all
     select 1, 0, 102   union all
     select 1, 0, 103   union all
     select 1, 0, 104   union all
     select 1, 0, 105   union all
     select 1, 1, 106   union all
     select 1, 1, 107   union all   
     select 1, 0, 108   union all
     select 1, 0, 109   union all
     select 1, 1, 110   union all
     select 1, 1, 111   union all
     select 1, 0, 112   union all
     select 1, 0, 113   union all
     select 1, 1, 114   union all
     select 1, 0, 115   union all
     select 2, 1, 116   union all
     select 2, 0, 117   union all
     select 2, 0, 118   union all    
     select 2, 0, 119   union all
     select 2, 1, 120   union all
     select 2, 1, 121   union all
     select 2, 0, 122   union all
     select 2, 0, 123   union all
     select 2, 1, 124   union all
     select 2, 0, 125  
    )

Et cette requête (ici j'en prends un sur trois pour éviter de créer un jeu d'essai énorme, mais le principe reste le même) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
   , T2(userid, ts, rn) as
    (select userid
           , ts
           , row_number()
              over(partition by userid
              order by ts asc)
     from T1      
     where touserid=0
    )
select * from T2
where mod(rn-1, 3)=0
order by userid, rn   
;

J'obtiens le résultat attendu : le premier puis un cas sur 3 dans mon exemple

userid	ts	rn
1	100	1
1	104	4
1	109	7
1	115	10
2	117	1
2	122	4

[Couin]

Hello,

Merci pour ta réponse.

Je pense que la version MySQL (chez Ionos, un hébergement mutualisé) doit être 5.7 ou pas loin (pas marqué dans leur Phpmyadmin).

J'avoue que je suis un peu (complètement même) perdu par l'exemple même

Dasn la liste du jeu d'exemple, noramlement devraient etre retenus les valeur où j'ai mis un * au bout de la ligne.
select 1, 0, 100 union all*
select 1, 0, 102 union all
select 1, 0, 103 union all
select 1, 0, 104 union all*
select 1, 0, 105 union all
select 1, 0, 108 union all
select 1, 0, 109 union all*
select 1, 0, 112 union all
select 1, 0, 113 union all
select 1, 0, 115 union all*
select 2, 0, 117 union all
select 2, 0, 118 union all
select 2, 0, 119 union all*
select 2, 0, 122 union all
select 2, 0, 123 union all
select 2, 0, 125 *

Je trouve une divergence avec le tableau final , après 115.

Je vais essayer de préciser un peu plus le besoin car je pense qu'après relecture de mon post d'origine, je n’ai pas mis quelque chose de super clair lol

En gros le tchat a aujourd’hui 230000 lignes. Les timestamps ne sont évidement pas contigus puisque c'est celui de chaque message. Les id de ligne ne le sont pas non plus forcément car il peut y avoir des messages qui ont été supprimés (modération ou tests).

Dans l'admin du site, j'ai un script permettant de lister par page tous les messages publics (donc touserid = 0).
Sur chaque page, je commence par le message le plus récent (de la page), d'où le ORDER BY timestamp DESC.
Comme j'affiche 50 messages par page, ca fait un paquet de pages. Du coup, j'avais ajouté un champs pour saisir le numéro de page à afficher. Mais pas du tout pratique pour chercher par date. J'ai rajouté un menu déroulant Select , dans laquelle je liste la date du message le plus récent de chaque page :


Donc voilà, pour la première page, le message le plus récent, bah c’est le dernier message public du tchat , la page 2, c'est 50 messages (publics) plus tôt etc etc etc. Le menu déroulant donne ca :


Pour le moment ca marche en faisant un array php comme ceci :

Code php :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$i = 0;
$p = 0;
$query = "SELECT messagetimestamp FROM table_messages WHERE touserid = 0 ORDER BY id DESC";
$result = mysqli_query($dbwrite, $query);
$datepages = array();
while ($row = mysqli_fetch_array($result) )  {
	if ( $i == 0 ) {
	$p++;
	$date = date("Y-m-d H:i:s", $row['messagetimestamp ']);
	$datepages[$p]= $date;
	}
	$i++;
	if ( $i == $limit ) { $i = 0; }
}

Même si le délai reste encore correct, je pense que c’est un peu idiot de traiter sur 198000 lignes (messages publics) alors qu'une requête sortant directement les 3961 dates serait certainement plus rapide.

Merkouin

[escartefigue]

La divergence après la valeur 115 est due au fait que j'ai partitionné par identifiant émetteur (userid), on reconsidère donc le rang pour chaque identifiant émetteur.
S'il ne faut pas le faire, il suffit de reprendre la même requête en supprimant la condition de partitionnement

[Couin]

Re

Le userid était plus un identifiant membre plutôt que son rang, c’est vrai que j'ai pas précised

Le souci c'est que j'ai pas MySQL 8, ni sur l'hébergement mutualisé ( Version du serveur : 5.7.32-log ), ni sur mon serveur de dev (qui est encore plus vieux que le mutualisé puisque 5.5.22-0ubuntu1).
Du coup c'est un peu cuitos pour les fonctions précités

[escartefigue]

avec le même jeu d'essai que précédemment, voici une requête applicable avec MySQL V5.7 :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
set @RN=0
;
select * 
from (select userid   as US
           , ts      as TS
           , (@RN:=@RN + 1) as RN
      from T1
      where touserid=0
      order by ts
     ) T2
where mod(RN-1, 3)=0
order by US, RN   
;

J'ai supprimé le partitionnement par userid puisque non souhaité

[Couin]

Yeah !

Nickel !

Voici donc la requête et le script php adapté au besoin :

Code php :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$query1 = "set @RN=0;";
mysqli_query($dbwrite, $query1);
$query1 = "select * from
(select messagetimestamp , (@RN:=@RN + 1) as RN from tablemessages where touserid = 0 order by messagetimestamp desc) T2
where mod(RN-1, 50)=0
order by messagetimestamp DESC";
$result = mysqli_query($dbwrite, $query1);
$i = 0;
$p = 0;
$datepages = array();
while ($row = mysqli_fetch_array($result) )  {
$p++;
$datepages[$p]= date("Y-m-d H:i:s", $row['messagetimestamp ']);
}

Grand merci pour ton aide

[Couin]

PS : Désolé Winjerome pour la colorisation, j'y pense rarement à modifier la balise après coup