I have relations between User and Entry Models one to many
but I didn’t used user_id like a foreign key, I used supplier_id
so when i tried to filter data with where has I have the following sql error : SQLSTATE[42S22]: Column not found: 1054 Unknown column 'entries.user_id' in 'where clause' (SQL: select * from userswhererole= supplier andusers.company_id= 1 and exists (select * fromentrieswhereusers.id = **entries.user_id** and entry_date between 2022-01-01 and 2022-01-31))
I think that the query must be like ………..entries.supplier_id……. not user_id because i set the relation with specific foreign key :
Entry.php
public function supplier()
{
return $this->belongsTo(User::class, 'supplier_id');
}
there is any way to set foreign key in whereHas clause ?
the code of filter :
public function scopeEntriesBetween($query, $start, $end)
{
$query->whereHas('entries', function ($query) use ($start, $end) {
$query->whereBetween(
'entry_date',
[$start->format('Y-m-d'), $end->format('Y-m-d')]
);
});
}
>Solution :
Like you did for the relation from Entry to User, do the same for the relation from User to Entry
class User//..
{
//..
public function entries()
{
return $this->hasMany(Entry::class, 'supplier_id');
}
//..
}
Now you can use the relation entries in whereHas or directly with the right foreign key