Laravel get last row value form many table in one to Many relationship

Question

I am building an application which has a model with one to many relationship. In the model, the student table has one to many relationship with student address details. I want to retrieve the last row from address details table. I am stuck on how to retrieve that data. I could not work out from similar answer on this website.

My current solution is this

$students = Student::with('visaDetails', 'addresses', 'instituteDetails', 'subAgents',
            'staffDetails', 'commissionDetails', 'comments')->paginate(16);

        foreach ($students as $student){
            foreach ($student->addresses as $address){
                dd($address->id);
            }
        }

My student model is as follow

class Student extends Model
{
    //One to Many relation to student address

    public function addresses(){
        return $this->hasMany('App\Model\Address');
    }
}

But this solutions is not efficient as number of record grows exponentially. Also, I need to find last row for other many table as well.

If anybody could help me then it would be very nice.

score 3 · Accepted Answer · edited Feb 19 '20 at 06:40

3

To get latest record from address table for each student you can go with following self joined query

SELECT s.*, a.*
from students s
join address a on s.id = a.student_id
left join address a1 on a.student_id = a1.student_id
and a.created_at < a1.created_at
where a1.student_id is null

I assume by latest means you need address with most recent created_at attribute.

Using query builder you might rewrite it as

DB::table('students as s')
  ->select('s.*', 'a.*')
  ->join('address as a', 's.id', '=', 'a.student_id')
  ->leftJoin('address as a1', function ($join) {
        $join->on('a.student_id', '=', 'a1.student_id')
             ->whereRaw(DB::raw('a.created_at < a1.created_at'));
   })
  ->whereNull('a1.student_id')
  ->get();

Laravel Eloquent select all rows with max created_at

Laravel - Get the last entry of each UID type

Laravel Eloquent group by most recent record

edited Feb 19 '20 at 06:40

Claudio Nastasi Junior

141
9

answered Jun 16 '18 at 00:49

M Khalid Junaid

63,861
10
90
118

Thank you so much. This works perfectly. What should i do when I need to populate latest row from all the many tables. like address, institute, staff etc. – Jivan Bhandari Jun 16 '18 at 01:41
@Bikram for multiple tables the would become more complex and might be slow when data increases i guess you can fetch them by individual queries for each table, For multiple table in same query the logic is same as for address , each table will be self joined – M Khalid Junaid Jun 16 '18 at 02:57
1

I am using multiple join for now. The performance has not degraded till now. Let's see what happens when data increases. – Jivan Bhandari Jun 17 '18 at 03:21

score 0 · Answer 2 · answered Nov 04 '18 at 00:38

I created a package that allows you to limit the number of eager loading results per parent:
https://github.com/staudenmeir/eloquent-eager-limit

Use the HasEagerLimit trait in both the parent and the related model.

class Student extends Model {
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
}

class Address extends Model {
    use \Staudenmeir\EloquentEagerLimit\HasEagerLimit;
}

Then you can apply ->limit(1) to your relationship:

$students = Student::with([
    'addresses' => function($query) {
        $query->limit(1);
    },
    'visaDetails', 'instituteDetails', 'subAgents',
    'staffDetails', 'commissionDetails', 'comments'
])->paginate(16);

Laravel get last row value form many table in one to Many relationship

2 Answers2

Linked