Exemple de requêtes SQL avancées

Le but ici n’est pas de refaire la documentation de référence mais plutôt de donner d’une part quelques exemples et d’autre part de mettre en avant quelques fonctions bien utiles. 
C’est une librairie de fonctions que j’utilise fréquemment et qu’il me faut souvent copier pour ne pas faire d’erreurs.

Join: lier 2 tables sur un champ

Exemple tout simple d’un requête join qui lie 2 tables en fonction d’un champ.

$favorites = DB::table('favoris')
	->join('annonces', 'annonces.id', '=', 'favoris.idannonce')   
	->select('annonces.genre', 'favoris.*') 
	->where('favoris.iduser','=',$current_user_id)
	->whereNull('bla_bla');
	->whereNotNull('bla_bla_bla')
	->get();

Exemple d’une requête imbriquée

Exemple suivant montre comment créer une requête imbriquée en utilisant une variable pour la condition. La variable doit être passée avec le mot-clé use.

$allads = DB::table('anonnces')
	->join('anonnces_lang', function ($join)  use ($lang)  {
		$join->on('anonnces.id', '=', 'anonnces_lang.idfiche')
		->where('anonnces_lang.lang', $lang);
	})
	->select('anonnces.*', 'anonnces_lang.ville','anonnces_lang.ville_url')
	->orderBy('ville', 'asc')
	->whereNull('date_pause')
	->where('date_fin','>',date("Y-m-d H:i:s") )->get(); 

Champ calculé, raw sql

Exemple d’une requête générée par Laravel qui contient entre autres des champs calculés par le moteur de base de données.

$annonces = DB::table('annonces')
    ->select(DB::raw("annonces.id, annonces.name,
        if(`date_fin`<'".date("Y-m-d H:i:s")."',1,0) as annonce_active,
        if(`date_pause` is null,0,1) as annonce_pause
    "))
    ->where('name','like','a%')
    ->orderBy('created_at', 'asc') 
->get();

Champ calculé pour retourner un count avec un group by

Exemple d’une requête générée par Laravel qui contient entre autres des valeurs count avec un group by

$reviews = DB::table('reviews')
        ->select(DB::raw('count(*) as nb_reviews, idads'))
        ->where('status', 1)
        ->groupBy('idads')
        ->get();

$cronlog = DB::table('cron_log')
        ->select("*",DB::raw(" TIMESTAMPDIFF(SECOND,date_start,date_end) as duration"))
        ->orderBy('status','desc');

Comparer 2 champs dans une table

Dans certains cas, il est utile de comparer 2 champs dans une table. Dans mon cas, j’utilise cela pour ajouter une latence à une action. Ma cron a besoin de comparer le plus vite possible 2 champs. Pour effectuer une telle action, j’utilise la fonction whereRaw de la façon suivante:

$all = DB::table('annonces')->whereRaw('npa!=last_npa')->get();

Incrémenter, décrémenter un champ

Le système de génération de requête fournit également des méthodes pratiques pour incrémenter ou décrémenter un champ du nombre que vous désirez, et même modifier des valeurs en même temps.

//ajoute 1 au champ views
DB::table('annonces')->where('id', $idannonce)->increment('views');
//enlève 3 au champs views
DB::table('annonces')->where('id', $idannonce)->decrement('views',3);
//incrément de 1 le champs vue et ajoute la date du jour dans le champs last_view
DB::table('annonces')->where('id', $idannonce)->increment('views',1,['last_view'=>date('Y-m-d')]);
//il n'est pas possible d’incrémenter 2 champs en même temps. Pour réaliser cela, il faut faire:
DB::table('banner_pub')->where('id', 1)
   ->update([
      'view' => DB::raw('view + 1'),
      'view_day' => DB::raw('view_day + 1')
   ]);

Modifier des données avec la fonction update

//Modifier 1 champ 
DB::table('annonces')->where('id', $idannonce)->update(['ma_var' =>9]);
//Modifier plusieurs champs
DB::table('annonces')->update(['ma_var' => 0,'ma_var2'=>0]);
DB::table('annonces')->where('id', $idannonce)->update(['ma_var' => 0,'ma_var2'=>0]);
//Mettre champ a null
DB::table('annonces')->where('id',$idannonce)->update(['review_date' =>null]);

Utilisation de where, orWhere et whereIn

Les exemples suivants montrent comment utilise where ou orwhere en hiérarchisant les where. C’est compliqué a expliquer mais je pense que l’exemple suivant va vous le faire comprendre. Je montre aussi une utilisation de whereIn.

$yestrerday=date('Y-m-d H:i:s', strtotime("-1 days"));
$annonces = DB::table('annonces')
	->where('date_fin','<',$yestrerday)
	->orWhere('member_premium',1)
        ->limit(10)
	->get(); 

// Faire plusieurs OU sur des champs 
$fiches = DB::table('annonces')
->where('id',$idannonce)  
->where(function ($query) use ($iduser) {
	$query->where('iduser',$iduser)
	->orWhere('idvendeur',$iduser);
})
->get(); 
// Where avec un tableau de valeurs
$log = DB::table('log_fiche')
->whereIn('type_action', ['update', 'delete','reset'])
->get();