How to load a Model's relationship without loading the model itself.

I have af Farm model like this:

class Farm extends Model
{
//...
    public function salons()
    {
        return $this->hasMany(Salon::class);
    }

    public function flocks()
    {//using a lib here
        return $this->hasManyThrough(Flock::class, Salon::class);
    }
//...
    public static function fetchAllFlocksByDateAndId($farm_id, $date)
    {
        return Farm::with(['flocks' => function (Builder $query) use ($date) {
            $query->where('started_at', "<=", $date)
                ->where(function (Builder $query) use ($date) {
                    $query->whereNull('ended_at')
                        ->orWhere('ended_at', ">=", $date);
                });
        }])->find($farm_id);
    }
}

Then i use the this line to get the flocks

$flocks = Farm::fetchAllFlocksByDateAndId(Auth::user()->getFarmId(),$validated["date"]);

the result are like this:

{
  "id": 1,
  "user_id": 1,
  "name": "فارم شرکت تولیدی بهرو",
  "is_active": 1,
  "created_at": "2023-08-16T11:20:18.000000Z",
  "updated_at": "2023-08-16T11:20:18.000000Z",
  "flocks": [
    {
      "id": 1,
      "name": "گروه 5339",
      "family_id": 4,
      "line_id": 3,
      "is_hen": 0,
      "initial_age": 0,
      "initial_count": 5000,
      "current_count": 6000,
      "salon_id": 1,
      "started_at": "2023-08-16",
      "ended_at": null,
      "created_at": "2023-08-16T11:20:18.000000Z",
      "updated_at": "2023-08-16T11:20:18.000000Z",
      "laravel_through_key": 1
    },
	//...					
  ]
}

Question

how do i get rid of the first part that is the farm itself and only get the flocks array while still using eloquent and relationships specially since the ORM is doing an axtra query to fetch that data, you can see the db queries here:

[ // app\Http\Middleware\DebugMiddleware.php:31
  "sql" => "select * from `farms` where `farms`.`id` = ? limit 1"
  "bindings" => array:1 [
    0 => 1
  ]
  "time" => 12.27
]

[ // app\Http\Middleware\DebugMiddleware.php:31
  "sql" => "select `flocks`.*, `salons`.`farm_id` as `laravel_through_key` from `flocks` inner join `salons` on `salons`.`id` = `flocks`.`salon_id` where `salons`.`farm_id` in (1) and `started_at` <= ? and (`ended_at` is null or `ended_at` >= ?)"
  "bindings" => array:2 [
    0 => "2023-08-20"
    1 => "2023-08-20"
  ]
  "time" => 14.21
]

I know you can easily do this manually, but since i'm new to laravel i want to try my best to learn all it's offering inside the framework and ORM before i get hacky with it.

kevinbui

2 years ago

Best Answer

Level 41

What about moving that fetchAllFlocksByDateAndId to the the Flock model? A tradeoff is that we have a join a few more tables, should be fine:

class Flock extend Model
{
    public static function fetchAllFlocksByDateAndId($farm_id, $date)
    {

            return static::select('flocks.*')
                ->where('started_at', "<=", $date)
                ->where(function (Builder $query) use ($date) {
                    $query->whereNull('ended_at')
                        ->orWhere('ended_at', ">=", $date);
                })
                ->where('farms.id', $farm_id)
                ->join('salons', 'salons.id', '=', 'flocks.salon_id')
                ->join('farms', 'farms.id', '=', 'salons.farm_id')
                ->get();
    }  
}

Alternatively, we can do inline relationship existence queries:

class Flock extend Model
{
    public function salons()
    {
        return $this->belongsTo(Salon::class);
    }

    public static function fetchAllFlocksByDateAndId($farm_id, $date)
    {

            return static::select('flocks.*')
                ->where('started_at', "<=", $date)
                ->where(function (Builder $query) use ($date) {
                    $query->whereNull('ended_at')
                        ->orWhere('ended_at', ">=", $date);
                })
                ->whereRelation('salons', 'farm_id', $farm_id)
                ->get();
    }  
}

1 like

MansourM

2 years ago

Level 1

@kevinbui thanks,

I can use your example without the last join since i have the farm_id in salons. so it's a good result, but my point is to be able to use the relationships, it seems weird to me that i can't use a relationship without loading the parent model. (when i already have the parent id)

kevinbui

2 years ago

Level 41

@MansourM I have updated my answer with an alternative, you might want to have a look.

1 like

MansourM

2 years ago

Level 1

To sum things up, there are many ways to do this, the best way to do this via eloquent models and relationships seems to be the inline relationship existence queries as mentioned by @kevinbui.

Code

static::select('flocks.*')
            ->where('started_at', "<=", $date)
            ->where(function (Builder $query) use ($date) {
                $query->whereNull('ended_at')
                    ->orWhere('ended_at', ">=", $date);
            })
            ->whereRelation('salon', 'farm_id', $farm_id)
            ->get();

Query

[▼ // app\Http\Middleware\DebugMiddleware.php:31
  "sql" => "select `flocks`.* from `flocks` where `started_at` <= ? and (`ended_at` is null or `ended_at` >= ?) and exists (select * from `salons` where `flocks`.`salon_id` ▶"
  "bindings" => array:3 [▶]
  "time" => 1.17
]

this approach uses exists subquery which is more readable than a join but is slightly slower in most cases (according to stackoverflow:) , so if you want to go for best performance you need to go for a approach like this:

Code

static::select('flocks.*')
            ->where('started_at', "<=", $date)
            ->where(function (Builder $query) use ($date) {
                $query->whereNull('ended_at')
                    ->orWhere('ended_at', ">=", $date);
            })
            ->where('salons.farm_id', $farm_id)
            ->join('salons', 'salons.id', '=', 'flocks.salon_id')
            ->get();

Query

[▼ // app\Http\Middleware\DebugMiddleware.php:31
  "sql" => "select `flocks`.* from `flocks` inner join `salons` on `salons`.`id` = `flocks`.`salon_id` where `started_at` <= ? and (`ended_at` is null or `ended_at` >= ?) and `salons`.`farm_id` = ? ◀"
  "bindings" => array:3 [▶]
  "time" => 0.8
]

Please or to participate in this conversation.