Laravel 4: Adding where clause to a join condition

It says in the laravel docs that it is possible to add where clause on a join, but whenever I try in my code using the where clause, I get the error: Call to undefined method Illuminate\Database\Query\JoinClause::where(). Anyone knows how to add where clause in a join clause?

Laravel Website Example:

DB::table('users') ->join('contacts', function($join) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.user_id', '>', 5); }) ->get(); 

Code I'm trying to implement:

DB::table('users') ->join('contacts', function($join) { $current_date = date('Y-m-d'); $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.effective_date', '>=', $current_date); }) ->get(); 

8 Answers

if you want add more condition on a join add more $join->on or $join->orOn.

if you want to add a condition to your first select, add it outside join function.

DB::table('users') ->join('contacts', function($join) { $date = date('Y-m-d'); $join->on('users.id', '=', 'contacts.user_id'); }) ->where('contacts.effective_date', '>=', $date); ->get(); 

Updated
In Laravel 4.0 which I think you use, you can't use where inside your join closure, but since Laravel 4.1 and above you can have where conditions after your join condition. I couldn't find documentation for Laravel 4.1 but this is the #join documentation for L4.2 and above

Please Check Below Answer

DB::table('users') ->join('contacts', function($join) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.user_id', '>', 5); }) ->get(); 

Try This solution

 DB::table('users') ->join('contacts', function($join) { $current_date = date('Y-m-d'); $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.effective_date', '>', $current_date) ->where('contacts.effective_date', '=', $current_date); }) ->get(); 

$current_date = date('Y-m-d'); DB::table('users') ->join('contacts', function($join) use ($current_date) { $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.effective_date', '>=', $current_date); }) ->get(); 

You are calling $current_date but you decarle $date

DB::table('users') ->join('contacts', function($join) { $date = date('Y-m-d'); $join->on('users.id', '=', 'contacts.user_id') ->where('contacts.effective_date', '>=', $date); }) ->get(); 

I don't know if this solve the problem, try it ;)

You are sure that you are working with laravel 4.1? I think you are using laravel 4.0 instead of 4.1. Look in your composer.json file.

Add this just before the get() function call

->where('contacts.effective_date', '=', $current_date); 

$users = DB::table('users') ->join('contacts', 'users.id', '=','contacts.user_id') ->join('orders', 'users.id', '=', 'orders.user_id') ->select('users.*', 'contacts.phone', 'orders.price')->get(); 

please check this link