Oliver Kucharzewski Oliver Kucharzewski - 27 days ago 8
MySQL Question

Eloquent relation WHERE statement not working

I'm trying to retrieve a list of all 'Holidays' combined with 'HolidayInfo' which are the respective table names.

This is the query i'm attempting to use:

$holidays = Holidays::with('info')->where('country', $country)->get();


When using this query; I get the following error:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'country' in 'where clause' (SQL: select * from `holidays` where `country` = australia)"


It seems as if the join has not been performed. Even when giving writing 'holiday_info.country' it still doesn't work as expected.

When doing a vardump of
$holidays = Holidays::with('info')->get();
I get this:

enter image description here

Here it is evident that a relationship is being established but is not included in the query.

Here are the tables in use:

enter image description here
enter image description here

My Eloquent Models:

Holiday

protected $table = 'holidays';
protected $primaryKey = 'holiday_id';
public $timestamps = false;
public $incrementing = false;
public function info(){
return $this->hasOne('App\HolidayInfo', 'holiday_id','holiday_id');
}


Holiday Info:

protected $table = 'holiday_info';
public $timestamps = false;
protected $primaryKey = null;
public $incrementing = false;
public function holidays(){
return $this->belongsTo('App\Holiday', 'holiday_id', 'holiday_id');
}


I'm not sure why the connection between both tables isn't working in the top query. It seems as if country isnt recognised as a field in holiday_info hence showing the join isnt being completed correctly. Any ideas what the issue may be?

Answer Source

I think it code might help you

$holidays = Holidays::whereHas('info', function($info) use($country){
    $info->where('country', $country);              
})->get();

Eloquent Relationship