I want to use query for search where doctor location or clinic address from clinic table that joined with the doctors id's foreign key but using with i can able to get data but for clinic address the error is coming like this :-

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'clinicaddress' in 'where clause' (SQL: select * from doctors where city LIKE %test% or state LIKE %test% or country LIKE %test% or address1 LIKE %test% or address2 LIKE %test% or clinicaddress LIKE %test% and status = 1)

query is shown like below:-

$doctor = Doctor::with('clinics')->where('city','LIKE','%'.$request->locationsearch.'%')
            ->orwhere('state','LIKE','%'.$request->locationsearch.'%')
            ->orwhere('country','LIKE','%'.$request->locationsearch.'%')
            ->orwhere('address1','LIKE','%'.$request->locationsearch.'%')
            ->orwhere('address2','LIKE','%'.$request->locationsearch.'%')
            ->orwhere('clinicaddress','LIKE','%'.$request->locationsearch.'%')
            ->where('status','=','1')->get();

Doctor Model code is like this:-

protected $fillable = [
        'username','email','firstname','lastname','phoneno','password','gender','dob','profileimage',
        'biography','address1','address2','city','state','country','pincode','service','specialization',
        'general_cons_price','videocallprice','voicecallprice','status',
    ];

public function clinics(){
        return $this->hasMany('App\Models\Clinic','dr_id');
    }

Clinic Model code is like this:-

public function doctor(){
        return $this->belongsTo('App\Models\Doctor','dr_id');
    }

so please tell me how can i use the orwhere query in the same variable with relationship.

CodePudding user response：

You would need to use a whereHas.

You could do something like ->whereHas('clinic', function ($query) use ($addressVariable) { $query->where('address', $addrerssVariable); })

You can also do ->orWhereHas in your case if you need the or. You should be mindful of how you're using orWhere with where. You may need to group them differently for it to do what you really want. For example ->where('thingOne', true)->where(function ($query) { $query->where('thingTwo', false)->orWhere('thingThree', false); }).

CodePudding user response：

If i understand you true way, i think you should use belongs to many relationship. Like this; The doctor may work in more than one clinic. And more than one doctor can work in a clinic.

Pivot Table;

 Schema::create('clinic_doctor', function (Blueprint $table) {
        $table->id();
        $table->foreignId('clinic_id')->constrained('clinics');
        $table->foreignId('doctor_id')->constrained('doctors');
        $table->timestamps();
    });

Doctor Model;

public function clinics(){
   return $this->belongsToMany(Clinic::class, 'clinic_doctor');
{

Clinic Model;

public function doctors(){
   return $this->belongsToMany(Doctor::class, 'clinic_doctor');
}

Your query;

$doctors = Doctor::whereHas('clinics',function($clinics) use ($request) {
    $clinics->where('city','like','%'.$request->city.'%');
})->get();