Eloquent: filter both pivot table and model table

Question

Eloquent relationship as below:

"stores" belongs to many "storedaytimes" (belong to many)

The pivot table (store_day_time_stores) has a key called "user_id", the storedaytimes table has a field called "date".

I want to filter records in "storedaytimes" based on given "user_id(from pivot table)" and a "start" and "end" date(from storedaytime table).

My current query is

$storedaytime->stores()
    ->wherePivot('user_id','=',$user)
    ->orderBy('date')
    ->where('date','>=',$start)
    ->where('date','<=',$end)->get();

It's producing a very scary looking query that's not working:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'date' in 'where clause' (SQL: select stores.*, order_day_time_store.order_day_time_id as pivot_order_day_time_id, order_day_time_store.store_id as pivot_store_id, order_day_time_store.user_id as pivot_user_id, order_day_time_store.id as pivot_id from stores inner join order_day_time_store on stores.id = order_day_time_store.store_id where order_day_time_store.order_day_time_id is null and order_day_time_store.user_id = 4 and date >= 2017-05-01 12:00:00 and date <= 2017-05-31 12:00:00 order by date asc)

Please help.thank you :3

Answer 1

figured out myself: the following query works:

$storedaytime ->where('date','>=',$start)
->where('date','<=',$end)
->whereHas('stores',function($q) use($user){
                $q->where('user_id',$user);})
->get();

Answer 2

Try the following:

$storedaytime->stores()
    ->wherePivot('user_id','=',$user)
    ->whereBetween('storedaytimes.date', [$start, $end])
    ->orderBy('storedaytimes.date')
    ->get();

It is recommended that the order by clause be at the end of SQL statements, unless you have to order the date beforehand. There is no reason to do it in your case, so place it at the end.