Laravel 4: Adding where clause to a join condition
Solution 1
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
Solution 2
Please Check Below Answer
DB::table('users')
->join('contacts', function($join)
{
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();
Solution 3
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();
Solution 4
$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();
Solution 5
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 ;)
user9507
Updated on September 05, 2020Comments
-
user9507 over 3 years
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();
-
user9507 over 10 yearsI can not use 'on' since it can not be joined using a static date. What I would like to do is filter the result before joining it to other tables , which in effect, will have lesser execution time.
-
user9507 over 10 yearsthat's the problem. I get an error when I add the where clause as stated in my question above.
-
Joel over 10 yearsThis is more of a comment than an answer to the question
-
chickenchilli about 9 yearsAlso watch out for this one: github.com/laravel/framework/issues/3681 the bindings are screwed up when doing this.