Forum d'entraide PHPFrance

Bonjour à tous,

J'essaye vainement de faire un comptage un peu particulier sur une de mes tables, le tout si possible en full SQL...

Je vais déjà vous présenter la requête d'affichage, à partir de laquelle je souhaiterai faire mon "comptage"...

Voici la bête:

SELECT 
    t2 . * 
FROM 
    transactions AS t
LEFT JOIN 
    orders AS o 
ON 
    o.id_order = t.ref_order
LEFT JOIN 
    transactions AS t2 
ON 
    t2.id_transaction = t.id_transaction
WHERE 
    t.account_num = 123
    OR o.ref_user = 123
GROUP BY t2.id_auto

Et voici le contenu (simplifié) des tables transactions et orders:

--
-- Structure de la table `transactions`
--

CREATE TABLE IF NOT EXISTS `transactions` (
  `id_auto` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_transaction` int(10) unsigned NOT NULL,
  `ref_order` int(11) DEFAULT NULL,
  `ref_account_type` int(11) NOT NULL,
  `account_num` int(11) DEFAULT NULL,
  `libelle` varchar(250) DEFAULT NULL,
  `debit` float DEFAULT '0',
  `credit` float DEFAULT '0',
  `vat` float DEFAULT '0',
  `amount_ht` float DEFAULT NULL,
  `blocked_payment_target` int(11) DEFAULT NULL,
  `date_payment_insert` datetime DEFAULT NULL,
  `date_payment_valid` datetime DEFAULT NULL,
  `date_modification` datetime DEFAULT NULL,
  `date_extraction` datetime DEFAULT NULL,
  `date_extraction_wanted` datetime DEFAULT NULL,
  `canceled` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id_auto`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;

--
-- Contenu de la table `transactions`
--

INSERT INTO `transactions` (`id_auto`, `id_transaction`, `ref_order`, `ref_account_type`, `account_num`, `libelle`, `debit`, `credit`, `vat`, `amount_ht`, `blocked_payment_target`, `date_payment_insert`, `date_payment_valid`, `date_modification`, `date_extraction`, `date_extraction_wanted`, `canceled`) VALUES
(1, 1, 1, 5116, 14, 'Opération n°1', 29, 0, 4.75, 24.24, NULL, '2010-10-19 09:46:17', '2010-10-19 09:46:24', NULL, NULL, NULL, 0),
(2, 1, 1, 4671, 123, 'Opération n°1', 0, 29, 4.75, 24.24, NULL, '2010-10-19 09:46:17', '2010-10-19 09:46:24', NULL, NULL, NULL, 0),
(3, 3, 1, 4671, 123, 'Opération n°1', 29, 0, 4.75, 24.24, NULL, '2010-10-19 09:46:17', '2010-10-19 09:46:25', NULL, NULL, NULL, 0),
(4, 3, 1, 411, 123, 'Opération n°1', 0, 29, 4.75, 24.24, NULL, '2010-10-19 09:46:17', '2010-10-19 09:46:25', NULL, NULL, NULL, 0),
(5, 5, 2, 5112, 12, 'Opération n°2', 29, 0, 4.75, 24.24, NULL, '2010-10-19 09:49:39', '2010-10-19 09:50:09', NULL, NULL, NULL, 0),
(6, 5, 2, 4671, 123, 'Opération n°2', 0, 29, 4.75, 24.24, NULL, '2010-10-19 09:49:39', '2010-10-19 09:50:09', NULL, NULL, NULL, 0),
(7, 7, 2, 4671, 123, 'Opération n°2', 29, 0, 4.75, 24.24, NULL, '2010-10-19 09:49:39', '2010-10-19 09:50:09', NULL, NULL, NULL, 0),
(8, 7, 2, 411, 123, 'Opération n°2', 0, 29, 4.75, 24.24, NULL, '2010-10-19 09:49:39', '2010-10-19 09:50:09', NULL, NULL, NULL, 0),
(9, 9, NULL, 5116, 14, 'Opération n°3', 29, 0, 4.75, 24.24, NULL, '2010-10-19 11:32:46', '2010-10-19 11:32:46', NULL, NULL, NULL, 0),
(10, 9, NULL, 4671, 123, 'Opération n°3', 0, 29, 4.75, 24.24, NULL, '2010-10-19 11:32:46', '2010-10-19 11:32:46', NULL, NULL, NULL, 0),
(11, 11, NULL, 5116, 14, 'Opération n°4', 29, 0, 4.75, 24.24, NULL, '2010-10-19 14:41:31', '2010-10-19 14:41:31', NULL, NULL, NULL, 0),
(12, 11, NULL, 4671, 123, 'Opération n°4', 0, 29, 4.75, 24.24, NULL, '2010-10-19 14:42:13', '2010-10-19 14:42:13', NULL, NULL, NULL, 0);


--
-- Structure de la table `orders`
--

CREATE TABLE IF NOT EXISTS `orders` (
  `id_order` int(11) NOT NULL AUTO_INCREMENT,
  `ref_user` int(11) NOT NULL
  PRIMARY KEY (`id_order`,`ref_user`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Contenu de la table `orders`
--

INSERT INTO `orders` (`id_order`, `ref_user`,) VALUES
(1, 123),
(2, 123);

Le problème c'est qu'ensuite, pour l'affichage, je fait un traitement php un peu complexe (du moins à traiter en sql) que voilà:

 while($transact = mysql_fetch_assoc($results)){
    
    // commande associée
    if(!empty($transact['ref_order'])){

      if(!isset($formated_results['o'.$transact['ref_order']])){
        $formated_results['o'.$transact['ref_order']] = array(
            'line_type'=>'order',
            'ref_order'=>$transact['ref_order']
        );
      }

      if($transact['ref_account_type']==transaction::USER_CA){
        $formated_results['o'.$transact['ref_order']]['id_transaction']=$transact['id_transaction'];
        $formated_results['o'.$transact['ref_order']]['libelle']=$transact['libelle'];
        $formated_results['o'.$transact['ref_order']]['date_payment_insert']=$transact['date_payment_insert'];
        $formated_results['o'.$transact['ref_order']]['date_payment_valid']=$transact['date_payment_valid'];
      }

      if($transact['account_num']<100){
        $formated_results['o'.$transact['ref_order']]['payment_way']=$transact['nom_payment_way'];
        $formated_results['o'.$transact['ref_order']]['amount_ttc']=$transact['debit'];
        $formated_results['o'.$transact['ref_order']]['amount_ht']=$transact['amount_ht'];
        $formated_results['o'.$transact['ref_order']]['vat']=$transact['vat'];
      }
    }
    // paiement tout seul
    else {

      if(!isset($formated_results['t'.$transact['id_transaction']])){
        $formated_results['t'.$transact['id_transaction']] = array(
            'line_type'=>'transaction',
            'id_transaction'=>$transact['id_transaction'],
            'ref_order'=>null
        );
      }

      if($transact['account_num']<100){
        $formated_results['t'.$transact['id_transaction']]['payment_way']=$transact['nom_payment_way'];
        $formated_results['t'.$transact['id_transaction']]['amount_ttc']=$transact['debit'];
        $formated_results['t'.$transact['id_transaction']]['amount_ht']=$transact['amount_ht'];
        $formated_results['t'.$transact['id_transaction']]['vat']=$transact['vat'];
        $formated_results['t'.$transact['id_transaction']]['libelle']=$transact['libelle'];
        $formated_results['t'.$transact['id_transaction']]['date_payment_insert']=$transact['date_payment_insert'];
        $formated_results['t'.$transact['id_transaction']]['date_payment_valid']=$transact['date_payment_valid'];
      }
    }
  }

En bref, ça rend ceci sur le papier (pas de commentaires sur le design siouplai :-°)

En gros, lorsqu'une commande est associé à un paiement, je condense quatre enregistrements en un, et lorsque ça n'est pas le cas, je combine de enregistrement en un...
Donc j'ai testé un

SELECT COUNT(*) FROM transactions GROUP BY ref_order

pour ensuite compter le nombre des résultats retournés (une ligne par commande) sauf que bien sûr tous les paiements qui ne sont pas liés à une facture sont regroupés et on perd le décompte... :/

J'avoue que je reste bloqué!

Merci d'avance pour votre aide, n'hésitez pas à demander plus d'infos!

Cordialement,

J'ai noté un détail, le parallèle entre le ref_order et le libellé : je suis donc parti sur cet élément est j'ai juste ajouté le champ approprié dans le GROUP BY :

mysql> SELECT COUNT(*) AS lignes
    -> FROM transactions 
    -> GROUP BY ref_order, libelle;
+--------+
| lignes |
+--------+
|      2 |
|      2 |
|      4 |
|      4 |
+--------+
4 rows in set (0.01 sec)

À partir de ton jeu d'essai, j'ai donc bien maintenant les 4 lignes que tu souhaitais incluant la séparation entre les lignes n'ayant pas de ref_order.

Sinon, dans la mesure où cette colonne ne peut pas être prise en compte, peut-être pourrais-tu envisager une requête UNION, la première avec une clause WHERE ref_order IS NOT NULL et la seconde avec WHERE ref_order IS NOT NULL + les éléments te permettant le tri voulu.

Bonjour,

Merci pour cette réponse qui m'a mis sur la voie!
Comme tu as a pu t'en douter, le group by fonctionne en l'état mais je ne veux pas me baser sur le champs libelle car ils vont varier.

Par contre ton idée d'UNION m'as aidé.
J'ai testé pas mal de COUNT() ... UNION , ça n'a pas fonctionné, mais je me suis rendu compte du coup que j'avais simplement deux cas de traitements différents...Donc je fait deux requête différentes... une petite addition en php...
Et hop!
ça donne ceci:

SELECT count(*)
FROM cdr_transactions_new
WHERE account_num = 123 AND ref_order > 0
AND ref_account_type = 4671 AND credit > 0

+

SELECT count(*)
FROM cdr_transactions_new
WHERE account_num = 123 AND ref_order IS NULL
AND ref_account_type = 4671 AND credit > 0

Donc un cas avec commande liée et l'autre sans.

Par contre j'entrevois des soucis pour la suite: la pagination :/
Je ne m'y suis pas encore penché donc on verra bien, mais je vois mal comment déterminer mon limit...

Je laisse donc se sujet ouvert

Merci encore!

L'idée que j'explorerais pour pouvoir utiliser une clause LIMIT, ce serait de faire un SELECT sur la requête UNION. Schématiquement :

SELECT
  col_1, col_2,..., col_n
FROM (
    SELECT 
        col_1, col_2,..., col_n
    FROM table_x
    WHERE condition IS NOT NULL
    UNION
    SELECT 
        col_1, col_2,..., col_n
    FROM table_x
    WHERE condition IS NULL
) LIMIT x, y;

Jamais testé mais je vois pas de raison pour que ça ne fonctionne pas.

Sinon pour simplifier, tu pourrais créer une vue à aprtir de la requête UNION et ensuite taper directement sur la vue avec la clause LIMIT ou d'autres clsues de tri.

Bonjour,

Le soucis c'est que pour l'affichage, je ne fait pas les requêtes "UNION"...
C'est la requête postée plus haut:

SELECT 
    t2 . * 
FROM 
    transactions AS t
LEFT JOIN 
    orders AS o 
ON 
    o.id_order = t.ref_order
LEFT JOIN 
    transactions AS t2 
ON 
    t2.id_transaction = t.id_transaction
WHERE 
    t.account_num = 123
    OR o.ref_user = 123
GROUP BY t2.id_auto

Et cette dernière renvoie plus de ligne qu'a l'affichage ensuite....
Donc je ne peux pas faire de clause LIMIT là-dessus je pense...

Je me demande par contre, si je ne vais pas finir par faire deux pages de résultats, une pour les transactions avec commande liée et une autre pour le reste...
Normalement je pourrais utiliser LIMIT du coup

Alors des nouvelles du front!

N'ayant pas réussit à faire quelque chose de correct, j'ai botté en touche
En effet au lieu de paginer sur un nombre fixe d'entrée, je fait une pagination par mois!
Donc plus aucuns soucis!

Merci encore pour ton aide

Les nouvelles du front pour ce qui me concerne : j'ai perdu un cheveu Non, j'déconne, j'ai plus de cheveux sur le front depuis belle lurette.

As-tu exploré l'idée de créer une vue ? Parce que je reste convaincu que ce serait une piste de solution solide pour ton problème général. Botter en touche, ça aide quand on est pressé et que les contraintes ne sont pas trop strictes. Ça dépend en fait de ce qui est attendu au bout. Les possibilités en SQL sont beaucoup plus importantes que ce qu'on imagine volontiers, et les possibilités offertes par les SGBDRs sont en général largement sous-exploitées, et en fouillant, j'ai fini par découvrir au fil du temps des options qui étaient disponibles et quasiment sous mon nez sans que je le soupçonne au départ.