How to use relational Model scope in Laravel Eloquent with “Left Join”..?

Question

I have following two models in laravel using Eloquent:

Parent Appraisal model as below:

class Appraisal extends \Eloquent
{

   protected $table = 'scope_appraisal';

   protected $primaryKey = 'appraisal_id';

   public function appraisalInfo()
   {
     return $this->hasOne('AppraisalInfo', 'appraisal_id', 'appraisal_id');
   }
}

Relational AppraisalInfo model as below:

class AppraisalInfo extends \Eloquent  
{

    protected $table = 'scope_appraisal_info';

    protected $primaryKey = 'appraisal_info_id';

    public function appraisal()
    {
       return $this->belongsTo('V2_0\Appraisal', 'appraisal_id', 'appraisal_id');

    }

   public function scopeBorrowerName($query, $name)
   {
       return $query->where('appraisal_info_borrower', 'LIKE', '%' . trim($name) . '%');
   }
}

I have make query with "LeftJoin" in controller as below:

   // QUERY USING LEFT JOIN
    $queryObj = Appraisal::leftJoin('scope_appraisal_info', function ($join)
    {
        $join->on('scope_appraisal.appraisal_id', '=', 'scope_appraisal_info.appraisal_id');

    });

    $queryObj = $queryObj->borrowerName('xyzname');

    $result = $queryObj->get();

But, It was returning me Error as below:

Call to undefined method Illuminate\Database\Query\Builder::borrowerName()

So, How can i access scope from relational model with above query..?

Please, Let me know if anyone knows....Thanks!

Your scope method is in the AppraisalInfo model and not the Appraisal model, on which you are querying. Have you tried moving the scope method to you Appraisal Model? I haven't tested, but you might add the leftJoin there instead. — Laravelian
Yes, it is working in Appraisal model, But i need such a way so that i can keep scope for field scope in their relevant model and used in query, because "borrower_name" field is within table "scope_appraisal_info" , so is there any way or required any correction on query..? — Keyur Vaghani
The issue is that you're trying to do it with a join. The join doesn't know about the relationship, that is attached after the query. What I would do is move the scope method to the Appraisal model, and try to add the join there as well -- that way you know that the join happens any time the borrowerName scope method is called on the Appraisal model. If you ened one on the AppraisalInfo model as well, as much as it feels wrong and dirty, you might have to duplicate some code. — Laravelian
Is there any other way instead of Join, so that i can keep my field scope with their relevant model and achieve same result..? — Keyur Vaghani
I don't know of a way to do what you're saying with Laravel's baked in relationships and scope methods. I think, if I were in your situation, I'd just move the query out to a repository instead of in the model. Doing that would afford you some more flexibility. You could build your repository in such a way that you could do something like this: $appraisalRepository->withBorrowerName('name')->findAll(); and then build up your query with joins as needed in the repo. — Laravelian

tosin tosin · Accepted Answer · 2017-04-05T08:25:39

Not a smart way, but you can achieve like below:

$queryObj = Appraisal::leftJoin('scope_appraisal_info', function ($join)
{
    $join->on('scope_appraisal.appraisal_id', '=', 'scope_appraisal_info.appraisal_id');
    $join = (new AppraisalInfo())->scopeBorrowerName($join, 'xyzname');
});

However, unless you have to use scope I recommend using "where" in join.

$name = 'xyzname';
$queryObj = Appraisal::leftJoin('scope_appraisal_info', function ($join) use ($name)
{
    $join->on('scope_appraisal.appraisal_id', '=', 'scope_appraisal_info.appraisal_id')
         ->where('appraisal_info_borrower', 'LIKE', '%' . trim($name) . '%');
});

on Laravel 5.2.

How to use relational Model scope in Laravel Eloquent with “Left Join”..?

1 Answers