Discussion :

[Toufik83]

Bonjour,

J'ai une requête qui fonctionne bien si j'utilise une concaténation classique comme :

Code php :

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

1
2
3
4
5
 
$req="select .... from table group by itemId
having (...) order by";
if($condition1) $req.=" itemId desc";
if($condition2) $req.=" itemName asc";

L'utilisateur à des paramètres, comme "l'ordre d'affichage des publications", c'est un champs "autorised" JSON sous forme :{"pub":{"order":{"type":"newest"}},...}, ce que je veux c'est d'afficher ses publications en fonction de ses paramètres, mais quand j'essaies de remplacer les conditions PHP par une case mysql, l'ordre n'est jamais correcte :

Code :

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

1
2
3
4
5
6
 
$req="select .... from table group by itemId
having (...) 
order by 
(case when  json_extract(autorised,'$.pub.order.type')= 'newest' then itemId end) desc,
(case when  json_extract(autorised,'$.pub.order.type')= 'oldest' then itemName end) asc"

[CinePhil]

Bonjour,

1) Si vous enlevez vos case when, vous vous retrouvez avec un tri d'abord sur la colonne itemId puis sur la colonne itemName. Est-ce ce que vous voulez ?

2) Il est d'ailleurs curieux que votre condition 'newest' ou 'oldest', qui sont sématiquement deux contraires, ce qui correspond au sens desc et asc, ne porte pas sur la même colonne !

3) Si aucune des deux conditions n'est satisfaite, alors votre requête va finir par ORDER BY DESC, ASC. Ça m'étonnerait que ça fonctionne, sauf si vous avez des colonnes qui s'appellent comme ça, ce qui serait une mauvaise idée puisque ce sont des mots du langage SQL !

4) Si je comprends bien, vous souhaitez trier soit en DESC, soit en ASC, selon ce qui sort de la fonction json_extract. Il faut alors utiliser un seul CASE et plutôt de cette manière (à tester) :

Code :

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

1
2
3
4
5
ORDER BY itemId 
    CASE
        WHEN json_extract(autorised,'$.pub.order.type') = 'newest' THEN DESC
        WHEN json_extract(autorised,'$.pub.order.type') = 'oldest' THEN ASC
    END

[Toufik83]

Bonjour,

Merci de votre réponse, cependant je viens d'utiliser votre code et malheureusement j'ai une erreur SQL #1064 - Erreur de syntaxe près de 'DESC when json_extract(autorised,"$.pub.order.type")="oldest...' à la ligne 52 .

Pour mieux vous expliquer ce que je veux, je voudrais trier les publications en fonction de 3 colonnes :

• countComments (nombre de commentaires)
• nbrLikes (nombre de likes)
• idPub (clé primaire de la table auto-incrémente)

Je n'ai pas de colonnes avec le nom "desc" ou "asc", et les cases au complet sont comme ça :

Code sql :

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

1
2
3
4
5
6
7
8
 
order by
        case when json_extract(autorised,"$.pub.order.type")="newest" then idPub  end desc ,
        case when json_extract(autorised,"$.pub.order.type")="oldest" then idPub  end asc,
        case when json_extract(autorised,"$.pub.order.type")="hp" then countComments end desc ,
        case when json_extract(autorised,"$.pub.order.type")="lp" then countComments   end asc,
        case when json_extract(autorised,"$.pub.order.type")="hlike" then nbrLikes  end desc ,
        case when json_extract(autorised,"$.pub.order.type")="llike" then nbrLikes  end asc

le résultat de "oldest" doit normalement commencer par les publications anciennes, donc idPub asc, pourtant le retour est :

idPub	nbrLikes	countComments
97	0	3
84	0	0
88	1	0
91	1	4
284	3	1

-Avec "newest" le retour est correcte :

idPub	nbrLikes	countComments
284	3	1
97	0	3
91	1	4
88	1	0
84	0	0

-Avec "hlike" highterLike (Commencer par les pubs ayant un nombre supérieur de likes) , nbrLikes desc le retour est faux :

idPub	nbrLikes	countComments
97	0	3
84	0	0
88	1	0
91	1	4
284	3	1

-Avec "llike" lowerLike (Commencer par les pubs ayant un nombre inférieur de likes) , nbrLikes asc le retour est correcte:

idPub	nbrLikes	countComments
97	0	3
84	0	0
88	1	0
91	1	4
284	3	1

[CinePhil]

Pour mieux vous expliquer ce que je veux, je voudrais trier les publications en fonction de 3 colonnes :

countComments (nombre de commentaires)
nbrLikes (nombre de likes)
idPub (clé primaire de la table auto-incrémente)

Donc la requête de base, c'est :

Code :

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

ORDER BY countComments, nbrLikes, idPub

Le tri par défaut est ASC.

Comme on veut aussi pouvoir trier en DESC selon le choix de l'utilisateur, il faut un CASE par colonne, sur le modèle de celui que je vous ai fourni dans ma précédente réponse :

Code :

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
ORDER BY 
    countComments
        CASE
            WHEN json_extract(autorised,'$.pub.order.type') = 'hp' THEN DESC
            WHEN json_extract(autorised,'$.pub.order.type') = 'lp' THEN ASC
        END,
    nbrLikes
        CASE
            WHEN json_extract(autorised,'$.pub.order.type') = 'hlike' THEN DESC
            WHEN json_extract(autorised,'$.pub.order.type') = 'llike' THEN ASC
        END,
    itemId 
        CASE
            WHEN json_extract(autorised,'$.pub.order.type') = 'newest' THEN DESC
            WHEN json_extract(autorised,'$.pub.order.type') = 'oldest' THEN ASC
        END

[Toufik83]

Bonjour,

Avec ce code j'obtiens une erreur SQL
#1064 - Erreur de syntaxe près de 'CASE WHEN json_extract(autorised,'$.pub.order.type') = 'hp' THEN...' à la ligne 52.

La version de MySQL est 10.4.14-MariaDB.

[CinePhil]

OK donc le test que je n'avais pas fait, comme indiqué dans ma première réponse, s'avère en échec.

Comme vous utilisez PHP, je vous conseille alors de remplacer vos case SQL de votre requête par ce que vous aviez commencé à faire en PHP

Code :

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

1
2
3
4
5
 
$req = "select .... from table group by itemId
having (...) order by";
if($condition1) $req.=" itemId desc";
if($condition2) $req.=" itemName asc";

Vous pouvez encore essayer en SQL cette syntaxe :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
ORDER BY 
        CASE
            WHEN json_extract(autorised,'$.pub.order.type') = 'hp' THEN countComments DESC
            WHEN json_extract(autorised,'$.pub.order.type') = 'lp' THEN countComments ASC
        END,
        CASE
            WHEN json_extract(autorised,'$.pub.order.type') = 'hlike' THEN nbrLikes DESC
            WHEN json_extract(autorised,'$.pub.order.type') = 'llike' THEN nbrLikes ASC
        END,
        CASE
            WHEN json_extract(autorised,'$.pub.order.type') = 'newest' THEN itemId DESC
            WHEN json_extract(autorised,'$.pub.order.type') = 'oldest' THEN itemId ASC
        END

[Toufik83]

Bonjour,

J'apprécie fortement vos efforts pour résoudre mon problème, mais malheureusement la deuxième syntaxe n'est pas acceptée par mysql, apparemment il n'accepte pas les DESC/ASC avec un nom de colonne dans Then : THEN countComments DESC même en ajoutant des parenthèses autour des cases.

Je vais garder la concaténation classique, car pour l'instant c'est le seul moyen qui me retourne un résultat correcte.

[al1_24]

Bonjour,

Sachant que les trois colonnes sont de type numérique, en inversant le signe d'une suite numérique on inverse aussi son ordre.
Compte tenu par ailleurs que les conditions de tri sont exclusives, on pourrait peut-être écrire ainsi la condition de tri :

Code :

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

1
2
3
4
5
6
7
8
9
order by
        case json_extract(autorised,"$.pub.order.type")
            when 'newest'   then idPub
            when 'oldest'   then idPub * -1
            when 'hp'       then countComments
            when 'lp'       then countComments * -1
            when 'hlike'    then nbrLikes
            when 'llike'    then nbrLikes * -1
        end

[Toufik83]

Bonjour,

Merci al1_24 de votre réponse, cependant le trie est toujours en mode ascendant.

Je viens de créer une nouvelle table avec des données simples afin de m'assurer que le problème n'est pas seulement lié à ma requête et vous pouvez tester :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1
-- Généré le : lun. 03 jan. 2022 à 22:56
-- Version du serveur :  10.4.14-MariaDB
-- Version de PHP : 7.2.33
 
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
 
--
-- Base de données : `test`
--
 
-- --------------------------------------------------------
 
--
-- Structure de la table `pubs`
--
 
CREATE TABLE `pubs` (
  `idPub` int(11) NOT NULL,
  `nbrLikes` int(11) NOT NULL,
  `countComments` int(11) NOT NULL,
  `autorised` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '{"pub":{"order":{"type":"newest"}}}' CHECK (json_valid(`autorised`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
--
-- Déchargement des données de la table `pubs`
--
 
INSERT INTO `pubs` (`idPub`, `nbrLikes`, `countComments`, `autorised`) VALUES
(1, 1, 5, '{\"pub\":{\"order\":{\"type\":\"newest\"}}}'),
(2, 5, 2, '{\"pub\":{\"order\":{\"type\":\"newest\"}}}'),
(3, 4, 10, '{\"pub\":{\"order\":{\"type\":\"newest\"}}}');
 
--
-- Index pour les tables déchargées
--
 
--
-- Index pour la table `pubs`
--
ALTER TABLE `pubs`
  ADD PRIMARY KEY (`idPub`);
 
--
-- AUTO_INCREMENT pour les tables déchargées
--
 
--
-- AUTO_INCREMENT pour la table `pubs`
--
ALTER TABLE `pubs`
  MODIFY `idPub` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

[Toufik83]

Bonjour,

Je viens de comprendre pourquoi ça n'a pas fonctionné, c'est parce que la valeur de "$.pub.order.type" est retournée avec des guillemets ("newest","oldest"....) et dans les conditions de case j'ai mis json_extract(autorised,'$.pub.order.type') = "newest" alors que "newest" != ' "newest" '.

En remplaçant les guillemets tout fonctionne comme prévu .

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
select ....
,(select regexp_replace(json_extract((select autorised from users where id_user=:id),"$.pub.order.type"),'[^a-zA-Z]','') from users  where id_user=:id) as type
...
order by 
   case when type="newest" then idPub end desc 
   , case when type="oldest" then idPub end asc
   ,case when type="hlike" then nbrLikes end desc
   ,case when type="llike" then nbrLikes  end asc
   ,case when type="hp" then countComments end desc
   ,case when type="lp"  then countComments end asc

Merci à vous.