CODEWITHSUNDEEP
sqllaravelcontrollersubqueryrelationship
Hope
Hope

Reputation: 644

Query some parent data and its child data in Laravel 8

I have a personnel table which is connected to other tables. One of its child table is BMI. A personnel has many BMI record. At the moment I have this query.

$personnel = Personnel::join('personnel_training', function($join) use ($request){
                $join->on('personnel.id', '=', 'personnel_training.personnel_id')
                ->where('personnel_training.training_id', $request->training); 
            })
            ->join('state', 'personnel.state_id', '=', 'state.id')
            ->join('unit', 'state.unit_id', '=', 'unit.id')
            ->select('state.name as statename','unit.name as unitname','personnel.*','personnel_training.training_id')
                    ->selectRaw("TIMESTAMPDIFF(YEAR, DATE(birth_date), current_date) AS age")
                    ->selectRaw("TIMESTAMPDIFF(YEAR, DATE(date_lastprom), current_date) AS sample")
                    ->orderBy('last_name', 'asc')
                    ->paginate(50)
                    ->appends(request()
                    ->query());

My code is working fine at the moment. The thing I want to accomplish is to connect the bmi and personnel table and get latest record of personnel BMI. What are the things I need to add? I already tried other way but I have no luck. I am thinking that maybe subquery will give me the data I want but I don't know how to do it. I already connect the personnel and bmi model to have a one to many relationship.

Upvotes: 0

Views: 112

Answers (2)

matiaslauriti
matiaslauriti

Reputation: 8102

So you want simply to access the relationship of them all... don't take my comment as harsh but please, read the documentation, it is more complex to create the code you are showing us than doing what the documentation says...

You have to do this:

Personnel::with(['personnelTraining', 'state', 'unit'])->orderBy('last_name')->paginate(50)->appends(request()->query());

Then in your blade you have to process the missing TIMESTAMPDIFF, it would be $personnel->birth_date->diffInYears($personnel->current_date), same for date_lastprom.

I have no idea what request()->query() will do...

Upvotes: 1

Marvz73
Marvz73

Reputation: 93

In your personnel model have this relationship function

return $this->hasMany('Personnel', 'personnel_id');
     ->order_by('date', 'desc')
     ->limit(5);

Upvotes: 1

Related Questions