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();
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)