I have a "daisy chain" of tables. I managed to get three deep table query and i'm not sure how to go about further.
I got through vehicles->complement->deliveries, but i'm not sure how to proceed to get employees. As you can see, they're all connected via foreign keys and docs don't explain more then three deep.
Here is how how i got so far, which works for three deep:
public function deliveries(){
return $this->belongsToMany("App\Delivery", "App\Complement", "vehicle_id", "delivery_id");
}
It's classic, belongsToMany relationship that targets three deep. Not sure how to proceed to forth one...
Edit1:
Vehicles model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Vehicle extends Model
{
protected $fillable = [
'registration', 'sec_id', "workOrganization_id"
];
/*public function deliveries(){
return $this->belongsTo("App\Delivery",'vehicle_id');
}*/
public function user(){
return $this->belongsTo("App\User", 'sec_id', "id");
}
public function type(){
return $this->belongsTo("App\VehiclePivot", 'vehicle_type_id', "id");
}
public function workOrganization(){
return $this->belongsTo("App\WorkOrganization", 'workOrganization_id', "id");
}
public function deliveries(){
return $this->belongsToMany("App\Delivery", "App\Complement", "vehicle_id", "delivery_id");
}
}
Deliveries model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Delivery extends Model
{
protected $table = 'deliveries';
protected $fillable = [
'load_place',
'unload_place',
"comment",
"time_in",
"time_out",
"operator_id",
"sec_id"
];
public function operator(){
return $this->belongsTo("App\Employee",'operator_id');
}
public function enteredBy(){
return $this->belongsTo("App\User",'sec_id');
}
public function complement(){
return $this->hasMany("App\Complement", 'delivery_id');
}
}
Complement model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Complement extends Model
{
protected $table = 'complement';
protected $fillable = [
'delivery_id',
'vehicle_id',
];
public function vehicles(){
return $this->belongsTo("App\Vehicle", 'vehicle_id');
}
}
Employees model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Employee extends Model
{
protected $fillable = [
'lastName',
'firstName',
"work_org_id",
"sec_id",
"avatar"
];
public function work_organization(){
return $this->belongsTo("App\WorkOrganization",'work_org_id');
}
public function enteredBy(){
return $this->belongsTo("App\User",'sec_id');
}
public function deliveries(){
return $this->hasMany("App\Delivery",'operator_id');
}
}
CodePudding user response：
Dot syntax is solution here. Simply "connect" models as you were connecting tables with foreign keys. But in models, you'll use relationship methods. Example:
Vehicle has "Complements" method which finds all complements, then in Complement model you got "Deliveries" method which find all deliveries connected with individual delivery, then you have yours "Employees" method which finds employees which handle individual delivery.
Model's methods either use "belongsTo" or "belongsToMany" relationships which depends how you defined your tables.
And here are models with their methods:
Vehicle model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Vehicle extends Model
{
protected $fillable = [
'registration', 'sec_id', "workOrganization_id"
];
public function complements(){
return $this->hasMany("App\Complement", 'vehicle_id', "id");
}
}
Complement model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Complement extends Model
{
protected $table = 'complement';
protected $fillable = [
'delivery_id',
'vehicle_id',
];
public function deliveries(){
return $this->belongsTo("App\Delivery", 'delivery_id', "id");
}
}
Delivery model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Delivery extends Model
{
protected $table = 'deliveries';
protected $fillable = [
'load_place',
'unload_place',
"comment",
"time_in",
"time_out",
"operator_id",
"sec_id"
];
public function employees(){
return $this->belongsTo("App\Employee", 'operator_id', "id");
}
}
And here is how i finally called them in controller:
if($request->vehicle_id && Vehicle::find($request->vehicle_id)){
$vehicles = Vehicle::with("complements.deliveries.employees", ...$request->Vehicle)
->when($dates->start_date, function ($query, $date) {
$query->where('updated_at', '>=', $date);
})
->when($dates->end_date, function ($query, $date) {
$query->where('updated_at', '<=', $date);
})->find($request->vehicle_id);
}
else{
$vehicles = Vehicle::with("complements.deliveries.employees", ...$request->Vehicle)
->when($dates->start_date, function ($query, $date) {
$query->where('updated_at', '>=', $date);
})
->when($dates->end_date, function ($query, $date) {
$query->where('updated_at', '<=', $date);
})->get();
}
There are also some dates, but they aren't important, just to sift through needed data.
CodePudding user response：
I can see 3 "routes" to get from the Vehicle model to the Employee model
- Vehicle hasMany Complement belongsTo Delivery belongsTo Employee.
- Vehicle hasMany Complement belongsToMany Employee
- Vehicle belongsToMany Delivery belongsTo Employee
In my opinion, route number 3 is the easiest.
class Vehicle extends Model
{
public function deliveries()
{
// belongsToMany's 2nd parameter is the pivot table, not the pivot model. Rest of the parameters should be okay to omit in this case
return $this->belongsToMany(Delivery::class, 'complement')
->as('complement')
->using(Complement::class)
->withPivot(['sec_id'])
->withTimestamps();
}
}
class Delivery extends Model
{
public function employee()
{
return $this->belongsTo(Employee::class, 'operator_id');
}
}
$vehicles = Vehicle::with('deliveries.employee')->...->get();
foreach ($vehicles as $vehicle) {
$vehicle->... // all vehicles columns
foreach ($vehicle->deliveries as $delivery) {
$delivery->... // all deliveries columns
$delivery->complement->... // all complement columns (pivot table)
$delivery->employee->... // all employees columns
}
}