class Project extends Model
{
public function categories()
{
return $this->hasMany('App\Category');
}
public function properties()
{
return $this->hasManyThrough('App\Property', 'App\Category');
}
}
class Category extends Model
{
public function project()
{
return $this->belongsTo('App\Project');
}
public function properties()
{
return $this->hasMany('App\Property');
}
}
class Property extends Model
{
use SoftDeletes, BelongsToThrough;
public function project()
{
return $this->belongsToThrough('App\Project', 'App\Category');
}
public function category()
{
return $this->belongsTo('App\Category', 'category_id');
}
}
CONTROLLER
public function index(Request $request)
{
$page = $request->input('page') ? $request->input('page') : 1;
$itemsPerPage = $request->input('itemsPerPage') ? $request->input('itemsPerPage') : 25;
$search = $request->input('search');
$project_id = $request->input('project_id');
$properties = Property::select('*')
->when($search, function ($query, $search) {
return $query->where('name', "like", "%$search%");
})
->with('project')
->when($project_id, function ($query, $project_id) {
return $query->whereHas('project', function ($query) use ($project_id) {
return $query->where('id', $project_id);
});
})
->paginate($itemsPerPage);
return $properties;
}
ERROR
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select count(*) as aggregate frompropertieswhere exists (select * fromprojectsinner joincategoriesoncategories.project_id=projects.idwherecategories.id=properties.category_idandid= 1 andcategories.deleted_atis null andcategories.deleted_atis null andprojects.deleted_atis null) andproperties.deleted_atis null)
PROBLEM
When we try to use whereHas relationship WHEN $project_id exist;
->when($project_id, function ($query, $project_id) {
return $query->whereHas('project', function ($query) use ($project_id) {
return $query->where('id', $project_id);
});
})
if remove this whereHas eloquent builder, everything working fine include ->with('project')
I found the problem now by adding table name (projects) to id
changed from
return $query->where('id', $project_id);
to
return $query->where('projects.id', $project_id);
MODEL
CONTROLLER
ERROR
SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select count(*) as aggregate from
propertieswhere exists (select * from
projectsinner join
categorieson
categories.
project_id=
projects.
idwhere
categories.
id=
properties.
category_idand
id= 1 and
categories.
deleted_atis null and
categories.
deleted_atis null and
projects.
deleted_atis null) and
properties.
deleted_atis null)
PROBLEM
When we try to use whereHas relationship WHEN
$project_id
exist;if remove this whereHas eloquent builder, everything working fine include
->with('project')
Thanks