Laravel get eloquent query builder relations by property-CodePudding

I am using the eloquent query builder to build a search query conditionally depending on the parameters provided by the users search query.

This seems to work for attributes that directly belong to the model i am searching for. However, i'm having issues when trying to return additional results that are related to the model but don't directly belong to the model.

What i am trying to do exactly:

I'm trying to allow users to search schools by teacher name where the name/keyword appears in a relation of the school through an intermediary table.

Here are my models and relationships:

School:

class School extends Authenticatable
{
    protected $fillable = [
        'school_name', 'head_teacher', 'address'
    ];

    public function teachers()
    {
        return $this->belongsToMany(Teacher::class, 'teacher_links', 'school_id', 'teacher_id');
    }
}

Teacher:

class Teacher extends Authenticatable
{
    use Notifiable;

    protected $table = 'teacher';

    protected $fillable = [
        'name'
    ];

}

TeacherLink:

class TeacherLink extends Authenticatable
{
    protected $table = 'teacher_links';
    protected $fillable = [
        'teacher_id', 'school_id'
    ];

I can successfuly query the teachers through a school in a controller using $school->teachers so i know the relationship is working fine.

Here is my code for the controller where the users search parameters are conditionally built up:

public function index(Request $request)
{
    $schools = School::query();

    // User Search Term
    $search = $request->get('search');
    $headTeacher = $request->get('head_teacher');

    $questions
        ->when($search, function ($q) use ($search) {
            $q->where('school_name', 'like', '%' . $search . '%')
                ->orWhere('head_teacher', 'like', '%' . $search . '%')
                
                // This is the query i'm having an issue with
                ->whereHas('teachers', function($q) use ($search) {
                    $q->where('teacher_name', 'like', '%' . $search . '%');
                });

        })

}

The two initial where clauses return the correct results when searching by a school name or head_teacher as these two properties directly belong to a school. However, i'd like to return additional schools that include a teachers name through the relationship.

So a school can belong to many teachers and vice versa through the intermediary teacher_link table but i don't know how to accurately filter this this using the eloquent query builder.

Initially i tried using the union function and joins which worked but broke subsequent where clauses as it doesn't seem to allow where clauses to follow after union queries.

It would be great if someone could tell me what i'm doing wrong and how i can fix the query in question:

            // This is the query i'm having an issue with
            ->whereHas('teachers', function($q) use ($search) {
                $q->where('teacher_name', 'like', '%' . $search . '%');
            });

EDIT

I have tried the following which seems to work but it is slow:

$schools
        ->when($search, function ($q) use ($search) {
            $q->join("teacher_links", function ($join) {
                    $join->on("teacher_links.school_id", "=", "schools.id");
                })
                ->join("teachers", function ($join) {
                    $join->on("teachers.id", "=", "teacher_links.teacher_id");
                })
                ->select("schools.*")
                ->where("teachers.teacher_name", 'like', '%' . $search . '%')
                ->orWhere('school_name', 'like', '%' . $search . '%')
                ->orWhere('school_address', 'like', '%' . $search . '%')
                ->orWhere('school_focus', 'like', '%' . $search . '%');
        });

CodePudding user response：

Try using orWhereHas in that query instead.

->when($search, function ($q) use ($search) {
    $q->where('school_name', 'like', '%' . $search . '%')
        ->orWhere('head_teacher', 'like', '%' . $search . '%')
        ->orWhereHas('teachers', function ($sub) use ($search) {
            $sub->where('teacher_name', 'like', '%' . $search . '%');
        });
    });

CodePudding user response：

Try using eloquent relationship

School::where('school_name', 'like', '%' . $search . '%')
  ->orWhere('head_teacher', 'like', '%' . $search . '%')
  ->with(['teachers'=>function($query) use ($search) {
      return $query->orwhere('teacher_name', 'like', '%' . $search . '%');
    }]
  )->get();