CODEWITHSUNDEEP
laravel
M a m a D
M a m a D

Reputation: 2139

Laravel: Is it possible to use eagerloading after pagination?

Eagerloading with pagination is simple:

Model::with(['relation1', 'relation2'])->paginate();

There are 6 models M1, ..., M6 and model M1 has foreign key to models M2, ..., M6. There are at least 2,000,000 records in each model and model M1 has more than 10,000,000 records. The following statement

M1::paginate();

is fast enough but when relations are included, it takes more than 45 seconds to return the results. To improve the performance, I need to run the M1::paginate(); at the beginning, then include other relations.

My solution is to loop through the collection, gather the ids and add the relations. I would like to know does such thing have been implemented in Laravel before?

Upvotes: 0

Views: 46

Answers (1)

IGP
IGP

Reputation: 15786

Whenever you are unsure about how the queries made, open the console (php artisan tinker) and write the following:

DB::listen(fn($q) => dump([$q->sql, $q->bindings, $q->time]))

For each query you make (in the current console session), you'll get an array containing the SQL, the bindings and the time it actually takes for the database to return the data (this does not take into account how long it takes PHP to turn these results into an Eloquent Collection).

For example, for a Model (A) that has one hasMany relation with another Model (B), look at the output below:

>>> DB::listen(fn($q) => dump([$q->sql, $q->bindings, $q->time]))
=> null

>>> App\Models\A::with('b')->get()->first()->id
array:3 [
  0 => "select * from "a""
  1 => []
  2 => 0.0
]
array:3 [
  0 => "select * from "b" where "b"."a_id" in (1, 2, 3, 4, 5,
6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26,
27)"
  1 => []
  2 => 0.0
]
=> 1

>>> App\Models\A::with('b')->paginate(5)->first()->id
array:3 [
  0 => "select count(*) as aggregate from "a""
  1 => []
  2 => 0.0
]
array:3 [
  0 => "select * from "a" limit 5 offset 0"
  1 => []
  2 => 0.0
]
array:3 [
  0 => "select * from "b" where "b"."a_id" in (1, 2, 3, 4, 5)"

  1 => []
  2 => 0.0
]

As you can see, the pagination has an effect on the relationship queries made.

Upvotes: 1

Related Questions