CODEWITHSUNDEEP
phplaravel
Stephen H. Anderson
Stephen H. Anderson

Reputation: 1068

How to add a where clause when using "with" on an Eloquent query in Laravel

I have a query built where I'm using "with" to include related models. However, I'm not sure how to filter those related models in a where clause.

return \App\Project::with("projectLeaders")->join('companies', 'company_id', '=', 'companies.id')
                        ->join('project_status', 'project_status.id', '=', 'projects.status_id')
                        ->select('companies.*', 'project_status.name AS statusName', 'projects.*');

Please note the with("projectLeaders") in the query. So, ProjectLeaders is a relation that brings objects of kind Employee, how can I filter in that query those "Employees" whose attribute "Lastname" is like "Smith" ?

Upvotes: 0

Views: 101

Answers (4)

ManojKiran
ManojKiran

Reputation: 6341

The query you have written is correct. But after building the query you need to fetch the data from database.

METHOD ONE

So adding get() method to your query:

return App\Project::with('projectLeaders')
                    ->leftJoin('companies', 'company_id', '=', 'companies.id')
                    ->leftJoin('project_status', 'project_status.id', '=', 'projects.status_id')
                    ->select('companies.*', 'project_status.name AS statusName', 'projects.*')
                    ->get();

METHOD TWO (with pagination)

return App\Project::with('projectLeaders')
                    ->leftJoin('companies', 'company_id', '=', 'companies.id')
                    ->leftJoin('project_status', 'project_status.id', '=', 'projects.status_id')
                    ->select('companies.*', 'project_status.name AS statusName', 'projects.*')
                    ->paginate(3);

Upvotes: 0

Manzurul Hoque Rumi
Manzurul Hoque Rumi

Reputation: 3094

You can use Closure when accessing relation using with. Check below code for more details:

    return \App\Project::with(["projectLeaders" => function($query){
            $query->where('Lastname', 'Smith') //check lastname
        }])->join('companies', 'company_id', '=', 'companies.id')
                            ->join('project_status', 'project_status.id', '=', 'projects.status_id')
                            ->select('companies.*', 'project_status.name AS statusName', 'projects.*');

Upvotes: 1

FirstIndex
FirstIndex

Reputation: 159

You may use the where method on a query builder instance to add where clauses to the query. The most basic call to where requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. Finally, the third argument is the value to evaluate against the column.

    return \App\Project::with("projectLeaders")->join('companies', 'company_id', '=', 'companies.id')
                            ->join('project_status', 'project_status.id', '=', 'projects.status_id')
                            ->where('lastname','=','Smith')
                            ->select('companies.*', 'project_status.name AS statusName', 'projects.*');

Don't forget to return results with a get();

Upvotes: 0

hizbul25
hizbul25

Reputation: 3849

You can implement where class both tables. Please check following code and comments.

return \App\Proyecto::with(["projectLeaders" => function($query){
  $query->where() //if condition with inner table.
}])->join('empresas', 'id_empresa', '=', 'empresas.id')
                        ->join('tipo_estado_proyecto', 'tipo_estado_proyecto.id', '=', 'proyectos.id_tipo_estado_proyecto')
  ->where() //if condition with main table column.
                        ->select('empresas.*', 'tipo_estado_proyecto.nombre AS nombreEstadoProyecto', 'proyectos.*');

Upvotes: 1

Related Questions