1

I have two tables beneficiaries and accommodation_histories and I want only to join last record of accommodation_histories into beneficiaries.

This is my method so far:

public function getQuery(): Builder
{
    return DB::table('beneficiaries')
        ->select([
            'beneficiaries.id',
            'media.id as media',
            'beneficiaries.name as beneficiary_name',
            'beneficiaries.surname',
            'beneficiaries.gender',
            'beneficiaries.date_of_birth',
            'countries.name as country_of_birth',
            'accommodation_histories.center as center',
            'accommodation_histories.date_of_receipt as date_of_receipt',
            'accommodation_histories.release_date as release_date',
        ])
        ->leftJoin('media', 'media.model_id', '=', 'beneficiaries.id')
        ->join('countries', 'countries.id', '=', 'beneficiaries.country_of_birth')
        ->join('accommodation_histories', 'accommodation_histories.beneficiary', '=', 'beneficiaries.id');
}
knubbe
  • 1,132
  • 2
  • 12
  • 21
  • Check this post https://stackoverflow.com/questions/2043259/sql-server-how-to-join-to-first-row – Giacomo M May 17 '20 at 10:11
  • 1
    There is an example in the documentation under [Subquery Joins](https://laravel.com/docs/7.x/queries#joins) which may be what you are looking for. – porloscerros Ψ May 17 '20 at 17:01

1 Answers1

0

You can do something like below at the parent model (beneficiaries) :

public function accommodation_histories()
{
    return $this->hasOne('App\Accommodation_histories')->orderBy('id', 'desc');
}
Ali Lashini
  • 427
  • 1
  • 5
  • 17