Laravel Database - atabegruslan/Notes GitHub Wiki

Relationships

Migration scripts

  • Migration is for database structure.
  • To run a DB migration script again:
    • php artisan migrate:rollback (which deletes the most recent batch out of the migrations table)
    • Or go into the DB, manually delete the entry out of the migrations table.
  • Seeding is for database data.
    • Make seed: php artisan make:seeder WhateverTableSeeder
    • Run seed: php artisan db:seed --class=WhateverTableSeeder

Timestamps and Soft Deletes

If you weren't using these before and decide to start using them

  1. Adust the database
    • For timestamps: add created_at & updated_at nullable columns of timestamp type, default now.
    • For soft delete: add deleted_at nullable column of timestamp type, default null.
  2. Make the migration script consistent by adding
Schema::create('whatevers', function (Blueprint $table) {
    ...
    $table->softDeletes();
    $table->timestamps();
});
  1. In model, add:
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Whatever extends Model
{
    use SoftDeletes;

    public $timestamps = true;

You can see that Illuminate\Database\Eloquent\Model.php::performDeleteOnModel() is overridden by Illuminate\Database\Eloquent\SoftDeletes.php::performDeleteOnModel()

https://www.itsolutionstuff.com/post/how-to-use-soft-delete-in-laravel-5example.html

PgSql

Have these in .env

DB_CONNECTION=pgsql
DB_HOST=127.0.0.1
POSTGRESQL_ADDON_DB=xxx
POSTGRESQL_ADDON_USER=postgres
POSTGRESQL_ADDON_PASSWORD=

Enable extension=pdo_pgsql in php.ini

Eloquent ORM or Query Builder (\DB)

Eloquent: https://hub.packtpub.com/eloquent-without-laravel

https://laravel-news.com/query-expressions-for-laravel

Sushi: array driver for Eloquent: https://laravel-news.com/laravel-sushi

Speed vs Changing DB

Eloquent is slower. But easier when changing DB, eg from MySQL to PostgreSQL

https://stackoverflow.com/questions/38391710/laravel-eloquent-vs-query-builder-why-use-eloquent-to-decrease-performance

Functionalities

Eloquent have more functionalities. (You can code for them in the model file)

Considerations for N+1 problem

With Query Builder, you can explicitly write queries with considerations for N+1 problem.
With Eloquent, you have to use things like with to enable considerations for N+1 problem. Eloquent won't do it by itself.

https://www.youtube.com/watch?v=uVsY_OXRq5o

N+1 problem

By default: lazy load.
But lazy load have N + 1 problem.
EG: Picture have Metadata. So if you want to retrieve N Pictures and their width: $picture->metadata->width, then for each picture another query will be run for metadata. Hence, you'll end up with N + 1 queries (N for Metadata and 1 for Picture).
Eager loading reduces this from N+1 to 2:

select * from pictures
select * from metadatas where id in (1, 2, 3, 4, 5, ...)

Eager loading have 2 functions that we can utilize: Picture::with('metadata')->get(); or Picture::all()->load('metadata');.

Better code for DB optimization

  1. Add indexes and FKs
  2. Use ->get() last. So group and order, then take the first n entries, and finally use ->get().
  3. Refer to relationship instead of fetching all related entries. So use $model->relation() instead of $model->relation. Use things like ->count() after that.

Also:

  • Use magic methods eg withCount.
  • Use with to avoid N+1 problem.

https://www.youtube.com/watch?v=yAAqAxiaEmg

Performance:

Correct syntax for Cache & Pagination:

Useful knowhow:

All in one command: php artisan migrate:fresh --seed --seeder=XxxSeeder

Common mistakes

Repo pattern