CODEWITHSUNDEEP
phpmysqllaraveloptimization
Bobino
Bobino

Reputation: 1

Optimize laravel query many-to-many cascade

I'm trying to optimize my query because it takes far, far too long to execute.

My model consists of 6 tables, see ER diagram:

ER Schema

This is my Query builder:

 $events =  Event::orderBy('date','DESC')->with(['person', 'person.country', 'person.topics', 'train', 'station'])->get();

I have also already indexed the different tables.

Between topic and person there is a pivot table:

public function topics()
    {
        return $this->belongsToMany(Topic::class, 'person_topic')->withPivot('quantity')->withTimestamps();
    }

can someone help me ?

Upvotes: 0

Views: 353

Answers (1)

Armin Sameti
Armin Sameti

Reputation: 181

when you are using eager loading in your models it will translate your eloquent query to many WhereIn queries so for example the below code

$books = Book::with('author')->get();

foreach ($books as $book) {
    echo $book->author->name;
}

will be translated to these two queries:

select * from books

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

so based on your query if you want to fetch all the entities you have with all the relations I could suggest you use some join queries instead of using Laravel eager loading.

sample from laravel docs :

DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.id', 'contacts.phone', 'orders.price')
            ->get();

based on matiaslauriti comment you can also do some paginate on your query so it fetches from database chunk by chunk.

sample :

Flight::chunk(200, function ($flights) {
    foreach ($flights as $flight) {
        //
    }
});

there is also a Lazy method that does the same jobs too.

Upvotes: 1

Related Questions