[LARAVEL] Sous requetes Query Builder

Axis
PHP

Bonjour à tous, je bug avec le le Query Builder de Laravel je ne sais pas comment faire ma requête pour extraire un tableau de statistique sur une table.

J’ai une table ‘suply’ qui comporte différentes colonnes dont :

  • date, DATE
  • type, BOOLEAN
  • line_type, VARCHAR(3)
  • parcels, INT(11)

Pour l’explication sur cette table je stocke la date et le nombre de colis (parcels) chargés (type = 1) ou déchargés (type =2) pour chaque ligne de transport. Cette ligne peut être interne à la société (line_type = ‘INT’) ou extrene (line_type = ‘EXT’).

Je cherche à extraire des statistiques par mois pour chaque élément suivants :

  • Nombre de colis chargés (donc type =1) pour les lignes internes (line_type = ‘INT’)
  • Nombre de colis déchargés (donc type =0) pour les lignes internes (line_type = ‘INT’)
  • Nombre de colis chargés (donc type =1) pour les lignes internes (line_type = ‘EXT’)
  • Nombre de colis déchargés (donc type =0) pour les lignes internes (line_type = ‘EXT’)

En SQL pur j’ai la requête suivante :

SELECT CONCAT(YEAR(date), ‘-‘, MONTH(date)),
            (SELECT SUM(parcels) FROM supply WHERE type = 1 AND line_type=’INT’ AND YEAR(date) = YEAR(a.date) AND MONTH(date) = MONTH(a.date) AS load_int_parcels,
            (SELECT SUM(parcels) FROM supply WHERE type = 0 AND line_type=’INT’ AND YEAR(date) = YEAR(a.date) AND MONTH(date) = MONTH(a.date) AS unload_int_parcels,
            (SELECT SUM(parcels) FROM supply WHERE type = 1 AND line_type=’EXT’ AND YEAR(date) = YEAR(a.date) AND MONTH(date) = MONTH(a.date) AS load_ext_parcels,
            (SELECT SUM(parcels) FROM supply WHERE type = 0 AND line_type=’EXT’ AND YEAR(date) = YEAR(a.date) AND MONTH(date) = MONTH(a.date) AS unload_ext_parcels
FROM supply a
GROUP BY YEAR(date), MONTH(date)

Par contre je n’arrive pas du tout à l’adapter avec le Query Builder de Laravel.
Quelqu’un aurait-il une piste ?

J’espère avoir été clair…

5 réponses

Axis
Auteur
Réponse acceptée

J'ai réussi en fait je m'y prenais mal dans ma requete je n'avais pas besoin de faire un SELECT dans un SELECT j'ai simplement rajouter des conditions :

SELECT CONCAT(YEAR(a.date), '-', MONTH(a.date)),
  SUM(CASE WHEN type = 1 AND line_type = 'INT' THEN parcels ELSE 0 END) AS load_int_parcels,
  SUM(CASE WHEN type = 1 AND line_type = 'EXT' THEN parcels ELSE 0 END) AS load_ext_parcels,
  SUM(CASE WHEN type = 0 AND line_type = 'INT' THEN parcels ELSE 0 END) AS unload_int_parcels,
  SUM(CASE WHEN type = 0 AND line_type = 'EXT' THEN parcels ELSE 0 END) AS unload_ext_parcels
FROM rage_supply a
GROUP BY YEAR(date), MONTH(date)

ça donne ça :

        $stats_per_months = Supply::select([
                \DB::raw('CONCAT(YEAR(date), MONTH(date)) AS id,
                    CONCAT(YEAR(date), \'-\', MONTH(date)) AS month,
                    SUM(CASE WHEN type = 1 AND line_type = \'INT\' THEN parcels ELSE 0 END) AS load_int_parcels,
                    SUM(CASE WHEN type = 1 AND line_type = \'EXT\' THEN parcels ELSE 0 END) AS load_ext_parcels,
                    SUM(CASE WHEN type = 0 AND line_type = \'INT\' THEN parcels ELSE 0 END) AS unload_int_parcels,
                    SUM(CASE WHEN type = 0 AND line_type = \'EXT\' THEN parcels ELSE 0 END) AS unload_ext_parcels')
            ])
            ->groupBy(\DB::raw('YEAR(date)'), \DB::raw('MONTH(date)'))
            ->orderBy(\DB::raw('YEAR(date)'), 'DESC')
            ->orderBy(\DB::raw('MONTH(date)'), 'DESC')
            ->take(12)
            ->get();
mxmaxime

Salut, dans la documentation tu trouves toutes les méthodes dont tu as besoin

select()
groupBy()

Lis la documentation du QueryBuilder: https://laravel.com/docs/5.2/queries

Axis
Auteur

Si seulement j'avais trouvé dans la documentation comment faire un SELECT dans un SELECT je n'aurais pas posté ici. Amoins que j'ai mal regardé mais merci quand meme Emix

mxmaxime

Tu peux aussi exécuter une query avec DB::raw

Axis
Auteur

J'ai déjà essayé mais ça me retourne le meme résultat pour chaque mois alors je dois m'y prendre mal...
Voici ce que j'ai tenté :

        $stats_per_months = Supply::select([
                \DB::raw('CONCAT(YEAR(date), MONTH(date)) AS id,
                    CONCAT(YEAR(date), \'-\', MONTH(date)) AS month,
                    (SELECT SUM(parcels) FROM rage_supply X WHERE x.type = 1 AND x.line_type = \'INT\' AND YEAR(x.date) = YEAR(date) AND MONTH(x.date) = MONTH(date)) AS load_int_parcels,
                    (SELECT SUM(parcels) FROM rage_supply X WHERE x.type = 0 AND x.line_type = \'INT\' AND YEAR(x.date) = YEAR(date) AND MONTH(x.date) = MONTH(date)) AS unload_int_parcels,
                    (SELECT SUM(parcels) FROM rage_supply X WHERE x.type = 1 AND x.line_type = \'EXT\' AND YEAR(x.date) = YEAR(date) AND MONTH(x.date) = MONTH(date)) AS load_ext_parcels,
                    (SELECT SUM(parcels) FROM rage_supply X WHERE x.type = 0 AND x.line_type = \'EXT\' AND YEAR(x.date) = YEAR(date) AND MONTH(x.date) = MONTH(date)) AS unload_ext_parcels')
            ])
            ->groupBy(\DB::raw('YEAR(date)'), \DB::raw('MONTH(date)'))
            ->orderBy(\DB::raw('YEAR(date)'), 'DESC')
            ->orderBy(\DB::raw('MONTH(date)'), 'DESC')
            ->take(12)
            ->get();