CODEWITHSUNDEEP
laravel
mychuks
mychuks

Reputation: 53

Syntax error or access violation: 1066 Not unique table/alias: '

I am writing an app in laravel which displays this error SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'medicals' (SQL: select * from patients left join medicals on medicals.patients_id = patients.id left join medicals on medicals.trx_id = patient_referral.trx_id)

  return Patients::where(function ($q) use ($startdate, $enddate, $id) {
        $q->where("patient_referral.trx_id",  $id);
    })
        ->leftJoin("medicals", "medicals.patients_id", "=", "patients.id")
        ->leftJoin("medicals", "medicals.trx_id", "=", "patient_referral.trx_id")
        ->get();

Medical Model

class Medicals extends Model
{
protected $guarded = [];

public function patients()
{
    return $this->belongsTo(Patients::class, "id", "patients_id");
}
public function tests()
{
    return $this->belongsTo(Tests::class);
}
}

Patient Model

class Patients extends Model { protected $guarded = [];

public function medicals()
{
    return $this->hasMany(Medicals::class);
}

public function patient_referral()
{
    return $this->hasMany(PatientReferral::class);
}

}

Patient Referral Model

class PatientReferral extends Model { protected $guarded = [];

protected $table = "patient_referral";


public function patients()
{
    return $this->belongsTo(Patients::class);
}
}

Upvotes: 2

Views: 4902

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You are trying to join medicals table twice so in this case you need to provide unique alias to each instance and in join clause specify that alias for columns so that database will know the exact column from table to use in filter like

Patients::where(function ($q) use ($startdate, $enddate, $id) {
            $q->where("patient_referral.trx_id",  $id);
        })
        ->leftJoin("medicals as a", "a.patients_id", "=", "patients.id")
        ->leftJoin("medicals as b", "b.trx_id", "=", "patient_referral.trx_id")
        ->get();

Upvotes: 7

Related Questions