Utilisation de la base de données (basée sur le composant de base de données Laravel)

Récupérer toutes les lignes

<?php
namespace app\controller;

use support\Request;
use support\Db;

class UserController
{
    public function all(Request $request)
    {
        $users = Db::table('users')->get();
        return view('user/all', ['users' => $users]);
    }
}

Récupérer des colonnes spécifiques

$users = Db::table('user')->select('name', 'email as user_email')->get();

Récupérer une ligne

$user = Db::table('users')->where('name', 'John')->first();

Récupérer une colonne

$titles = Db::table('roles')->pluck('title');

Utiliser la valeur du champ id comme index

$roles = Db::table('roles')->pluck('title', 'id');

foreach ($roles as $id => $title) {
    echo $title;
}

Récupérer une valeur unique (champ)

$email = Db::table('users')->where('name', 'John')->value('email');

Supprimer les doublons

$email = Db::table('user')->select('nickname')->distinct()->get();

Traitement par lots

Si vous devez traiter des milliers d'enregistrements de base de données, lire toutes ces données en une seule fois peut être long et risquer d'entraîner des erreurs mémoire. Dans ce cas, vous pouvez envisager d'utiliser la méthode chunkById. Cette méthode récupère un petit morceau du jeu de résultats à la fois et le passe à une fonction de fermeture pour le traiter. Par exemple, nous pouvons diviser toutes les données de la table users en morceaux de 100 enregistrements :

Db::table('users')->orderBy('id')->chunkById(100, function ($users) {
    foreach ($users as $user) {
        //
    }
});

Vous pouvez arrêter de récupérer des résultats par morceaux en retournant false dans la fonction de fermeture.

Db::table('users')->orderBy('id')->chunkById(100, function ($users) {
    // Traiter les enregistrements...

    return false;
});

Remarque : N'essayez pas de supprimer des données dans la fonction de rappel, cela pourrait entraîner l'absence de certains enregistrements dans le jeu de résultats.

Agrégation

Le générateur de requêtes fournit également diverses méthodes d'agrégation, telles que count, max, min, avg, sum, etc.

$users = Db::table('users')->count();
$price = Db::table('orders')->max('price');
$price = Db::table('orders')->where('finalized', 1)->avg('price');

Vérifier si un enregistrement existe

return Db::table('orders')->where('finalized', 1)->exists();
return Db::table('orders')->where('finalized', 1)->doesntExist();

Expression brute

Prototype

selectRaw($expression, $bindings = [])

Parfois, vous pourriez avoir besoin d'utiliser des expressions brutes dans vos requêtes. Vous pouvez utiliser selectRaw() pour créer une expression brute :

$orders = Db::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();

De même, des méthodes d'expressions brutes telles que whereRaw(), orWhereRaw(), havingRaw(), orHavingRaw(), orderByRaw(), groupByRaw() sont également disponibles.

Db::raw($value) est également utilisé pour créer une expression brute, mais il n'a pas de fonctionnalité de liaison de paramètres et doit donc être utilisé avec précaution contre les injections SQL.

$orders = Db::table('orders')
                ->select('department', Db::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > ?', [2500])
                ->get();

Instruction Join

// jointure
$users = Db::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();

// leftJoin            
$users = Db::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

// rightJoin
$users = Db::table('users')
            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

// crossJoin   
$users = Db::table('sizes')
            ->crossJoin('colors')
            ->get();

Instruction Union

$first = Db::table('users')
            ->whereNull('first_name');

$users = Db::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

Instruction Where

Prototype

where($column, $operator = null, $value = null)

Le premier paramètre est le nom de la colonne, le deuxième paramètre est un opérateur supporté par n'importe quel système de base de données, et le troisième est la valeur à comparer avec la colonne

$users = Db::table('users')->where('votes', '=', 100)->get();

// Lorsque l'opérateur est un signe égal, il peut être omis, donc cette expression a le même effet que la précédente
$users = Db::table('users')->where('votes', 100)->get();

$users = Db::table('users')
                ->where('votes', '>=', 100)
                ->get();

$users = Db::table('users')
                ->where('votes', '<>', 100)
                ->get();

$users = Db::table('users')
                ->where('name', 'like', 'T%')
                ->get();

Vous pouvez également passer un tableau de conditions à la fonction where :

$users = Db::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

La méthode orWhere accepte les mêmes paramètres que la méthode where :

$users = Db::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

Vous pouvez passer une fonction de fermeture à la méthode orWhere en tant que premier paramètre :

// SQL: select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
$users = Db::table('users')
            ->where('votes', '>', 100)
            ->orWhere(function($query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();

La méthode whereBetween / orWhereBetween vérifie si la valeur du champ est entre deux valeurs données :

$users = Db::table('users')
           ->whereBetween('votes', [1, 100])
           ->get();

La méthode whereNotBetween / orWhereNotBetween vérifie si la valeur du champ est en dehors de deux valeurs données :

$users = Db::table('users')
                    ->whereNotBetween('votes', [1, 100])
                    ->get();

La méthode whereIn / whereNotIn / orWhereIn / orWhereNotIn vérifie si la valeur du champ doit exister dans un tableau donné :

$users = Db::table('users')
                    ->whereIn('id', [1, 2, 3])
                    ->get();

La méthode whereNull / whereNotNull / orWhereNull / orWhereNotNull vérifie si le champ spécifié doit être NULL :

$users = Db::table('users')
                    ->whereNull('updated_at')
                    ->get();

La méthode whereNotNull vérifie si le champ spécifié ne doit pas être NULL :

$users = Db::table('users')
                    ->whereNotNull('updated_at')
                    ->get();

Les méthodes whereDate / whereMonth / whereDay / whereYear / whereTime sont utilisées pour comparer la valeur d'un champ à une date donnée :

$users = Db::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();

Les méthodes whereColumn / orWhereColumn sont utilisées pour comparer si les valeurs de deux champs sont égales :

$users = Db::table('users')
                ->whereColumn('first_name', 'last_name')
                ->get();

// Vous pouvez également passer un opérateur de comparaison
$users = Db::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

// La méthode whereColumn peut également accepter un tableau
$users = Db::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at'],
                ])->get();

Groupement des paramètres

// select * from users where name = 'John' and (votes > 100 or title = 'Admin')
$users = Db::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

whereExists

// select * from users where exists ( select 1 from orders where orders.user_id = users.id )
$users = Db::table('users')
           ->whereExists(function ($query) {
               $query->select(Db::raw(1))
                     ->from('orders')
                     ->whereRaw('orders.user_id = users.id');
           })
           ->get();

orderBy

$users = Db::table('users')
                ->orderBy('name', 'desc')
                ->get();

Tri aléatoire

$randomUser = Db::table('users')
                ->inRandomOrder()
                ->first();

Le tri aléatoire peut avoir un impact considérable sur la performance du serveur, il n'est pas recommandé de l'utiliser.

groupBy / having

$users = Db::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();
// Vous pouvez passer plusieurs paramètres à la méthode groupBy
$users = Db::table('users')
                ->groupBy('first_name', 'status')
                ->having('account_id', '>', 100)
                ->get();

offset / limit

$users = Db::table('users')
                ->offset(10)
                ->limit(5)
                ->get();

Insertion

Insertion d'un seul enregistrement

Db::table('users')->insert(
    ['email' => 'john@example.com', 'votes' => 0]
);

Insertion de plusieurs enregistrements

Db::table('users')->insert([
    ['email' => 'taylor@example.com', 'votes' => 0],
    ['email' => 'dayle@example.com', 'votes' => 0]
]);

ID auto-incrémenté

$id = Db::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
);

Remarque : Lors de l'utilisation de PostgreSQL, la méthode insertGetId utilisera par défaut id comme le nom du champ auto-incrémenté. Si vous devez obtenir un ID à partir d'une autre « séquence », vous pouvez passer le nom du champ en tant que deuxième paramètre à la méthode insertGetId.

Mise à jour

$affected = Db::table('users')
              ->where('id', 1)
              ->update(['votes' => 1]);

Mise à jour ou insertion

Parfois, vous voudrez peut-être mettre à jour un enregistrement existant dans la base de données, ou le créer s'il n'existe pas :

Db::table('users')
    ->updateOrInsert(
        ['email' => 'john@example.com', 'name' => 'John'],
        ['votes' => '2']
    );

La méthode updateOrInsert essaiera d'abord de trouver les enregistrements correspondants dans la base de données à l'aide des clés et valeurs du premier paramètre. Si l'enregistrement existe, il sera mis à jour avec les valeurs du deuxième paramètre. Si aucun enregistrement ne correspond, un nouvel enregistrement sera inséré, et les données de ce nouvel enregistrement seront une combinaison des deux tableaux.

Auto-incrément & Auto-diminution

Ces deux méthodes acceptent au moins un paramètre : le champ à modifier. Le deuxième paramètre est facultatif et détermine le montant de l'incrément ou de la décrémentation :

Db::table('users')->increment('votes');

Db::table('users')->increment('votes', 5);

Db::table('users')->decrement('votes');

Db::table('users')->decrement('votes', 5);

Vous pouvez également spécifier les champs à mettre à jour pendant l'opération :

Db::table('users')->increment('votes', 1, ['name' => 'John']);

Suppression

Db::table('users')->delete();

Db::table('users')->where('votes', '>', 100)->delete();

Si vous devez vider une table, vous pouvez utiliser la méthode truncate, qui supprimera toutes les lignes et réinitialisera l'ID auto-incrémenté à zéro :

Db::table('users')->truncate();

Transactions

Voir Transactions de base de données

Verrouillage pessimiste

Le générateur de requêtes contient également des fonctions qui peuvent vous aider à réaliser un « verrouillage pessimiste » dans la syntaxe select. Pour réaliser un « verrou partagé » dans une requête, vous pouvez utiliser la méthode sharedLock. Le verrou partagé empêche les colonnes de données sélectionnées d'être altérées jusqu'à ce que la transaction soit validée :

Db::table('users')->where('votes', '>', 100)->sharedLock()->get();

Ou bien, vous pouvez utiliser la méthode lockForUpdate. Utiliser un verrou « update » empêche les lignes d'être modifiées ou sélectionnées par d'autres verrous partagés :

Db::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

Débogage

Vous pouvez utiliser dd ou dump pour afficher les résultats des requêtes ou les instructions SQL. La méthode dd affiche les informations de débogage et interrompt l'exécution de la requête. La méthode dump affiche également les informations de débogage mais n'interrompt pas l'exécution :

Db::table('users')->where('votes', '>', 100)->dd();
Db::table('users')->where('votes', '>', 100)->dump();

Remarque
Le débogage nécessite l'installation de symfony/var-dumper, commande composer require symfony/var-dumper