• 15 heures
  • Moyenne

Ce cours est visible gratuitement en ligne.

course.header.alt.is_certifying

J'ai tout compris !

Mis à jour le 11/05/2020

Query Builder

Connectez-vous ou inscrivez-vous gratuitement pour bénéficier de toutes les fonctionnalités de ce cours !

Nous avons jusqu'à présent utilisé Eloquent pour générer nos requêtes. Malgré la pertinence de celui-ci, il est parfois nécessaire de générer des requêtes qui dépassent ses compétences. C'est alors qu'intervient le Query builder, un outil pratique de génération de requêtes avec une syntaxe explicite qui est le parfait compagnon d'Eloquent.

Les données

Migration

Pour effectuer des tests, nous aurons besoin de données. Créez une nouvelle migration :

php artisan make:migration create_livres_table

Et entrez ce code :

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateLivresTable extends Migration {

    /**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('auteurs', function(Blueprint $table) {
			$table->increments('id');
			$table->timestamps();
			$table->string('nom', 100)->unique();
		});
		Schema::create('editeurs', function(Blueprint $table) {
			$table->increments('id');
			$table->timestamps();
			$table->string('nom', 100)->unique();
		});
		Schema::create('livres', function(Blueprint $table) {
			$table->increments('id');
			$table->timestamps();
			$table->string('titre', 100);
			$table->integer('editeur_id')->unsigned();
			$table->text('description');
		});
		Schema::create('auteur_livre', function(Blueprint $table) {
			$table->increments('id');
			$table->integer('auteur_id')->unsigned();
			$table->integer('livre_id')->unsigned();
		});
		Schema::table('livres', function(Blueprint $table) {
			$table->foreign('editeur_id')->references('id')->on('editeurs')
						->onDelete('restrict')
						->onUpdate('restrict');
		});
		Schema::table('auteur_livre', function(Blueprint $table) {
			$table->foreign('auteur_id')->references('id')->on('auteurs')
						->onDelete('restrict')
						->onUpdate('restrict');
		});
		Schema::table('auteur_livre', function(Blueprint $table) {
			$table->foreign('livre_id')->references('id')->on('livres')
						->onDelete('restrict')
						->onUpdate('restrict');
		});
	}

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
		Schema::table('livres', function(Blueprint $table) {
			$table->dropForeign('livres_editeur_id_foreign');
		});
		Schema::table('auteur_livre', function(Blueprint $table) {
			$table->dropForeign('auteur_livre_auteur_id_foreign');
		});
		Schema::table('auteur_livre', function(Blueprint $table) {
			$table->dropForeign('auteur_livre_livre_id_foreign');
		});
		Schema::drop('auteur_livre');
		Schema::drop('livres');
		Schema::drop('auteurs');
		Schema::drop('editeurs');
	}

}

Et lancez la migration :

php artisan migrate

Le résultat est la création de ces 4 tables avec ces relations :

Les 4 tables et leurs relations
Les 4 tables et leurs relations

On a comme relations :

  • 1:n entre editeurs et livres,

  • n:n entre auteurs et livres.

Population

Pour la population nous allons utiliser la librairie fzaninotto/Faker qui est chargée par défaut par Laravel. Cette librairie permet de générer des noms, adresses, textes, nombres... avec une grande facilité. C'est très pratique par exemple lorsqu'on effectue des tests.

Laravel intègre cette librairie et permet de créer des "Model Factories". Regardez ce fichier :

Le ModelFactory
Le ModelFactory

Avec ce code :

<?php

/*
|--------------------------------------------------------------------------
| Model Factories
|--------------------------------------------------------------------------
|
| Here you may define all of your model factories. Model factories give
| you a convenient way to create models for testing and seeding your
| database. Just tell the factory how a default model should look.
|
*/

$factory->define(App\User::class, function (Faker\Generator $faker) {
    return [
        'name' => $faker->name,
        'email' => $faker->email,
        'password' => bcrypt(str_random(10)),
        'remember_token' => str_random(10),
    ];
});

Vous avez un exemple avec un factory pour le modèle User. On va donc créer nos factories pour nos modèles :

<?php
$factory->define(App\Editeur::class, function (Faker\Generator $faker) {
    return [
        'nom' => $faker->name,
    ];
});

$factory->define(App\Auteur::class, function (Faker\Generator $faker) {
    return [
        'nom' => $faker->name,
    ];
});

$factory->define(App\Livre::class, function (Faker\Generator $faker) {
    return [
        'titre' => $faker->sentence(3),
        'description' => $faker->text,
        'editeur_id' => $faker->numberBetween(1, 40),
    ];
});

Ensuite on prévoit ce code dans  DatabaseSeeder :

<?php

use Illuminate\Database\Seeder;
use Faker\Factory;

class DatabaseSeeder extends Seeder {

    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        factory(App\Editeur::class, 40)->create();
        factory(App\Auteur::class, 40)->create();  
        factory(App\Livre::class, 80)->create(); 
        
        for ($i = 1; $i < 41; $i++) {
            $number = rand(2, 8);
            for ($j = 1; $j <= $number; $j++) {
                DB::table('auteur_livre')->insert([
                    'livre_id' => rand(1, 40),
                    'auteur_id' => $i
                ]);
            }
        }

    }

}

Lancez ensuite la population :

php artisan db:seed

On aura ainsi 40 éditeurs et 40 auteurs avec des noms aléatoires. On aura aussi 80 livres affectés à des éditeurs et aussi des relations entre les livres et les auteurs. Donc de quoi effectuer tranquillement des requêtes.

Il nous faut aussi les modèles pour faire marcher tout ça si on veut utiliser Eloquent. Pour les éditeurs :

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Editeur extends Model
{

    public function livres()
	{
		return $this->hasMany('App\Livre');
	}

}

Pour les auteurs :

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Auteur extends Model
{

    public function livres()
	{
		return $this->belongsToMany('App\Livre');
	}

}

Pour les livres :

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Livre extends Model
{

    public function auteurs()
	{
		return $this->belongsToMany('App\Auteur');
	}

	public function editeur()
	{
		return $this->belongsTo('App\Editeur');
	}

}

Les sélections

Liste des éditeurs

Commençons par des choses simples, on veut tous les éditeurs. Avec Eloquent c'est facile :

<?php
$editeurs = App\Editeur::all();
foreach ($editeurs as $editeur) {
    echo $editeur->nom, '<br>';
}

Vous obtenez ainsi la liste complète des éditeurs. Avec le Query Builder la syntaxe est la suivante :

<?php
$editeurs = DB::table('editeurs')->get();
foreach ($editeurs as $editeur) {
    echo $editeur->nom, '<br>';
}

Il faut bien désigner la table et utiliser ensuite la méthodeget. Le résultat est le même.

Tableau des valeurs d'une colonne

Pour obtenir un tableau des valeurs d'une colonne on utilise la méthodelists :

<?php
$editeurs = DB::table('editeurs')->lists('nom');
foreach ($editeurs as $editeur) {
    echo $editeur, '<br>';
}

Ce qui fonctionne aussi avec Eloquent :

<?php
$editeurs = App\Editeur::lists('nom');
foreach ($editeurs as $editeur) {
    echo $editeur, '<br>';
}

Ce qui correspond à cette requête SQL :

select nom from editeurs

Ligne particulière

Si nous voulons trouver une ligne particulière, avec Eloquent :

<?php
$editeur = App\Editeur::find(10);
echo $editeur->nom;

Nous aurons le nom de l'éditeur qui a l'id 10. Avec Query Builder il faut écrire :

<?php
$editeur = DB::table('editeurs')->whereId(10)->first();
echo $editeur->nom;

On précise la table, on précise l'id avec la méthodewhere et on prend le premier enregistrement (first).

En SQL pur on écrirait :

SELECT * FROM editeurs WHERE id = 10

Colonne isolée

On peut sélectionner juste une colonne avec la méthodepluck :

<?php
$editeur = DB::table('editeurs')->whereId(10)->pluck('nom');
dd($editeur);

Ce qui en SQL s'écrit :

select nom from editeurs where id = 10

On peut aussi faire un select pour sélectionner des colonnes :

<?php
$editeurs = App\Editeur::select('nom')->get();
foreach ($editeurs as $editeur) {
  echo $editeur->nom, '<br>';
}

Ou simplement avec le Query Builder :

<?php
$editeurs = DB::table('editeurs')->select('nom')->get();
foreach ($editeurs as $editeur) {
  echo $editeur->nom, '<br>';
}

Le requête est encore celle-ci :

select nom from editeurs

Lignes distinctes

On peut aussi utiliser la méthodedistinct pour avoir des lignes distinctes :

<?php
$livres = App\Livre::select('editeur_id')->distinct()->get();
foreach ($livres as $livre) {
    echo $livre->editeur_id, '<br>';
}

Et en version Query Builder :

<?php
$livres = DB::table('livres')->select('editeur_id')->distinct()->get();
foreach ($livres as $livre) {
    echo $livre->editeur_id, '<br>';
}

Avec cette requête SQL :

select distinct editeur_id from livres

Plusieurs conditions

On a vu qu'on peut utiliser la méthodewhere, on peut lui adjoindre la méthodeorWhere :

<?php
$livres = App\Livre::where('titre', '<', 'c')
->orWhere('titre', '>', 'v')
->get();
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

En version Query Buider :

<?php
$livres = DB::table('livres')
->where('titre', '<', 'c')
->orWhere('titre', '>', 'v')
->get();
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

Avec cette requête SQL :

select * from livres where titre < 'c' or titre > 'v'

Encadrer des valeurs

On peut encadrer des valeurs avecwhereBetween :

<?php
$livres = App\Livre::whereBetween('titre', array('g', 'k'))->get();
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

En version Query Builder :

<?php
$livres = DB::table('livres')->whereBetween('titre', array('g', 'k'))->get();
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

Avec cette requête SQL :

select * from livres where titre between 'g' and 'k'

On peut faire l'inverse  avecwhereNotBetween :

<?php
$livres = App\Livre::whereNotBetween ('titre', array('g', 'k'))->get();
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

En version Query Builder :

<?php
$livres = DB::table('livres')->whereNotBetween ('titre', array('g', 'k'))->get();
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

Avec cette requête SQL :

select * from livres where titre not between 'g' and 'k'

Prendre des valeurs dans un tableau

On peut aussi prendre des valeurs dans un tableau avecwhereIn :

<?php
$livres = App\Livre::whereIn('editeur_id', [10, 11, 12])->get();
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

En version Query Builder :

<?php
$livres = DB::table('livres')->whereIn('editeur_id', [10, 11, 12])->get();
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

Avec cette requête SQL :

select * from livres where editeur_id in ('10', '11', '12')

Ordonner et grouper

On peut aussi ordonner et grouper :

<?php
$livres = App\Livre::
select('editeur_id', DB::raw('count(id) as livre_count'))
->groupBy('editeur_id')
->get();
foreach ($livres as $livre) {
    echo $livre->editeur_id . ' => ' . $livre->livre_count, '<br>';
}

Ici on groupe les lignes par éditeur et on compte les livres. La clauseselect mérite un petit commentaire. Quand on ne sait pas faire quelque chose avec le Query Builder on a toujours la ressource d'utiliser une expression brute avecDB::raw comme je l'ai fait ici pour utiliser la fonctioncount de SQL.

En version Query Builder :

<?php
$livres = DB::table('livres')
->select('editeur_id', DB::raw('count(id) as livre_count'))
->groupBy('editeur_id')
->get();
foreach ($livres as $livre) {
    echo $livre->editeur_id . ' => ' . $livre->livre_count, '<br>';
}

Avec cette requête SQL :

select editeur_id, count(id) as livre_count from livres group by editeur_id

Les jointures

Trouver les titres des livres pour un éditeur dont on a l'id

Pour le moment on a vu des requêtes qui ne concernent qu'une seule table, ce qui n'est pas le plus répandu. Lorsque deux tables sont concernées on doit faire une jointure. Considérons un premier exemple : je veux les titres des livres pour un éditeur dont j'ai l'id :

<?php
$livres = App\Editeur::find(11)->livres;
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

Eloquent est à l'aise dans ce genre de recherche. Avec le Query Builder on doit faire :

<?php
$livres = DB::table('editeurs')
->where('editeurs.id', 11)
->join('livres', 'livres.editeur_id', '=', 'editeurs.id')
->get();
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

La jointure s'effectue avec la méthodejoin. Les requêtes générées dans les deux cas ne sont pas les mêmes, avec Eloquent on en a deux :

select * from editeurs where id = '11' limit 1
select * from livres where livres.editeur_id = '11'

Et avec Query Builder une seule :

select * from editeurs inner join livres on livres.editeur_id = editeurs.id where editeurs.id = '11'

En fait Eloquent dans ce cas ne fait pas de jointure.

Trouver les livres d'un auteur dont on connaît le nom

Maintenant cherchons les livres d'un auteur dont on connaît le nom :

<?php
$livres = App\Livre::whereHas('auteurs', function($q)
{
    $q->whereNom('Osbaldo White');
})->get();
foreach ($livres as $livre) {
	echo $livre->titre, '<br>';
}

Là encore Eloquent s'en tire élégamment. Il génère la requête suivante :

select * from livres where (select count(*) from auteurs inner join auteur_livre on auteurs.id = auteur_livre.auteur_id where auteur_livre.livre_id = livres.id and nom = 'Osbaldo White') >= '1'

La requête est un peu acrobatique mais elle fonctionne. Avec le Query Builder on peut écrire quelque chose comme ça :

<?php
$livres = DB::table('livres')
->join('auteur_livre', 'livres.id', '=', 'auteur_livre.livre_id')
->join('auteurs', 'auteurs.id', '=', 'auteur_livre.auteur_id')
->where('auteurs.nom', '=', 'Osbaldo White')
->get();
foreach ($livres as $livre) {
    echo $livre->titre, '<br>';
}

Une double jointure qui génère la requête :

select * from livre inner join auteur_livre on livres.id = auteur_livre.livre_id inner join auteurs on auteurs.id = auteur_livre.auteur_id where auteurs.nom = 'Osbaldo White'

Trouver les auteurs pour un éditeur dont on connaît l'id

Allons plus loin et trouvons les auteurs pour un éditeur dont on connaît l'id :

<?php
$livres = App\Editeur::find(10)->livres;
foreach ($livres as $livre) {
    foreach($livre->auteurs as $auteur) {
		echo $auteur->nom, '<br>';
	}
}

On trouve facilement les livres de l'éditeur, mais pour aller jusqu'aux auteurs il faut boucler sur les résultats. Voici les 3 requêtes générées :

select * from editeurs where id = '10' limit 1
select * from livres where livres.editeur_id = '10'
select auteurs.*, auteur_livre.livre_id as pivot_livre_id, auteur_livre.auteur_id as pivot_auteur_id from auteurs inner join auteur_livre on auteurs.id = auteur_livre.auteur_id where auteur_livre.livre_id = '28'

C'est bien optimisé. Voici ce qu'on peut réaliser avec le Query Builder :

<?php
$auteurs = DB::table('auteurs')
->select('auteurs.nom')
->join('auteur_livre', 'auteurs.id', '=', 'auteur_livre.auteur_id')
->join('livres', 'livres.id', '=', 'auteur_livre.livre_id')
->join('editeurs', function($join)
    {
		$join->on('editeurs.id', '=', 'livres.editeur_id')
		->where('editeurs.id', '=', 10);
	})
->get();
foreach($auteurs as $auteur) {
	echo $auteur->nom, '<br>';
}

C'est évidemment plus compact au point de vue requête avec une seule :

select auteurs.nom from auteurs inner join auteur_livre on auteurs.id = auteur_livre.auteur_id inner join livres on livres.id = auteur_livre.livre_id inner join editeurs on editeurs.id = livres.editeur_id and editeurs.id = '10'

Attention aux requêtes imbriquées

Il faut être prudent dans certaines situations. Par exemple supposez que vous voulez avoir la liste des auteurs avec pour chaque nom d'auteur la liste de ses ouvrages. Vous pourriez écrire ce genre de code :

<?php
$auteurs = App\Auteur::all();
foreach ($auteurs as $auteur) {
    echo '<h1>' . $auteur->nom . '</h1>';
	foreach($auteur->livres as $livre) {
		echo $livre->titre, '<br>';
	}
}

Ça fonctionne très bien mais... si vous regardez vos requêtes vous allez être effrayé ! Dans mon cas j'en trouve 41 ! Tout simplement parce que pour chaque auteur vous lancez une requête pour trouver ses livres. Dans ce genre de situation il faut absolument utiliser le chargement lié, c'est-à-dire demander à Eloquent de charger la table livres avec la méthode with :

<?php
$auteurs = App\Auteur::with('livres')->get();
foreach ($auteurs as $auteur) {
    echo '<h1>' . $auteur->nom . '</h1>';
	foreach($auteur->livres as $livre) {
		echo $livre->titre, '<br>';
	}
}

Le changement peut paraître minime mais on n'a plus que 2 requêtes maintenant :

select * from auteurs
select livres.*, auteur_livre.auteur_id as pivot_auteur_id, auteur_livre.livre_id as pivot_livre_id from livres inner join auteur_livre on livres.id = auteur_livre.livre_id where auteur_livre.auteur_id in ('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')

Comment faire la même chose avec le Query Builder ? Ce n'est pas si simple, voici une solution avec utilisation d'une expression brute :

<?php
$results = DB::table('auteurs')
->select('nom', DB::raw('group_concat(titre) as titres'))
->groupBy('nom')
->join('auteur_livre', 'auteurs.id', '=', 'auteur_livre.auteur_id')
->join('livres', 'livres.id', '=', 'auteur_livre.livre_id')
->get();
foreach ($results as $result) {
    echo '<h1>' . $result->nom . '</h1>';
	$titres = explode(',', $result->titres);
	foreach($titres as $titre) {
		echo $titre, '<br>';
	}
}

Maintenant on n'a plus qu'une seule requête :

select nom, group_concat(titre) as titres from auteurs inner join auteur_livre on auteurs.id = auteur_livre.auteur_id inner join livres on livres.id = auteur_livre.livre_id group by nom

Vous n'arriverez pas toujours à réaliser ce que vous désirez avec seulement Eloquent, il vous faudra alors utiliser le Query Builder. Nous avons vu dans ces quelques exemples comment l'utiliser.

En résumé

  • Eloquent permet de faire beaucoup de manipulations sur les tables et est à l'aise avec les relations.

  • Le Query Builder est le compagnon parfait pour Eloquent.

  • Parfois il est plus efficace d'utiliser le Query Builder qu'Eloquent.

  • Parfois on doit utiliser des expressions brutes dans les requêtes mais il faut alors penser à se protéger des injections SQL.

Exemple de certificat de réussite
Exemple de certificat de réussite