php – Optimizing the use of relationships on a large data sample


Faced a problem when fetching a large amount of data.
As it turned out, Laravel connects tables via the IN operator, which is not very good when selecting> 1M Model::with('user') we get something like id in (1,2,3, ... 1000000)

Example: There is a table of users of the form 'id', 'name', 'email', 'number', 'bdate' Each user can have several projects in the system 'id', 'user_id', 'title', 'data', 'price', 'mode' the user_id field is user_id

you need to select all users and their projects. Let the model look like this:

class User extends Model

    protected $table = 'users'

    public function project()
      return $this->hasMany('App/Project', 'user_id', 'id');


What are the workarounds?

Yes, you can write a request yourself with pagination and process this for example 50,000 each. Is it possible to do this using laravel when describing the model's connection.


which is not very good when fetching> 1M Model :: with ('user') we get something like id in (1,2,3, … 1000000)

It is not normal to join tables with a million population. Data structures / algorithms need to be changed – if this occurs. You can always choose a data structure for a task, and so that everything will work quickly. What is needed specifically is not described, except how to join large tables. Therefore, no one gives the answer.

PS Top portals, for example, Facebook, VK – when using SQL, join is prohibited – for this very reason.

As it turns out, Laravel links tables via the IN operator

You don’t want to take a steam bath and leave joins, this is also a solution – but then why use Laravel constructs for fetching from ORM, if they do not fetch with joins the way you want? Isn't it possible to get a database adapter there, enter a selection with join manually, get a column of id-schnicks, and then create user objects in a loop through them? I think you can! I even think it is possible to feed a sample of id-schnicks to a paginator object, which, judging by the comment, is used: so that he himself puts down the limit / offset or max id / min id.

I think it is also possible in the "User Repository" (or it can be called the class of the user table) – add a method that generates such a list of User objects for you based on your selection. And this is architecturally true.

PS If something is specifically wrong, do not blame – Laravel has never seen it. For more than seen Zend (1,2,3), Yii

Scroll to Top