mysql, group by, distinct, cakephp

15370
,

Bonjour,

Depuis j'ai un soucis avec mysql je n'arrive pas à faire des requetes groupées sur une table

Ce que je fais

Voici la structure de ma table

CREATE TABLE `colis` (
  `id` int(11) NOT NULL,
  `frais` int(11) NOT NULL,
  `nom_expediteur` varchar(255) NOT NULL,
  `telephone_expediteur` varchar(255) NOT NULL,
  `nom_destinataire` varchar(255) NOT NULL,
  `telephone_destinataire` varchar(255) NOT NULL,
  `valeur` int(11) NOT NULL,
  `nature` text NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime DEFAULT NULL,
  `etat` int(11) DEFAULT '1',
  `user_id` int(11) NOT NULL,
  `agence_id` int(11) NOT NULL,
  `agence_id1` int(11) NOT NULL,
  `code_colis` varchar(255) DEFAULT NULL,
  `residence_expediteur` varchar(100) DEFAULT NULL,
  `slug` varchar(500) DEFAULT NULL,
  `nom_autre_destinataire` varchar(255) DEFAULT NULL,
  `prenom_autre_destinataire` varchar(255) DEFAULT NULL,
  `numero_autre_destinataire` varchar(255) DEFAULT NULL,
  `phone_autre_destinataire` varchar(255) DEFAULT NULL,
  `date_retrait` datetime DEFAULT NULL,
  `lieu_payement` varchar(100) DEFAULT NULL,
  `type_payement` varchar(100) DEFAULT NULL,
  `provenance` varchar(100) DEFAULT 'online',
  `numero_facture` int(11) DEFAULT NULL,
  `passage` int(11) DEFAULT NULL,
  `cloture_caisse` int(11) DEFAULT '0',
  `idsession` varchar(100) DEFAULT NULL,
  `date_reception` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `agent_retrait` int(11) DEFAULT '0',
  `sync` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `agences` (
  `id` int(11) NOT NULL,
  `nom_agence` varchar(255) NOT NULL,
  `ville` varchar(255) NOT NULL,
  `localisation` varchar(255) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime DEFAULT NULL,
  `etat` int(11) NOT NULL DEFAULT '1',
  `entreprise_id` int(11) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `sync` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

voici le code SQL que je fais

$conn->execute('SELECT DISTINCT(code_colis) as code_colis, frais, code_colis,nature,nom_agence,telephone_expediteur,colis.created,colis.slug,nom_expediteur,nom_destinataire,colis.etat,type_payement FROM colis,agences WHERE colis.agence_id = agences.id AND colis.id  ORDER BY code_colis ASC')->fetchAll('assoc')

Ce que je veux

je veux la liste groupé par code colis par agence

Ce que j'obtiens

Error: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mybd.Colis.frais' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

28 Réponse

1
112438
,

Hello,
je ne comprends pas trop ta demande ; ni ta requête d'ailleurs : il n'y a pas de GROUP BY, un AND colis.id inutile, pas certain de ta syntaxe du DISTINCT....
tu veux récupérer les code_colis par agence (en imaginant qu'il y ait plusieurs code_colis identiques) ?
peut-être un truc du genre :

SELECT DISTINCT colis.code_colis FROM colis AS colis
INNER JOIN agences AS agence
ON colis.agence_id = agence.id 
AND agence.nom_agence = "bidule" // ou autre condition, je ne sais pas
ORDER BY colis.code_colis ASC

cela dit, il faudrait peut-être mettre (entre autres) tes expéditeurs et destinataires dans d'autres tables que colis et faire qq jointures... c'es une idées...

15370
,

Merci @saibe,
en effet je veux récupérer les enregistrements en groupant sur le code de colis, car dans ma table j'ai plusieurs entrée avec le même code de colis. Lorsque j'execute ceci

SELECT DISTINCT colis.code_colis FROM colis AS colis
INNER JOIN agences AS agence
ON colis.agence_id = agence.id 
ORDER BY colis.code_colis ASC

La requête fonctionne comme voulu mais si je fait ceci:

SELECT DISTINCT colis.code_colis, frais, code_colis,nature,telephone_expediteur,Colis.created,Colis.slug,nom_expediteur,nom_destinataire,Colis.etat,type_payement FROM colis AS colis
INNER JOIN agences AS agence
ON colis.agence_id = agence.id 
ORDER BY colis.code_colis ASC

le résultat atendu n'est plus le même car cela me repete des lignes avec le mêm code colis pourtant a fait un distinct

112438
,

normal car dans ton SELECT colis.code_colis, frais, code_colis,nature,telephone_expediteur,colis.created,colis.slug,nom_expediteur,nom_destinataire,Colis.etat,type_payement il doit y avoir des colonnes différentes, du coup rien de distinct ;)
tu veux vraiment tout récupérer ?

15370
,

je veux récupérer les éléments distinct par code_colis donc si j'ai 3 lignes dans ma table avec le même numéro de colis qu'une seule ligne soit retourné parmis les 3 de préférence la ligne ayant la plus petive valeur du champ frais(MIN(frais))

112438
,

donc un truc du genre ?

SELECT colis.code_colis, MIN(colis.frais) FROM colis AS colis // bien vérifier ton select
INNER JOIN agences AS agence
ON colis.agence_id = agence.id 
AND agence.nom_agence = "bidule" // ou autre condition, je ne sais pas
GROUP BY colis.code_colis
ORDER BY colis.code_colis ASC

du coup je comprends le GROUP BY ;)

15370
,

oui sauf que je veux récupérer tous les champs si j'aoute les champs j'aurai des messages d'erreur du genre le champ ne dépend pas du group by

15370
,

si je fais par exemple ceci

SELECT colis.code_colis, MIN(colis.frais),code_colis,colis.id FROM colis AS colis
INNER JOIN agences AS agence
ON colis.agence_id = agence.id 
GROUP BY colis.code_colis
ORDER BY colis.code_colis ASC

j'ai un bon message d'erreur du genre
#1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mybd.colis.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

112438
,

tu as 2 fois code_colis et j'imagine que ton colis.id est une key ; donc il va être à chaque fois différent....

15370
,

voici le nouveau code

SELECT colis.code_colis, MIN(colis.frais),colis.id FROM colis AS colis
INNER JOIN agences AS agence
ON colis.agence_id = agence.id 
GROUP BY colis.code_colis
ORDER BY colis.code_colis ASC

même erreur le colis.id est l'identifiant auto increment du colis qui est bel et bien différent pour chaque enregistrement

112438
,

normal il faut le rajouter au group, essaie ça :

SELECT colis.code_colis, MIN(colis.frais), colis.id FROM colis AS colis
INNER JOIN agences AS agence
ON colis.agence_id = agence.id 
GROUP BY colis.code_colis, colis.id
ORDER BY colis.code_colis ASC

mais je répète, si ton colis.id est une key, il va te sortir tous les résultats
à mon avis ça fonctionnerait avec un autre select comme ça :

SELECT colis.code_colis, MIN(colis.frais), colis.nature FROM colis AS colis // par exemple
INNER JOIN agences AS agence
ON colis.agence_id = agence.id 
GROUP BY colis.code_colis, colis.nature
ORDER BY colis.code_colis ASC
15370
,

la requête n'a pas d'erreur sauf que cela retourne toujours des doublons

112438
,

laquelle ? 1ere ou seconde ?

112438
,

à mon avis il y a un pb de logique et/ou de bases de données...

15370
,

la seconde fonctione bien sauf que cela me récupère toujours les doublons je suis sur mysql 5.7

112438
,

parce que la nature doit être différente. Avec ceci ça devrait le faire mais il faudrait revoir tes tables et les scinder pour ne garder que l'essentiel pour colis :

SELECT colis.code_colis, MIN(colis.frais), MIN(colis.nature) FROM colis AS colis // par exemple
INNER JOIN agences AS agence
ON colis.agence_id = agence.id 
GROUP BY colis.code_colis
ORDER BY colis.code_colis ASC
112438
,

vois-tu où je veux en venir ?

15370
,

pas exactement car si je veux scindé ce n'est que créer une table pour les expéditeurs si je ne me trompe pas

112438
,

... en fait ton pb n'est pas un pb sql mais plutôt d'organisation de tes tables (et donc de ta logique...)
si je comprends bien, tu as des colis (avec un code ou un id singulier : c'est ce que je ne comprends pas....) qui sont proposés par différentes agences pour différents destinataires et expéditeurs, et tu voudrais trouver le meilleur 'lien' entre tout ça... et donc la meilleure solution pour un des trois protagonistes...
ta table colis n'est pas bien établie : agence_id1, autre_nom_pour, etc... c pas top et donc difficilement exploitable
je peux t'aider pour ta requête, ou même pour tes tables mais pas pour ta logique...
prends le temps de bien envisager les choses...
++

112438
,

bon à la volée sur ton data colis :

CREATE TABLE `colis` (
  `id` int(11) NOT NULL,    // doit être unique ? exellent pour les jointures
  `frais` int(11) NOT NULL,         // doit provenir d'un calcul donc pas sa place dans la table
  `nom_expediteur` varchar(255) NOT NULL,         // devrait provenir d'une autre table
  `telephone_expediteur` varchar(255) NOT NULL,          // idem
  `nom_destinataire` varchar(255) NOT NULL,     // idem
  `telephone_destinataire` varchar(255) NOT NULL,   // idem
  `valeur` int(11) NOT NULL,
  `nature` text NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` datetime DEFAULT NULL,
  `etat` int(11) DEFAULT '1',
  `user_id` int(11) NOT NULL,       // très bien pour faire un lien !!!! mais pourrait faire parti d'une table de jointure
  `agence_id` int(11) NOT NULL,     // pareil
  `agence_id1` int(11) NOT NULL,    // pareil
  `code_colis` varchar(255) DEFAULT NULL,   // idem id....
  `residence_expediteur` varchar(100) DEFAULT NULL,     // devrait provenir d'une autre table
  `slug` varchar(500) DEFAULT NULL,
  `nom_autre_destinataire` varchar(255) DEFAULT NULL,   // devrait provenir d'une autre table
  `prenom_autre_destinataire` varchar(255) DEFAULT NULL,     // devrait provenir d'une autre table
  `numero_autre_destinataire` varchar(255) DEFAULT NULL,     // devrait provenir d'une autre table
  `phone_autre_destinataire` varchar(255) DEFAULT NULL,      // devrait provenir d'une autre table
  `date_retrait` datetime DEFAULT NULL,
  `lieu_payement` varchar(100) DEFAULT NULL,    // devrait provenir d'une autre table genre jointure ou order
  `type_payement` varchar(100) DEFAULT NULL,    // devrait provenir d'une autre table genre jointure ou order
  `provenance` varchar(100) DEFAULT 'online',   // devrait provenir d'une autre table genre jointure ou order
  `numero_facture` int(11) DEFAULT NULL,    // devrait provenir d'une autre table
  `passage` int(11) DEFAULT NULL,
  `cloture_caisse` int(11) DEFAULT '0',     // devrait provenir d'une autre table
  `idsession` varchar(100) DEFAULT NULL,    // devrait provenir d'une autre table
  `date_reception` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  // devrait provenir d'une autre table
  `agent_retrait` int(11) DEFAULT '0',  // devrait provenir d'une autre table
  `sync` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
112438
,

je ne pourrai t'aider mieux

1