I'm trying to join two tables using more than one condition. The following query is not working because of the second join condition.
$all_update = DB::table('posts as p')
->join('cprefs as c','p.qatype', '=', 'c.qatype')
->where('c.wwide', '=', 'p.wwide') //second join condition
->where('c.user_id', $u_id)
->where('p.arank', 1)
->get();
The where() functions expects the last parameter to be a parameter where as you are passing in a column name.
To compare two columns you should use the whereColumn method.
With that in mind, you could also write your code like below:
$all_update = DB::table('posts as p')
->join('cprefs as c','p.qatype', '=', 'c.qatype')
->whereColumn('c.wwide', '=', 'p.wwide') //second join condition
->where('c.user_id', $u_id)
->where('p.arank', 1)
->get();
However, this would only work properly if the the join is an INNER JOIN which is true in your case.
The correct method to add multiple join clauses is as below
$all_update = DB::table('posts as p')
->join('cprefs as c', function($q) {
$q->on('p.qatype', '=', 'c.qatype')
->on('c.wwide', '=', 'p.wwide'); //second join condition
})
->where('c.user_id', $u_id)
->where('p.arank', 1)
->get();
Just use this one.
You need the keyword join to use multiple join condition. Irrespective of table.
$all_update = DB::table('posts as p')
->join('cprefs as c','p.qatype', '=', 'c.qatype')
->join('cprefs as c2','p.wwide', '=', 'c2.wwide') //second join condition
->where('c.user_id', $u_id)
->where('p.arank', 1)
->get();
