Fetching first or latest first from many to many relationship in eager loading in laravel

Question

I'm building a small application in laravel 5.4, where I'm having two models Contact and Companies I've many to many relationship between them, something like this in my Contact Model:

public function company()
{
    return $this
        ->belongsToMany('App\Company', 'company_contact','contact_id', 'company_id')->withTimestamps();
}

Now at someplace I want to have current company i.e. I want to have latest() first(). Or orderBy, created_by desc and get first() row. For this I have to do something like this:

$contacts = Contact::where('name', 'LIKE', '%'. $request->search_input. '%')
    ->orWhere('email', 'LIKE', '%'. $request->search_input. '%')
    ->orWhereHas('company', function ($q) use($request) {
        $q->where('name', 'LIKE', '%'. $request->search_input. '%');
    })
    ->with('company')
    ->orderBy('created_at', 'desc')
    ->paginate(50);
foreach ($contacts as $contact)
{
    $contact->company = $contact->company()->withPivot('created_at')->orderBy('pivot_created_at', 'desc')->first();
}

For this to remove foreach I tried using a new relation in my Contact model:

public function currentCompany()
{
    return $this
        ->belongsToMany('App\Company', 'company_contact','contact_id', 'company_id')
        ->withTimestamps()
        ->orderBy('created_at', 'desc')
        ->first();
}

But while fetching in controller:

$contacts = Contact::where('name', 'LIKE', '%'. $request->search_input. '%')
    ->orWhere('email', 'LIKE', '%'. $request->search_input. '%')
    ->orWhereHas('currentCompany', function ($q) use($request) {
        $q->where('name', 'LIKE', '%'. $request->search_input. '%');
    })
    ->with('CurrentCompany')
    ->orderBy('created_at', 'desc')
    ->paginate(50);

But it is throwing me error, is there any eloquent way or Collection way to remove this foreach.

Answer 1

use first() inside closure-

$contacts = Contact::where('name', 'LIKE', '%'. $request->search_input. '%')
->orWhere('email', 'LIKE', '%'. $request->search_input. '%')
->with(['company'=>function ($q) use($request) {
    $q->where('name', 'LIKE', '%'. $request->search_input. '%')->first();
}])
->orderBy('created_at', 'desc')
->paginate(50);

Or like this-

$contacts = Contact::where('name', 'LIKE', '%'. $request->search_input. '%')
->orWhere('email', 'LIKE', '%'. $request->search_input. '%')
->orWhereHas('company', function ($q) use($request) {
    $q->where('name', 'LIKE', '%'. $request->search_input. '%');
})
->with(['company'=>function($q){
       $q->first();  
}])
->orderBy('created_at', 'desc')
->paginate(50);

this way you don't need to do any additional foreach loop.

Answer 2

You can't really do what you are trying to do directly. The relationships are query shortcuts. You can ensure you only get the first but that will still be a part of a collection if your relationship is one/many to many.

Consider this:

$contacts = Contact::where('name', 'LIKE', '%'. $request->search_input. '%')
    ->orWhere('email', 'LIKE', '%'. $request->search_input. '%')
    ->orWhereHas('company', function ($q) use($request) {
        $q->where('name', 'LIKE', '%'. $request->search_input. '%');
    })
    ->with([ 'company' => function ($query) {
            return $query->latest(); //Take the latest only 
    })
    ->orderBy('created_at', 'desc')
    ->paginate(50);

However to access the company you need to do something like $contacts[index]->company->first() because it will still produce a collection of related companies even.

The problem is the ORM will perform 2 queries, the first one will retrieve all the Contact models which fulfil the query and the 2nd one will retrieve all related models for all contacts it retrieved. This means if you do things like limiting it to 1 related result you might end up only getting one for all contacts retrieved.