我正在尝试将原始sql查询转换为laravel查询。

这是原始查询：

select
tsk.id,tsk.request_id,tsk.sys_index,tsk.category_group,tsk.category,tsk.is_assigned,tsk.hash_id 
from
   user_tasks as usr 
   inner join
      unassigned_tasks as tsk 
      on usr.task_id = tsk.id 
where
   usr.assigned_to = 12
AND 
    tsk.product_id NOT IN ( SELECT product_id FROM product_progresses WHERE request_id = tsk.request_id )
AND 
    BINARY hash_id NOT IN ( SELECT hash_id FROM product_match_unmatches WHERE request_id = tsk.request_id AND auto_unmatched_by IS NOT NULL )

laravel查询为：

public function getTasks($assigned_to) {
    /** fetch products assigned to a specific user token,*   ignore already matched skus,and links that are auto-unmatched
    **/
    $tasks = DB::table('user_tasks as usr')
        ->join('unassigned_tasks as tsk','usr.task_id','=','tsk.id')

        ->select('tsk.id','tsk.request_id','tsk.sys_index','tsk.category_group','tsk.category','tsk.is_assigned','tsk.hash_id')

        ->where('usr.assigned_to',$assigned_to);
    $tasks->whereNotIn('tsk.product_id',function($qs) {
        $qs->from('product_progresses')
            ->select(['product_id'])
            ->where('request_id','tsk.request_id')
            ->get();

    });
    $tasks->whereNotIn(DB::raw('BINARY `hash_id`'),function($qs) {
        $qs->from('product_match_unmatches')
            ->select('hash_id')
            ->where('request_id','tsk.request_id')
            ->whereNotNull('auto_unmatched_by')
            ->get();
    });

    return $tasks->toSql();

以下查询应从外部查询中获取tsk.request_id值，但我认为列值未传递给它。

这是toSql()的输出：

SELECT `tsk`.`id`,`tsk`.`request_id`,`tsk`.`sys_index`,`tsk`.`category_group`,`tsk`.`category`,`tsk`.`is_assigned`,`tsk`.`hash_id` 
FROM   `user_tasks` AS `usr` 
       INNER JOIN `unassigned_tasks` AS `tsk` 
               ON `usr`.`task_id` = `tsk`.`id` 
WHERE  `usr`.`assigned_to` = ? 

AND `tsk`.`product_id` NOT IN (SELECT `product_id` 
                                  FROM   `product_progresses` 
                                  WHERE  `request_id` = ?) 
   AND BINARY `hash_id` NOT IN (SELECT `hash_id` 
                                FROM   `product_match_unmatches` 
                                WHERE  `request_id` = ? 
                                       AND `auto_unmatched_by` IS NOT NULL) 

请注意?子句中的where。

结果集不同于raw和laravel query。

我什至尝试查看绑定值：

//dd($tasks->getBindings());
$sql = str_replace_array('?',$tasks->getBindings(),$tasks->toSql());
dd($sql);

在运行此原始查询时，它将输出正确的结果集。

更新：

在检查bindings时，发现的内容如下：

array:3 [▼
  0 => 12
  1 => "tsk.request_id"
  2 => "tsk.request_id"
]

此处外部查询列用引号引起来，因此被视为字符串。

因此，也许where子句试图将request_id与字符串而不是外部列进行比较。

如果是这样，那么如何使它们被视为columns而不是string？