mysql, group by, distinct, cakephp

Par romses, il y a 7 ans

Base de données MySQL

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éponses

saibe, il y a 7 ans

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...

romses, il y a 7 ans

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

saibe, il y a 7 ans

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 ?

romses, il y a 7 ans

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))

saibe, il y a 7 ans

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 ;)

romses, il y a 7 ans

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

romses, il y a 7 ans

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

saibe, il y a 7 ans

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

romses, il y a 7 ans

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

saibe, il y a 7 ans

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
romses, il y a 7 ans

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

saibe, il y a 7 ans

laquelle ? 1ere ou seconde ?

saibe, il y a 7 ans

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

romses, il y a 7 ans

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

saibe, il y a 7 ans

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
saibe, il y a 7 ans

vois-tu où je veux en venir ?

romses, il y a 7 ans

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

saibe, il y a 7 ans

... 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...
++

saibe, il y a 7 ans

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;
saibe, il y a 7 ans

je ne pourrai t'aider mieux

romses, il y a 7 ans

déjà l'ID est identique et c'est la clé elle est auto increment. pour le code c'est juste un code comme vous le dite mais qui n'est pas unique(c'est une exigence), concernant les autres champs, j'arrive à les gérer dans d'autres requêtes mais ici je ne comprends pas le pourquoi cela ne passe pas par contre sous sql server cà passe bien

saibe, il y a 7 ans

??? je ne comprends plus rien : id identique ? alors que c une clé ai ?? concernant le code, comment est-il généré ?
as tu pris en compte mon dernier post sur ta table colis ?

saibe, il y a 7 ans

pourrais tu m'expliquer ton app : c un site de vente ?
quelles sont tes contraintes, etc...

romses, il y a 7 ans

je veux dire que l'id est la clé c'est une erreur les éléments sont auto increment

romses, il y a 7 ans

mon App est une plate forme de gestion des colis des agences de voyages qui fonctionne déjà en intranet mais il y'a des mises à jours je doit faire conernant les factures c'est dessus je suis entrain de finaliser sauf que la il y'a e probleme avec mysql

saibe, il y a 7 ans

.... tu as accès aux DB ? tu peux les modifier ?
en fait, au final, tu voudrais récupérer les propositions qui ont été retenues pour les facturer ?

saibe, il y a 7 ans

si ça fonctionne en intra.... tu as une autre plate-forme à gérer ?

saibe, il y a 7 ans

bon, j'arrête, désolé. si tu veux continuer envoie une image de tes tables (avec relations). sinon je ne vois plus comment t'aider...