how to write query with "with" using laravel eloquent-CodePudding

How to make with laravel a query with with like the example below Thank you

with s as (select s.id from s)
select a.*
from a
left join s on s.id = a.s_id
...

CodePudding user response：

WITH clauses are not supported by Eloquent. You can either pass in a raw query

$sql = <<<SQL
with s as (select s.id from s)
select a.*
from a
left join s on s.id = a.s_id
...
SQL;

$results = DB::select($sql);

Or use a package that adds this functionality like staudenmeir/laravel-cte

DB::query()
    ->withExpression('s', fn ($query) => $query->select('s.id')->from('s'))
    ->select('a.*')
    ->from('a')
    ->leftJoin('s', 's.id', 'a.s_id')
    ...

CodePudding user response：

Assume the a table eloquent model name is ModelA and s table model is ModelS

Inside the ModelA.php

public function testRelation(){
  return $this->belongsTo(ModelS::class)
}

public function test(){
  return ModelA::select('*')->with(['testRelation'=> function ($q){
    $q->select('id');
  }])->get();
}