自联接条件在PHP中失败但在mysql中有效

Question

I have this strange situation and not sure of what is wrong. I have a simple self join to find matches based on some conditions. I have this query running fine in mysql but when I call it through PHP, it doesn't return any values.

select * from Requests p inner join Requests c on c.ID<>p.ID
where usr_ID<>4
and p.c_ID = c.c_ID

This works fine but not the below one.

DB::table('Requests as parent')
  ->join('Requests as child', 'parent.ID', '<>', 'child.ID')
  ->where('parent.usr_ID', '<>', 4)
  **->where('parent.c_ID', '=', 'child.c_ID')**
  ->get();

In the above query, if I remove the second where condition(c_ID), it returns correct values. For all rows, this has a value of 1. If I replace child.c_ID or parent.c_ID by 1, it works again. I have tried with other columns as well and found the same issue.

Any pointers?

Answer 1

What the query builder makes out of your second where condition is:

WHERE parent.c_ID = 'child.c_ID'

So instead of a "normal" where() use whereRaw(), which takes your input and injects it right into the final SQL query

->whereRaw('parent.c_ID = child.c_ID')

Alternatively you could also use DB::raw() on the third argument

->where('parent.c_ID', '=', DB::raw('child.c_ID'))

Both are essentially the same so use whichever you like more.