Faced a problem when sampling a large amount of data.
As it turned out, Laravel connects the tables through the IN operator, which is not very good when sampling> 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' user_id index field

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 the request yourself with pagination and handle this for example 50,000 each. Is it possible to do this with laravel means when describing the connection of the model.

  • four
    And custom where() or join() not an option? - VenZell
  • @VenZell yes option, but perhaps there are less obvious methods. - Shadow33
  • one
    Can you show a specific example showing your problem? You need an example of the structure of the tables and how you link them in the corresponding models. - VenZell
  • There should be no such samples anywhere in Laravel, even with the join of the millionth table, you cannot join. It is better to reference and remake the sample, remake the logic. Otherwise, there will be problems later. - Goncharov Alexander
  • @GoncharovAlexander clever words, but unfortunately you do not offer solutions to the problem. When I had a similar problem in Yii2 (similar frameworks), I solved it by adding counter fields to the user model (for example, the number of projects). Thus, for the main page, I had enough data from the User model, and already when I opened the user page, all its projects were pulled. - Makarenko_I_V

1 answer 1

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

This is not normal, joining millionare tables. It is necessary to change the data structures / algorithms - if this occurs. The structure of the data for the task can always be chosen, and it will work quickly. What is necessary specifically - not described, except how to large tables. Therefore, no one gives the answer.

PS Top portals, for example, facebook, vk - when using SQL prohibit join - just for this reason.

As it turned out, Laravel links tables through the IN operator.

If you do not want to bathe and leave the join, this is also a decision - but then why use the Laravel constructs for sampling from ORM, if they make the selection with joins not in the way they want? Is it not possible to get the DB adapter, enter the sample with the join th manually, get the id column, and then form user objects in a loop on them? I think you can! I even think that you can feed a sample of id-schnikas to a paginator object, which judging by the commentary is used: so that he himself sets the limit / offset or max id / min id.

I think it is possible in the "User Repository" (or it can be called the class of the user table) - add a method that forms for you such a list of User objects according to your sample. And this is architecturally true.

PS If in something specifically wrong, do not blame - Laravel had never seen. For something more than seen Zend (1,2,3), Yii