lundi 3 décembre 2018

Which one SQL query is more performanced according to ORM and result query?

First query:

$orders = OrderModel::when($request->has("orderStartDate"), function ($query, $role) use ($request) {
        return $query->where("DeliveryDate", ">=", $request->orderStartDate);
    })->whereExists(function ($query) use ($request) {
        $query->select('orders.*')
        ->from('orderrecipients')
        ->join('users', 'users.Id', '=', 'orderrecipients.Users_Id')
        ->whereRaw('orders.Id = orderrecipients.Orders_Id')
        ->where('users.Country_Id', $request->country);
    })->get();

Second query:

$orders = OrderModel::where(function ($query) {
    if($request->has("orderStartDate")) {
       $query->where("DeliveryDate", ">=", $request->orderStartDate);
    }  
})->whereExists(function ($query) use ($request) {
    $query->select('orders.*')
    ->from('orderrecipients')
    ->join('users', 'users.Id', '=', 'orderrecipients.Users_Id')
    ->whereRaw('orders.Id = orderrecipients.Orders_Id')
    ->where('users.Country_Id', $request->country);
})->get();

Third query:

$orders = OrderModel::where(function ($query) {
        if($request->has("orderStartDate")) {
           $query->where("DeliveryDate", ">=", $request->orderStartDate);
        } 

        if ($request->has("country")) {
        $query->select('orders.*')
        ->from('orderrecipients')
        ->join('users', 'users.Id', '=', 'orderrecipients.Users_Id')
        ->whereRaw('orders.Id = orderrecipients.Orders_Id')
        ->where('users.Country_Id', $request->country);
        }

    })->get();

At third query I am not confident, that INNER JOIN should be placed in WHERE conditional, perhaps ORM builder knows sequence to join right these query parts

Their nomenclature looks similar, but suspect result query is differ and ORM builder spends different executing time to build mentioned above queries.

Scope of these queries is to get rows from orders table where exist rows in table orderrecipients joined with table users with condition WHERE that was applied to users table.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire