php - 以下查询有什么问题，它显示页面在运行后无法正常工作

Question

我正在尝试将原始 sql 转换为 laravel 查询生成器，但是当我运行它时，它显示“此页面不工作”。

其他页面运行良好。

原始 sql:

$sql = "select w.from_time as qa_date,w.staff_code,w.p_code,s.name_t,w.book,w.qty,d.product_code,d.name_t as product_name,
        d.dwg_file,d.part_no,d.cost,h.cc,h.sale_name,h.ref_code,w.description from process_trans as w 
        left join jt_d as d on(w.doc_code=d.doc_code and w.book=concat(d.book,'-',d.seq)) 
        left join jt_h as h on(d.doc_code=h.doc_code and h.book=d.book) 
         left join astaff as s on(w.staff_code=s.staff_code)
          where w.p_code2='Finished'";

Laravel 查询构建器:

$process_trans = DB::table('process_trans')
            ->leftJoin('jt_d', function ($join) {
                $join->on('process_trans.doc_code', '=', 'jt_d.doc_code');
            })
            ->leftJoin('jt_h', function ($join) {
                $join->on('jt_d.doc_code', '=', 'jt_h.doc_code');
                $join->on('jt_d.book', '=', 'jt_h.book');
            })
            ->leftJoin('astaff', 'process_trans.staff_code', '=', 'astaff.staff_code')
            ->select(
                'process_trans.*','jt_h.*','jt_d.*','astaff.*',  
                'astaff.name_t as staff_name',
                'process_trans.from_time as qa_date',
                'jt_d.name_t as product_name'
            )
            ->where('process_trans.p_code2','=','Finished')
            ->get();

            return $process_trans;

我怀疑是不是查询导致了问题。

Answer 1

查询不是问题。

假设这是您的数据布局:

create table astaff (staff_code int, name_t text);
create table jt_h (doc_code int, book int, cc text, sale_name text, ref_code text);
create table jt_d (doc_code int, book int, name_t text, product_code int, dwg_file text, part_no int, cost int, seq text);
create table process_trans (doc_code int, staff_code int, from_time timestamp, p_code2 text, p_code text, description text, book int, qty int);

Laravel 从查询生成器代码生成此查询(由 Laravel Debugbar 向我展示):

select `process_trans`.*, `jt_h`.*, `jt_d`.*, `astaff`.*, `astaff`.`name_t` as `staff_name`, `process_trans`.`from_time` as `qa_date`, `jt_d`.`name_t` as `product_name` 
from `process_trans` 
left join `jt_d` on `process_trans`.`doc_code` = `jt_d`.`doc_code` 
left join `jt_h` on `jt_d`.`doc_code` = `jt_h`.`doc_code` and `jt_d`.`book` = `jt_h`.`book` 
left join `astaff` on `process_trans`.`staff_code` = `astaff`.`staff_code` 
where `process_trans`.`p_code2` = 'Finished'

虽然不漂亮，但它可以工作(或者至少在执行时不会显示错误)。