Laravel scope with inner join

Question

I have 2 tables, projects and jobs. jobs has a column called project_id. In Laravel 5.2 I want to run a search that will return all jobs which belong to a project of a given search term. This SQL works:

SELECT jobs.*, projects.name FROM jobs INNER JOIN projects ON jobs.project_id = projects.id WHERE projects.name LIKE "%$keyword%"

In my Job.php model I have created a scope method, which errors:

public function scopeSearch($query, $keyword)
{
    if ($keyword != '') {
        $query->where(function($query) use ($keyword) {
            $query->where('projects.name', 'LIKE', '%' . $keyword . '%')->join('projects', 'jobs.project_id', '=', 'projects.id');
        });
    }
    return $query;
}

This produces the error:

Column not found: 1054 Unknown column 'projects.name' in 'where clause' (SQL: select * from jobs where (projects.name LIKE %test%))

In my JobsController.php I have:

$searchResults = Job::Search($searchTerm)->get();

Answer 1

The parameter $query in where(function($query) is not the $query that you passed in public function scopeSearch($query, $keyword)

You can either remove it with just the query like below (as @Rob mentioned)

public function scopeSearch($query, $keyword)
{
    if ($keyword != '') {
        $query->where('projects.name', 'LIKE', '%' . $keyword . '%')
           ->join('projects', 'jobs.project_id', '=', 'projects.id');
    }
    return $query;
}

or you need to include the $query in use()

$query->where(function() use ($keyword, $query)