Question 273746 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – How to fetch last record from the table

enter image description here

How can I fetch those latest records withRaw Sql syntax

I know how to get it through eloquent with hasOne relationship but i need to do it from joins

`post

  • id;
  • integer(‘no’);
  • string(‘division’);

histories

  • id;
  • foreignId(‘post_id’);
  • longText(‘content’);
  • date(‘inspection_date’);`
Tags:

2

Answers


  1. 0

    For example we have users and Answer tables . user can have answered many Answers . And we need to pull the latest Answer associated to each User . $query =

    Users::select('users.id', 'users.user_name','answers.created_at as last_activity_date')
->leftJoin('answers', function($query) 
{
   $query->on('users.id','=','answers.user_id')
   ->whereRaw('answers.id IN (select MAX(a2.id) from answers as a2 join users as u2 on u2.id = a2.user_id group by u2.id)');
})->where('users.role_type_id', Users::STUDENT_ROLE_TYPE)->get();

    Ref here : Laravel leftJoin only last record of right table.

    Login or Signup to reply.
  2. 0

    If you want to use raw SQL queries in Laravel instead of models, you can do it this way.

    $results = DB::select(DB::raw('
    SELECT p.id as post_id, p.no, p.division, h.id as history_id, h.content, h.inspection_date
    FROM posts p
    JOIN histories h ON p.id = h.post_id
    JOIN (
        SELECT post_id, MAX(inspection_date) as max_inspection_date
        FROM histories
        GROUP BY post_id
    ) latest_histories ON h.post_id = latest_histories.post_id AND h.inspection_date = latest_histories.max_inspection_date
'));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search