0

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();
Abdallah Sakre
  • 915
  • 1
  • 9
  • 26
  • Possible duplicate of [A JOIN With Additional Conditions Using Query Builder or Eloquent](https://stackoverflow.com/questions/16848987/a-join-with-additional-conditions-using-query-builder-or-eloquent) – sh1hab Oct 13 '19 at 09:37
  • And what is the error message that you're receiving? – J. Murray Oct 13 '19 at 11:27

2 Answers2

2

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.

MD.Tabish Mahfuz
  • 666
  • 5
  • 6
0

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();
Saurav Sen Sarma
  • 19
  • 5