How to query pivot table where clause?

Hi everyone, I have 3 tables (users, departments, department_user), I can query the pivot table results fine on my returned views but I am trying to write an eloquent query with where clauses on the pivot table.

My pivot table has a column called "term_end_date" and I am trying to get all records prior to a certain date. Any suggestions on how I would do this?

$departments_past = Department::findOrFail($id)->where(pivot->term_end_date, <, '2017-10-10');

I'm sure I'm way off on this, could someone point me in the correct direction on how to query the results based on the pivot table data? Thanks!

andonovn

8 years ago

Best Answer

Level 22

@mchiasson How about this one?

$department = Department::findOrFail($id);
$past = $department->users()
    ->wherePivot('term_end_date', '<', '2017-10-10')
    ->get(); // execute the query

12 likes

mchiasson

8 years ago

Level 1

@andonovn this worked perfectly thanks! I didn't realize ->wherePivot was the proper syntax!

Please or to participate in this conversation.