Laravel hasOne Eloquent Relationship // Multiple queries instead of one?-CodePudding

I'm testing the speed of my application and I have multiple hasOne relationships. For example, an Order has one status ( order is pending , order is shipped etc)

Order Model

<?php

namespace App\Models;
use App\Models\OrderStatus;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    protected $primaryKey = 'order_id';

    public function status() {
        return $this->hasOne( OrderStatus::class , 'id' , 'order_status_id' );
    }
}

OrderStatus Model

<?php

namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class OrderStatus extends Model
{
    protected $primaryKey = 'id';
    protected $table = 'orders_status';
}

OrderController Both examples make two queries to the database

public function show ( $order_id ) {
    Order::with('status')->where('order_id' , $order_id)->firstOrFail(),
}
    
public function otherShowExample ( Order $order ) {
    $order->load('status');
    return view( 'order.show' , [ 
        'order' => $order,
        'order_status' => $order->status
    ] );
}

With Join is just one query to the database

public function showOneQuery ( $order_id ) {

    $order = Order::where('order_id' , $order_id)
    ->select('orders.*' , 'orders_status.orders_status_name' )
    ->join('orders_status' , 'orders_status.id' , '=' , 'orders.order_status_id')
    ->firstOrFail();

}

DB::listen for the OrderController@show and OrderController@otherShowExample

select * from `orders` where `order_id` = ? limit 1
select * from `orders_status` where `orders_status`.`id` = ? and `orders_status`.`id` is not null limit 1

In the OrderController@show, when trying to show to the user only one record, with the OrderStatus relationship, using the facade DB::listen I can see that are two queries made.

The question is : Is this the normal behavior of the hasOne relationship ? Is not better using the join() ? I'm doing something wrong ?

CodePudding user response：

Yes. It's normal behavior of HasOne relationship (and any other).

Methods like with and load realize eager loading.

So, first of all, Eloquent will get an item or a collection of items from database. Then it will load relationships.

CodePudding user response：

Yes, It's normal. In Laravel relationship, First laravel fetch a main model object and after that for each relationship it will fire individual query to load them. It's no matter with which type of relation we used and number of records we are processing. When we use a join with Model, it will fire a single query but a result set will be considered as a Order model instance, we can't identify a relationship data in that Order instance and if we try to check $order->status then it will fire a query for that.

In relationship, query will be simple one so it's easy to do indexing in a table but in a join query sometime it's difficult to do indexing to optimize a query and in that case, firing more number of queries will work faster then individual query. For multiple queries laravel will not initialize connection with DB as laravel initialize one time per request so it will not add that amount of extra time to initialize a connection.