Discussion :

[vaneck]

bonjour,
je chercher à selectionner toutes les 25 lignes de ma table.

Code :

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

1
2
3
 
SET @rownum:=0;
SELECT `id` FROM Adherent WHERE (@rownum:=@rownum+1) % 25=1

Ce code fonctionne bien sur phpmyadmin, mais pas sur php

Code :

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

1
2
3
4
$code = 'SET @rownum:=0;
        SELECT `id` FROM Adherent WHERE (@rownum:=@rownum+1) % 25=1';
        $statement = $connection->getConnection()->query($code);
        print_r($statement->fetchAll(PDO::FETCH_COLUMN));

a vrais dire je suis dans le flou, je n'arrive pas a trouver la doc sur cet instruction que j'ai trouvé par hasard sur internet.
Auriez vous des pistes?
merci

[Séb.]

Tu ne précises pas ton SGBD. Je pars du principe que tu utilises un MySQL à jour, c'est à dire MySQL 8.

Tu peux utiliser la fonction d'analyse fenêtrée ROW_NUMBER() => https://dev.mysql.com/doc/refman/8.0...ion_row-number.
Pour garder une cohérence et une stabilité minimum dans le résultat il faut faire un tri, mettons sur un horodatage d'adhésion, ici members.joining_at.

Requête de base à exécuter sur MySQL Workbench pour comprendre la suite :

Code sql :

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

1
2
3
4
5
SELECT ALL
    id, full_name,
    ROW_NUMBER() OVER (ORDER BY joining_at ASC) AS i, -- Compteur, pour comprendre le résultat
    ROW_NUMBER() OVER (ORDER BY joining_at ASC) MOD 25 = 0 AS flag -- Marque à TRUE toutes les 25es lignes
FROM members

Voilà pour le résultat brut.

Ensuite, puisque tu ne veux conserver que les 25es lignes, il suffit d'embarquer cette requête dans une CTE et filtrer selon la colonne flag :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
WITH members_preselection (id, full_name, i, flag) AS (
    SELECT ALL
        id, full_name,
        ROW_NUMBER() OVER (ORDER BY joining_at ASC), -- Compteur, pour comprendre le résultat
        ROW_NUMBER() OVER (ORDER BY joining_at ASC) MOD 25 = 0 -- Marque à TRUE toutes les 25es lignes
    FROM members
)
SELECT ALL id, full_name, i
FROM members_preselection
WHERE flag = TRUE

[vaneck]

merci pour ta suggestion,
en effet j'ai oublié de précisé que je tournais sur MariaDB 10.3.32. j'ai testé vite fait le code que tu m'as donné sans succé, mais peut etre l'ai je mal ecrit. Cet syntaxe fonctionne sur mariadb?

[Séb.]

Selon la doc
CTE à partir de 10.2.1 https://mariadb.com/kb/en/with/
Fonctions fenêtrées à partir de 10.2.0 https://mariadb.com/kb/en/window-functions/

Donne la structure de ta table, ton script et l'erreur produite.

[vaneck]

merci, j'ai tapé quelque chose de ce gout la pour l'instant

Code :

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

1
2
3
4
SELECT `2`,
 ROW_NUMBER() OVER ( ORDER BY `id`) AS i,
 ROW_NUMBER() OVER ( ORDER BY `id`) MOD 25 = 0 AS flag 
FROM Adherent

2 etant le nom.
Cela fonctionne, mais j'ignore ce que veut dire MOD 25 = 0 ?

[mathieu]

il s'agit de l'opération modulo :
https://fr.wikipedia.org/wiki/Modulo_(op%C3%A9ration)

[vaneck]

ca fonctionne!
maintenant j'aimerais aller plus loin. Je voudrais qu'à partir d'une ID, je puisse recuperer l'ID de la 25 eme ligne avant et apres (et ainsi de suite).

Je pense dans un premier temps recuperer le decalage de ma ligne par rapport à son ordre naturel en base 25

Code :

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

1
2
3
4
5
6
7
 
WITH decalage AS ( 
    SELECT `id`, 
    ROW_NUMBER() OVER ( ORDER BY `id`) AS i 
    FROM Adherent 
)
SELECT `i` MOD 25 FROM decalage WHERE id = 82

ici par exemple mon tupple avec l'id 82 est situé à la 77eme lignes de la table. Il est donc décalé de 2 par rapport à 75 (3*25). Le code me renvoie bien 2.
j'aimerais pouvoir réutiliser ce 2 dans :

Code :

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

1
2
3
4
5
6
7
8
9
WITH pagination AS (
SELECT `id`,
    (ROW_NUMBER() OVER ( ORDER BY `id`)-2) MOD 25 = 0 AS flag 
    FROM Adherent
)
SELECT ALL `id`
FROM pagination
WHERE flag = TRUE

comme une sorte de variable.
A moins qu'il y ai un autre moyen de l'ecrire?

[vaneck]

il y a aussi un autre moyen de l'ecrire, en selectionant littéralement tout les 25 lignes en dessous de l'id choisit;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
WITH maCTE AS ( 
SELECT id 
    FROM Adherent 
    WHERE id < 82  
) 
, maCTE2 AS ( 
    SELECT `id`,
    ROW_NUMBER() OVER ( ORDER BY `id`  DESC) MOD 25 = 0 AS flag 
    FROM maCTE
) 
SELECT ALL `id`
FROM maCTE2
WHERE flag = TRUE

et au dessus

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
;WITH maCTE AS ( 
SELECT id 
    FROM Adherent 
    WHERE id > 82 
) 
, maCTE2 AS ( 
    SELECT `id`,
    ROW_NUMBER() OVER ( ORDER BY `id` ) MOD 25 = 0 AS flag 
    FROM maCTE
) 
SELECT ALL `id`
FROM maCTE2
WHERE flag = TRUE

mais je me demande toujours s'il y a pas plus simple , avec une seul requete?

[Séb.]

Il y a plus simple. Ci-dessous je marque la ligne de la valeur de référence (colonne "ref" à TRUE) dans la CTE "make_index".
Ensuite je me sers de l'index de la référence pour faire le calcul sur le modulo.

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
SET @id = 82; -- Valeur dont on cherche les lignes précédentes/suivantes
SET @shift = 15; -- Décalage toutes les @shift lignes avant/après @id
 
WITH
	RECURSIVE dataset (x) AS (
		-- Construction d'un jeu de données, valeurs de 30 à 190
		SELECT ALL 30
		UNION ALL
		SELECT ALL x + 1 FROM dataset WHERE x < 190
	),
	make_index (x, i, ref) AS (
		SELECT ALL
			x, -- Pour chaque valeur
			ROW_NUMBER() OVER (ORDER BY x ASC), -- Pose un numéro de ligne
			x = @id -- Et marque la valeur de référence
		FROM dataset
	)
SELECT ALL x, i, ref
FROM make_index
WHERE MOD(i - (SELECT ALL i FROM make_index WHERE ref = TRUE), @shift) = 0
;

Résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
x	i	ref
-------------------
37	8	0
52	23	0
67	38	0
82	53	1
97	68	0
112	83	0
127	98	0
142	113	0
157	128	0
172	143	0
187	158	0

J'ai gagné un truc ?

[escartefigue]

Une requête récursive s'appuyant sur une CTE sera en général peu performante, donc solution à choisir en fonction du volume à traiter

Et, question à 595 francs (soit 15 francs+15 francs+15 francs ) : @Vaneck pourquoi extraire une ligne sur 25 ? Quel est le besoin fonctionnel ?

[Séb.]

Une requête récursive s'appuyant sur une CTE sera en général peu performante, donc solution à choisir en fonction du volume à traiter

Euh oui, ici c'est juste pour générer un dataset pour l'exercice !

[vaneck]

désolé pour le retard, j'ai oublié de répondre mais ca marche
@seb, tu as gagné une place dans la liste de remerciement de mon app, ce qui ne te donne pas pour autant une part dans mon entreprise qui vaudra bientot des milliard, c'est dommage!
escartefigue: mon app est , en gros, un gestionnaire de bdd simpliste. Dans certaine tables les utilisateurs rentreront rapidement de nombreuses lignes en parallèle. Du coup entre le moment ou ma page .html s'affiche, et le moment ou je clique sur "page 3" (qui précede la 4 ou je suis), je n'aurais pas comme voulue les 25 lignes qui précede ma page 4.Alors que si je clique sur 'page 4-25ligne', je suis sur de tomber sur ce que je cherche. Voila, ,j'espere que c'est plus clair. L'autre objectif c'est aussi d'aprrendre du sql!