0

I have 3 models where I use many to many relationships

  • User
  • Position
  • UserPosition

Here my code where I tried create unique positions name in Position model then attach it to user.

Code:

public function createUserPosition()
{
    $user = User::find(1);
    $user->positions()->detach();

    $positions = [
        "developer",
        "coder",
        "singer"
    ];

    $ids = [];
    foreach ($positions as $name) {
        $position = Position::updateOrCreate(
            ["name" => $name],
            ["name" => $name]
        );
        $ids[] = $position->id;
    }

    $user->positions()->attach($ids);

    dd($user->positions);
}

Is it possible to optimize my code by reducing database queries?

Andreas Hunter
  • 4,504
  • 11
  • 65
  • 125
  • 1
    https://stackoverflow.com/questions/17472128/preventing-laravel-adding-multiple-records-to-a-pivot-table – Zain Mar 07 '20 at 08:15
  • use `sync` instead of `detach` and `attach` to reduce one query. [ref](https://laravel.com/docs/5.8/eloquent-relationships#updating-many-to-many-relationships) – iamab.in Mar 07 '20 at 08:20
  • Avoid queries inside a loop. If you are only updating the `name` field, then the update query is unnecessary. Only the `insert` query is needed – iamab.in Mar 07 '20 at 08:26
  • I'll update `name` field if exist same `position` with name but if it's doesn't exist I must insert it to table `positions`. Table `positions` has only 'name` field. How I can avoid queries inside a loop then in my case? @ab.in – Andreas Hunter Mar 07 '20 at 10:28
  • As there is only one field exist you don'y have to update the record if it exists. ie, If the position `developer` exists, then you don't have to update that record with same value `developer`. – iamab.in Mar 08 '20 at 12:26
  • Make a query with the `WhereIn('name', $positions)` method to get the existing records. loop through the results and pop out found values from the `$positions` array.Insert the remaining values using the `insert()` method after loop. You could run another query with `whereIn('name', $positions)` again to get the `ids`. Use `Sync` instead of `attach` and `detach` – iamab.in Mar 08 '20 at 12:34
  • Currently your controller method has 6 queries for 3 positions. But if number of positions increase number of queries will increase. **Using my method number of queries will be always 5 regardless of the number of positions** – iamab.in Mar 08 '20 at 12:42

0 Answers0