dongyi1921
dongyi1921
2018-08-07 13:02

提高查询速度Ionic / Laravel Post请求

I'm trying to increase the speed of my query, currently it takes around 15 seconds for all the data to get returned. I have an Ionic 3 app which is sending a post request to get all inventory and my Laravel 5.4 server is handling the request.

Here's my query :

    $input = file_get_contents( "php://input" );

    $request = json_decode( $input );
    $dealer_id = $request->dealer_id;

    $tmp = Inventory::where(
            'dealer_id', '=', $dealer_id
        )->where(
            'inventories.is_sold', '=', 0
        )->where(
            'is_active','=', 1
    );

    // dd($tmp);

    $data = collect();
    $pmt = $tmp->get();
    logger( sprintf('# of rows returned: %s', $pmt->count() ) );

    $pmt->each( function($row) use(&$data) {
      logger( sprintf('Row    : %s', $row->toJson() ));

        $data->push( array(
            'stock_number' => $row->stock_number,
            'vehicle_id' => $row->vehicle_id,
            'year' => $row->vehicle()->first()->year,
            'make' => $row->vehicle()->first()->make,
            'model' => $row->vehicle()->first()->model,
            // 'trim' => $row->vehicle()->first()->trim,
            'vin' => $row->vehicle()->first()->vin,
            'status' => $row->vehicle_status,
            'purchase_price' => $row->purchase_price,
            'cost' => $row->cost,
            // 'retail_price' => $row->retail_price,
            'search_meta' => $row->search_meta,
            // 'interior_color' => $row->vehicle()->first()->interior_color,
            // 'exterior_color' => $row->vehicle()->first()->exterior_color,
            'firstImg' => $row->getFirstImage(),
            'images' => Vimage::select('vehicle_id','name'
            )->where(
                'dealer_id', '=', $row->dealer_id
            )->where(
                'vehicle_id', '=', $row->vehicle_id
            )->get()
        ));

    });

    $statusKey = \App\lt_vehicle_status::where(
        'dealer_id', '=', $dealer_id
    )->where(
        'is_active','=', 1
    )->get();

    $response = [
       "status" => "Success",
       "code" => "MAC01",
       "reason" => "MAC - Inventory Gathered Successfully",
       "data" => $data,
       "status_keys" => $statusKey
   ];

   echo json_encode( $response );

Data Returned:

https://i.imgur.com/zxWSNo5.png

One of the biggest issues is getting all the image urls as well as all the vehicles.

Thank you to whomever can help me try to increase my speed and efficiency of this.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • doujiao2014 doujiao2014 3年前

    You have too many queries to get the vehicles and images. In the vehicles case, you can reduce them to 1 for each Inventory record by eager-loading the relationship:

    $tmp = Inventory::with('vehicle')
            where(
                'dealer_id', '=', $dealer_id
            )->where(
                'inventories.is_sold', '=', 0
            )->where(
                'is_active','=', 1
        );
    

    If the image is a relationship on Inventory you can add it to the with method call, if not you can collect the image search parameters separately and then perform a single select

    点赞 评论 复制链接分享
  • duanrong0738 duanrong0738 3年前

    Why don't you bring everything in a single query? Use joins and select only the needed columns. If you need an array, you just have to add toArray() and that's it. Also, add indexes if you don't have them.

    点赞 评论 复制链接分享