Datenbank-Laravel
Links
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();
Funktionen für "migrate"
Felddefinitionen
$table->id();
$table->timestamps();
$table->string('name', 64);
$table->string('plural', 64);
// foreign key of sproperties: but sproperties.id is integer, not biginteger
$table->integer('genus');
$table->text('usage');
// foreign key of users
$table->foreignId('verifiedby')->references('id')->on('users')->nullable();
$table->boolean('confirmed');
$table->bigIncrements('id'); // mit autoincrement
$table->binary('photo');
$table->char('name', 100);
$table->dateTime('created_at', $precision = 0);
$table->date('created_at');
$table->decimal('amount', $precision = 8, $scale = 2);
$table->double('amount', 8, 2);
$table->float('amount', 8, 2);
$table->longText('description');
$table->softDeletes($column = 'deleted_at', $precision = 0);
$table->time('sunrise', $precision = 0);
$table->timestamp('added_at', $precision = 0);
Indexe
$table->primary('id');
$table->primary(['id', 'parent_id']); // kombiniert
$table->index(['account_id', 'created_at']);
$table->unique('email');
$table->fullText('body');
$table->fullText('body')->language('english');
$table->renameIndex('from', 'to');
// Löschen
$table->dropPrimary('users_id_primary');
$table->dropUnique('users_email_unique');
$table->dropIndex('geo_state_index');
$table->dropFullText('posts_body_fulltext');
$table->dropForeign('posts_user_id_foreign');
// Constraints:
$table->unsignedBigInteger('user_id');
$table->foreign('user_id')->references('id')->on('users');
$table->foreignId('user_id')->constrained();
$table->foreignId('user_id')->constrained(table: 'users', indexName: 'posts_user_id');
$table->foreignId('user_id')->constrained()->onUpdate('cascade')->onDelete('cascade');
// constrained() immer zuletzt:
$table->foreignId('user_id')->nullable()->constrained();
// Aktivieren/Deaktivieren:
Schema::enableForeignKeyConstraints();
Schema::disableForeignKeyConstraints();
Schema::withoutForeignKeyConstraints(function () { /* Constraints disabled within this closure... */ });
Modifier
Nach Felddefinitionsfunktionen:
->unique()
->after('column') // Einordnung nach <column>
->autoIncrement()
->default($value)
->first() // Einordnung am Anfang
->nullable($value = true)
->useCurrent() // bei Timestamp: aktueller Zeitpunkt
->useCurrentOnUpdate()
Feldänderungen
Schema::table('users', function(Blueprint $table) {
$table->foreignId('role_id')->nullable()->constrained("roles")->cascadeOnUpdate()->nullOnDelete();
$table->string('name', 50)->change();
$table->integer('votes')->unsigned()->default(1)->change();
$table->renameColumn('from', 'to');
$table->dropColumn('votes');
$table->dropColumn(['votes', 'avatar', 'location']);
});