如何使用laravel加速Mysql查询

Question

Webapp that has one function that is slow.

I ask to suggest code to speed up this function from 5-6 seconds to 1-2 seconds.

I have database with tables:

table: Workers (id, first_name, last_name)

table: Stores (id, name)

table: worker_store (id, worker_id, store_id)

The idea is that workers are connected to a single or multiple stores via worker_store table.

In Laravel app I have a function that takes a single store and shows that workers are connected to this particular store. This must be displayed as “select” field with connected workers marked as “selected” and all other workers as not selected. My Laravel blade code look like this

<select multiple name="workers[]" id="workers" class="form-control">
 @foreach (App\Worker::orderBy('last_name')->get() as $worker)                               
  <option value="{{$worker->id}}" 
  **@if ($object->hasWorker($worker->id))                                    
   selected @endif**>{{$worker->getName()}}
  </option>           
@endforeach  


</select>

Database is MySQL and worker table has 30,000 records. It is very slow while laravel makes 30 000 separate queries to the database to check every worker if he is linked to a particular store (bold part of code above).

Please suggest a solution that doesn't need to query 30 000 times database and make generation of full in no more than 1-2 seconds (current code takes about 5-6 seconds).

Answer 1

This would be a lot to put in the view but here's the basic concept of what I think you are looking for...

// Grab all the workers so that we can loop through them.
$workers = App\Worker::all();

// Grab a list of the worker id's for the store we are currently searching for.
$storeWorkers = App\Store::with('workers')->find(1)->workers->lists('id');

// Use in_array to determine if the worker is currently working in the store we are looking at.
@foreach ($workers as $worker)
    <option value="{{ $worker->id }}" {{ in_array($worker->id, $storeWorkers) ? 'selected' : '' }}>{{ $worker->name }}</option>
@endforeach

This should net you a total of 3 queries. 1 to grab all the workers and 2 to grab the workers by store.

It would seem your models are not setup correctly. In your Store model, add the function...

public function workers()
{
    return $this->belongsToMany(Worker::class, 'worker_store');
}