Datenbank-Laravel

Links

• Laravel
• https://laravel.com/docs/10.x/queries

Zielsetzung

Auflistung der wichtigsten Klassen/Methoden für DB-Abfragen oder DB-Manipulation.

Reines SQL

$records = DB::select("select * from nouns where $condition order by name,id limit $limit", $parameters);
for ($records as $record){
  echo $record->id;
}

• Ergebnis ist eine Liste von Datensätzen

Querybuilder

Select-Ausdrücke

$users = DB::table('users')->get();
$users = DB::table('users')->select('name', 'email as user_email')->get();
$users = DB::table('users')->select(DB::raw('count(*) as user_count, status'))
   ->where('status', '<>', 1)->groupBy('status')->get();
DB::table('orders')->selectRaw('price * ? as price_with_tax', [1.0825])->get();
$orders = DB::table('orders')->select('department', DB::raw('SUM(price) as total_sales'))
  ->groupBy('department')->havingRaw('SUM(price) > ?', [2500])->get();
$orders = DB::table('orders')->orderByRaw('updated_at - created_at DESC')->get();
$orders = DB::table('orders')->select('city', 'state')
   ->groupByRaw('city, state')->get();

Datensätze beschränken: first() value() find()

$users = DB::table('users')->where('name', 'John')->first();
// only one column:
$email = DB::table('users')->where('name', 'John')->value('email');
// find it by the primary key
$user = DB::table('users')->find(3);

Batchverarbeitung mittels chunk()

DB::table('users')->orderBy('id')->chunk(100, function (Collection $users) {
  foreach ($users as $user) {
     // ...
    if(anyProblem()){
      return false;
    }
  }
  return true;
});

Where-Bedingungen

$users = DB::table('users')->where('votes', '=', 100)->where('age', '>', 35)->get();
$users = DB::table('users')->where([['status', '=', '1'],['subscribed', '<>', '1']])->get();
$users = DB::table('users')->where('votes', 100)->get();
$users = DB::table('users')->where('votes', '>', 100)->orWhere('name', 'John')->get();
$users = DB::table('users')->whereBetween('votes', [1, 100])->get();
$users = DB::table('users')->whereNotBetween('votes', [1, 100])->get();
$users = DB::table('users')->whereIn('id', [1, 2, 3])->get();
$users = DB::table('users')->whereNotIn('id', [1, 2, 3])->get();
$users = DB::table('users')->whereNull('updated_at')->get();
$users = DB::table('users')->whereNotNull('updated_at')->get();
// same values in 2 columns:
$users = DB::table('users')->whereColumn('first_name', 'last_name')->get();
$users = DB::table('users')->whereColumn('updated_at', '>', 'created_at')->get();
$activeUsers = DB::table('users')->select('id')->where('is_active', 1);
$users = DB::table('comments')->whereIn('user_id', $activeUsers)->get();

Datum/Zeit

$users = DB::table('users')->whereDate('created_at', '2016-12-31')->get();
$users = DB::table('users')->whereMonth('created_at', '12')->get();
$users = DB::table('users')->whereDay('created_at', '31')->get();
$users = DB::table('users')->whereYear('created_at', '2016')->get();
$users = DB::table('users')->whereTime('created_at', '=', '11:20:45')->get();

Order by

$users = DB::table('users')->orderBy('name', 'desc')->orderBy('email', 'asc')->get();
// latest() works on 'created_at':
$user = DB::table('users')->latest()->first();
$user = DB::table('users')->oldest()->first();

Aggregate

$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');

Joins

$users = DB::table('users')->join('contacts', 'users.id', '=', 'contacts.user_id')
  ->select('users.*', 'contacts.phone', 'orders.price')->get();
$users = DB::table('users')->leftJoin('posts', 'users.id', '=', 'posts.user_id')->get();
$users = DB::table('users')->rightJoin('posts', 'users.id', '=', 'posts.user_id')->get();
// Cross joins generate a cartesian product between the first table and the joined table
$sizes = DB::table('sizes')->crossJoin('colors')->get();

Limits

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