phpsqllaraveleloquent

I have defined relatioships in models but how can i write complex queries in Eloquent way


I have a query in existing application now i need to update query in eloquent way i have the following structure of my relationships how can i write the following query in eloquent way

Controller Query that i want to change in eloquent way

foreach ($data['Divisions'] as $sections) {

$SecQry1 = "SELECT sections.id, sections.division_id, sections.code, sections.name ,specifications.description 
                    from specifications inner JOIN sections on specifications.section_id=sections.id where specifications.status='active'
                    AND specifications.manufacturer_id = $user->id AND sections.division_id=" . $sections->id . " group by sections.id";


$SectionsDivision1 = DB::select($SecQry1);

foreach ($SectionsDivision1 as $key => $selectionDiv) {
    array_push($selectionDiv_array1, $selectionDiv);
}

}
class Specification extends Model {

    protected $table = 'specifications';

    public function section()
    {
        return $this->belongsTo('App\Section');
    }
}

class Section extends Model {

protected $table = 'sections';

public function specifications()
{
    return $this->hasMany('App\Specification', 'section_id');
}
public function division()
{
    return $this->belongsTo('App\Division');
}

}

class Division extends Model {

    protected $table = 'divisions';
    protected $fillable = array('name', 'description', 'code','status');

    public function sections()
    {
        return $this->hasMany('App\Section', 'division_id');
    }
}
  0 => {#1063 ▼
    +"id": 1
    +"division_id": 1
    +"code": "03 01 00"
    +"name": "Maintenance of Concrete"
    +"description": null
  }
]

Solution

  • You dont need relations for this specific query, you could use Laravel Eloquent with join like so:

    $SectionsDivision1 = Specification::join('sections', 'specifications.section_id', '=', 'sections.id')
    ->where([
        'specifications.status' => 'active',
        'specifications.manufacturer_id' => $user->id,
        'sections.division_id' => $sections->id
    ])->select('sections.id', 'sections.division_id', 'sections.code', 'sections.name', 'specifications.description')
    ->groupBy('sections.id');
    

    I hope it helps