Eloquent - how to add a join in the hasMany relationship?

Question

I have a typical hasMany method:

public function earmarks() {
    return $this->hasMany('App\Earmark');
}

But how do I add a join and some other conditions to this relationship when I use the ->with('earmarks') to retrieve them? I want to add:

->join('locations', 'locations.id', '=', 'earmarks.location')
->select('earmarks.*', 'locations.location AS em_location')
->orderBy('date', 'asc');

Solution 1

OK, figured out I need a closure in my with() clause, like this:

    $updated_laptops = Laptop::with([
        'earmarks' => function($q) {
            $q
            ->join('locations', 'locations.id', '=', 'earmarks.location')
            ->select('earmarks.*', 'locations.location AS em_location')
            ->orderBy('date', 'asc');
        }
    ])->addJoins()->selectListCols()->find($request->IDs)->keyBy('id');

Solution 2

this worked for me

public function earmarks() {
   return $this->hasMany('App\Earmark', 'labtop_id', 'id')
        ->join('locations', 'locations.id', '=', 'earmarks.location')
        ->select('earmarks.*', 'locations.location AS em_location')
        ->orderBy('date', 'asc')->get();
}

it can be a attribute in Labtop Model and be serializable in Labtop json object like this :

public function GetEarmarksAttribute() {
   return $this->hasMany('App\Earmark', 'labtop_id', 'id')
        ->join('locations', 'locations.id', '=', 'earmarks.location')
        ->select('earmarks.*', 'locations.location AS em_location')
        ->orderBy('date', 'asc')->get();
}

protected $appends = array('earmarks');

Comments

[daninthemix]

I'm not actually working with views, I'm just returning JSON. $updated_laptops = Laptop::with('earmarks')->addJoins()->selectListCols()->find‌​($request->IDs)->key‌​By('id'); -- this code does what I need EXCEPT I need the earmarks to also get the Location. So almost something like Laptop::with('earmarks')->with('location.location') -- although I presume this would get the relation to laptops, not to earmarks.

[Manabroy72]

Good job bro. I like it

[relos100]

O got error on tinker: LogicException with message 'App/User::test must return a relationship instance.' ...