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

user_14965306 0
Viewed 2056

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);
}
}
laravel
1 Answers

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();
user_8533600
Related