Lucru cu Baza de Date MySQL: Constructor de Comenzi SQL

- Returnare rezultate pe bucati
- Metode: max, min, avg, sum
- Clauze Where
- Clauze Where avansate
- Metode pentru Order, Group, Limit, si Offset

Constructorul de Comenzi SQL din Laravel ofera o interfata fluenta pentru crearea si efectuarea de comenzi SQL la baza de date.
- Foloseste extensia PDO cu parametri de protectie SQL injection. Nu e nevoie de filtrarea datelor adaugate in instructiunile SQL.

Mai intai adaugati clasa Facades DB in controller:

use Illuminate\Support\Facades\DB;

Apoi, se foloseste metoda DB::table() pentru a incepe construirea interogarii SQL.

Returnare Rezultate

Puteti aplica si inlantui mai multe metode la DB::table('table_name') pentru a obtine rezultatele pe care le vreti.

Selectare toate randurile dintr-un tabel MySQL

Pentru a obtine toare randurile dintr-un tabel, aplicati metoda get().
- Exemplu: Simplu UserController cu o metoda index() care obtine toti utilizatorii si returneaza un view.

<?php
namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller {
  //Show a list of all of the users.
  //@return view
  public function index(){
    $users = DB::table('users')->get();
    return view('user.index', ['users' => $users]);
  }
}

Metoda get() returneaza un array cu obiecte pentru fiecare rand. Numele coloanelor sunt proprietati in obiect.
Puteti folosi o instructiune foreach() pentru a parcurge rezultatele.

foreach($users as $user){
  echo $user->name;
}

- Sau in fisierul view blade:

@foreach($users as $user)
  {{$user->name}}
@endforeach

- Pentru a obtine rezultate distincte, aplicati metoda distinct():

$users = DB::table('users')->distinct()->get();

Preluarea unui singur rand / coloana dintr-un tabel

Pentru a obtine un singur rand din tabelul bazei de date, puteti folosi metoda first(). Ea returneaza un singur obiect cu numele coloanelor ca proprietati:

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

echo $user->name;

- Daca aveti nevoie de valoarea unei singure coloane dintr-un rand, aplicati metoda value('column_name'). Ea returneaza direct valoarea coloanei:

$email = DB::table('users')->where('id', '8')->value('email');

Preluarea unei liste cu valorile coloanelor

Pentru a obtine un rezultat cu valorile unei singure coloane din toate randurile, puteti folosi metoda pluck('column_name'):

$titles = DB::table('articles')->pluck('title');

foreach($titles as $title){
  echo '<br>'.$title;
}

- Puteti de asemenea sa specificati un index personalizat pentru coloana returnata:

$titles = DB::table('articles')->pluck('title', 'key');

foreach($titles as $key=>$title){
  echo '<br>'.$title;
}

Selectare coloane specificate

Pentru a selecta anumite coloanele dintr-un tabel, folositi metoda select() cu numele coloanelor ca argumente (puteti adauga si un Alias, cu "as"):

$users = DB::table('users')->select('name', 'emails as mail')->get();

Daca aveti deja o instanta a constructorului de comenzi SQL si doriti sa mai adaugati o coloana la select-ul existent, puteti folosi metoda addSelect():

$query = DB::table('users')->select('name', 'emails as mail');

$users = $query->addSelect('age')->get();

Expresii Raw SQL

Pentru a crea o expresie raw SQL, puteti folosi metoda DB::raw(). Aceste expresii vor fi incluse in interogarec SQL ca siruri (nu sunt filtrate automat anti SQL injection).

$users = DB::table('users')->select(DB::raw('count(*) as nru, status'))
->where('status', '<>', 1)->groupBy('status')->get();

Returnare rezultate pe bucati

Metoda chunk() este folositoare cand aveti nevoie sa lucrati cu mii de inregistrari ale bazei de date.
Aceasta metoda preia cate o parte din rezultate si plaseaza fiecare parte intr-o functie Callback pentru procesare.
- Aici este un exemplu cu selectarea intregului tabel de utilizatori, in bucati de cate 100 de inregistrari odata:

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

Pentru a opri preluarea altor bucati, aplicati return false; in functia Callback:

DB::table('users')->orderBy('id')->chunk(100, function($users){
  //...
  return false;
});

Metode: max, min, avg, sum

Constructorul de comenzi din Laravel furnizeaza o varietate de metode aditionale: max(), min(), avg(), si sum(). Puteti apela oricare dintre aceste metode dupa ce ati construit interogarea:

$nr_users = DB::table('users')->count();

$price = DB::table('orders')->min('price');

- Puteti combina aceste metode cu alte clauze:

$price = DB::table('orders')->where('finalized', 1)->avg('price');

Clauze Where

Pentru a adauga clauze WHERE la interogare, puteti folosi metoda where() .
Apelul de baza la where() necesita trei argumente:
- Numele coloanei.
- Un operator.
- Al treilea argument este valoare evaluata in raport cu coloana.

Daca vreti doar sa verificati egalitatea unei coloane cu valoarea data, puteti pune valoarea direct ca al doilea argument in metoda where().

Spre exemplu, aici aveti o interogare care selecteaza randurile in care valoarea coloanei "votes" este egala cu 10:

$users = DB::table('users')->where('votes', '=', 10)->get();
//or:
$users = DB::table('users')->where('votes', 10)->get();

- Puteti folosi o diferite tipuri de operatori in clauza WHERE:

$users = DB::table('users')->where('votes', '>=', 10)->get();

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

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

Puteti adauga array-uri cu mai multe conditii in functia where() (acestea vor fi legate intre ele cu operatorul AND):

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

Conditii SQL cu OR

Puteti utiliza functia where() impreuna cu orWhere(), care este folosita pentru conditii cu 'OR' intre ele. Metoda orWhere() accepta aceleasi argumente ca si where():

$users = DB::table('users')->where('votes', '>', 8)
->orWhere('name', 'MarPlo')->get();

Clauze Where aditionale

whereBetween() - verifica daca valoarea unei coloane este intre doua valori.
```
$users = DB::table('users')->whereBetween('votes', [1, 10])->get();
```
whereNotBetween() - verifica daca valoarea unei coloane este in afara a doua valori.
```
$users = DB::table('users')->whereNotBetween('votes', [1, 6])->get();
```
whereIn() - verifica daca valoarea unei coloane se afla in valorile dintr-un array.
```
$users = DB::table('users')->whereIn('id', [1, 2, 3])->get();
```
whereNotIn() - verifica daca valoarea unei coloane nu se afla in valorile dintr-un array.
```
$users = DB::table('users')->whereNotIn('id', [1, 2, 3])->get();
```

whereNull() - verifica daca valoarea coloanei este NULL.

$users = DB::table('users')->whereNull('address')->get();

whereNotNull() - verifica daca valoarea coloanei nu este NULL.

$users = DB::table('users')->whereNotNull('address')->get();

whereDate() - pentru a compara valoarea unei coloane cu o data calendaristica.
```
$users = DB::table('users')->whereDate('created_at', '2017-10-15')->get();
```

whereYear() - pentru a compara valoare unei coloane cu un an.

$users = DB::table('users')->whereYear('created_at', '2017')->get();

whereMonth() - pentru a compara valoarea unei coloane cu o luna specificata.
```
$users = DB::table('users')->whereMonth('created_at', '10')->get();
```
whereDay() - Pentru a compara valoarea unei coloane cu o zi specificata dintr-o luna.
```
$users = DB::table('users')->whereDay('created_at', '15')->get();
```

whereColumn() - Pentru a verifica o conditie intre doua coloane.

$users = DB::table('users')->whereColumn('col_1', '=', 'col_2')->get();

$users = DB::table('users')->whereColumn('col_1', '>', 'col_2')->get();

- Puteti sa adaugati array-uri pentru mai multe conditii WHERE. Aceste conditii for avea intre ele operatorul AND:

$users = DB::table('users')->whereColumn([
 ['col_1', '=', 'col_2'],
 ['col_2', '>', 'col_3']
])->get();

Clauze Where avansate

Constructorul de comenzi Laravel poate gestiona grupari imbricate de clauze WHERE sau "where exists".

Grupare conditii WHERE

Aici aveti un exemplu de grupare imbricata cu mai multe conditii WHERE:

DB::table('users')->where('name', '=', 'MarPlo')->orWhere(function($query){
  $query->where('votes', '>', 10)->where('title', '<>', 'Admin');
})->get();

Functia adaugata ca argument in metoda orWhere() primeste o instanta de constructor de comenzi pe care o puteti folosi pentru a defini conditiile care sa fie cuprinse in grupuri de paranteze.
- Exemplul de mai sus produce urmatorul SQL:

select * from users where name='MarPlo' or (votes >10 and title <> 'Admin')

Clauze Where Exists

Metoda whereExists() permite sa scrieti clauze SQL where exists. Metoda whereExists() accepta o functie ca argument, care va primi o instanta de constructor se comenzi care va permite sa definiti interogarea ce trebuie pusa in clauza SQL "exists":

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

Interogarea de mai sus produce urmatorul SQL:

select * from users where exists (
select 1 from orders where orders.user_id = users.id
)

Clauze Where JSON

Laravel suporta interogarea coloanelor de tip JSON in baze de date care furnizeaza suport pentru tipuri de coloane JSON: MySQL 5.7+ si Postgres.
Pentru a interoga elementele dintr-o coloana JSON, folositi operatorul "->" intre ele:

$users = DB::table('users')->where('preferences->meal', 'salad')->get();

Metode pentru Order, Group, Limit, si Offset

orderBy() - permite sa ordonati rezultatul interogarii dupa o coloana specificata.
Primul argument e coloana dupa care vreti sa ordonati, al doilea argument poate fi asc sau desc.
```
$users = DB::table('users')->orderBy('name', 'desc')->get();
```
latest() / oldest() - permita sa ordonati cu usurinta rezultatele dupa o coloana cu data calendaristica.
```
$user = DB::table('users')->latest('date_col')->first();
```

inRandomOrder() - returneaza aleatoriu rezultate interogarii.

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

groupBy() / having() - poate fi folosit pentru a grupa rezultatele interogarii.
```
$users = DB::table('users')->groupBy('col')->having('col', '>', 10)->get();
```

havingRaw() - pentru a adauga un sir SQL ca instructiune in clauza having.

$users = DB::table('orders')->select('product', DB::raw('SUM(price) as sales'))
 ->groupBy('product')->havingRaw('SUM(price) > 25')->get();

skip() / take() - pentru a limita numarul de rezultate returnate, sau pentru a sari un numar dat de rezultate in interogare.
```
$users = DB::table('users')->skip(10)->take(5)->get();
```
- Alternativ puteti folosi metodele limit() si offset().
```
$users = DB::table('users')->offset(10)->limit(5)->get();
```

- Documentatie: Laravel - Database: Query Builder

<<-Exemplu simplu de aplicatie .. -- Baza de Date MySQL ..->>

Lucru cu Baza de Date MySQL: Constructor de Comenzi SQL

Curs Php-mysql

Returnare Rezultate

Selectare toate randurile dintr-un tabel MySQL

Preluarea unui singur rand / coloana dintr-un tabel

Preluarea unei liste cu valorile coloanelor

Selectare coloane specificate

Expresii Raw SQL

Returnare rezultate pe bucati

Metode: max, min, avg, sum

Clauze Where

Conditii SQL cu OR

Clauze Where aditionale

Clauze Where avansate

Grupare conditii WHERE

Clauze Where Exists

Clauze Where JSON

Metode pentru Order, Group, Limit, si Offset

Un Test simplu in fiecare zi

HTML

CSS

JavaScript

PHP-MySQL

Engleza

Spaniola

Last accessed pages

Popular pages this month