Error trying to turn raw sql into laravel query builder

Question

I am trying to turn this very long raw sql into laravel query builder and encounter an error.

This is the original raw sql

 sql = "select d.doc_Code,d.seq,p.seq2,d.product_code,d.name_t,d.dwg_file,p.book,concat(p.book,'-',p.seq) as job_book,h.sale_code,h.sale_name,h.ref_code,h.ref_name,h.priority,p.code,p.in_time,wt_date,p.job_status,d.status,DATEDIFF(now(),p.in_time) as gdays,DATEDIFF(h.due_date,now()) as gdue_days,h.due_date,h.start_date as start_datex from jt_p as p inner join jt_d as d on (p.doc_code=d.doc_code and p.book=d.book and p.seq=d.seq and p.in_time is not null and p.wt_date is null and p.job_status not in('Z','C') and p.code<>'M' and d.status <>'C') inner join jt_h as h on(h.doc_code =p.doc_code and h.book=p.book)"

Here is the laravel query builder:

$jt_p = DB::table('jt_p')
        ->join('jt_d', function($join){
            $join->on('jt_p.doc_code', '=', 'jt_d.doc_code');
            $join->on('jt_p.book','=','jt_d.book');
            $join->on('jt_p.seq','=','jt_d.seq');
        })
        ->where('jt_p.in_time','!=','')
        ->where('jt_p.wt_time','=','')
        ->where('jt_p.job_status',DB::raw('not in ("Z","C")'))
        ->where('jt_p.code','!=','M')
        ->where('jt_d.status','!=','C')
        ->join('jt_h', function($join){
            $join->on('jt_h.doc_code', '=', 'jt_p.doc_code');
            $join->on('jt_p.book','=','jt_h.book');
        })
        ->select('jt_d.doc_code','jt_d.seq','jt_p.seq2','jt_d.product_code','jt_d.name_t','jt_d.dwg_file','jt_p.book',
        'jt_h.sale_code','jt_h.sale_name','jt_h.ref_code','jt_h.ref_name','jt_h.priority','jt_p.code','jt_p.in_time','jt_p.wt_time',
        'jt_p.job_status','jt_d.status',DB::raw("DATEDIFF(now(),jt_p.in_time) as gdays"),
        DB::raw("DATEDIFF(jt_h.due_date,now()) as gdue_days"),
        'jt_h.due_date','jt_h.start_date as start_datex')
        ->get();

 return $jt_p;

This is the error message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'not in ("Z","C") and `jt_p`.`code` != ? and `jt_d`.`status` != ?' at line 1 (SQL: select `jt_d`.`doc_code`, `jt_d`.`seq`, `jt_p`.`seq2`, `jt_d`.`product_code`, `jt_d`.`name_t`, `jt_d`.`dwg_file`, `jt_p`.`book`, `jt_h`.`sale_code`, `jt_h`.`sale_name`, `jt_h`.`ref_code`, `jt_h`.`ref_name`, `jt_h`.`priority`, `jt_p`.`code`, `jt_p`.`in_time`, `jt_p`.`wt_time`, `jt_p`.`job_status`, `jt_d`.`status`, DATEDIFF(now(),jt_p.in_time) as gdays, DATEDIFF(jt_h.due_date,now()) as gdue_days, `jt_h`.`due_date`, `jt_h`.`start_date` as `start_datex` from `jt_p` inner join `jt_d` on `jt_p`.`doc_code` = `jt_d`.`doc_code` and `jt_p`.`book` = `jt_d`.`book` and `jt_p`.`seq` = `jt_d`.`seq` inner join `jt_h` on `jt_h`.`doc_code` = `jt_p`.`doc_code` and `jt_p`.`book` = `jt_h`.`book` where `jt_p`.`in_time` != and `jt_p`.`wt_time` = and `jt_p`.`job_status` = not in ("Z","C") and `jt_p`.`code` != M and `jt_d`.`status` != C)

Why don't you use the in-built whereNotIn of Laravel?

linuxartisan
– linuxartisan

2019-08-07 08:02:23 +00:00
Commented Aug 7, 2019 at 8:02 — linuxartisan
– linuxartisan, Commented Aug 7, 2019 at 8:02

N69S · Accepted Answer · 2019-08-07 08:19:13Z

1

Change this line of code

->where('jt_p.job_status',DB::raw('not in ("Z","C")'))

to

->whereNotIn('jt_p.job_status',["Z","C"])

You need also to use IsNull IsNotNull

$jt_p = DB::table('jt_p')
        ->join('jt_d', function($join){
            $join->on('jt_p.doc_code', '=', 'jt_d.doc_code');
            $join->on('jt_p.book','=','jt_d.book');
            $join->on('jt_p.seq','=','jt_d.seq');
        })
        ->whereNotNull('jt_p.in_time')
        ->whereNull('jt_p.wt_time')
        ->whereNotIn('jt_p.job_status',["Z","C"])
        ->where('jt_p.code','!=','M')
        ->where('jt_d.status','!=','C')
        ->join('jt_h', function($join){
            $join->on('jt_h.doc_code', '=', 'jt_p.doc_code');
            $join->on('jt_p.book','=','jt_h.book');
        })
        ->select('jt_d.doc_code','jt_d.seq','jt_p.seq2','jt_d.product_code','jt_d.name_t','jt_d.dwg_file','jt_p.book',
        'jt_h.sale_code','jt_h.sale_name','jt_h.ref_code','jt_h.ref_name','jt_h.priority','jt_p.code','jt_p.in_time','jt_p.wt_time',
        'jt_p.job_status','jt_d.status',DB::raw("DATEDIFF(now(),jt_p.in_time) as gdays"),
        DB::raw("DATEDIFF(jt_h.due_date,now()) as gdue_days"),
        'jt_h.due_date','jt_h.start_date as start_datex')
        ->get();

 return $jt_p;

edited Aug 7, 2019 at 8:19

answered Aug 7, 2019 at 8:02

N69S

17.3k4 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Qi Yang Over a year ago

ya it works, but somehow it returns an empty array.Do you know what is the problem with the query compare with the raw sql?

N69S Over a year ago

is not null and != '' are two different things

N69S Over a year ago

@QiYang updated my answer, added the whereNull and whereNotNull in their places

N69S Over a year ago

@QiYang if the aswer helped resolve your issue, mark it as the answer, for posterity

Collectives™ on Stack Overflow

Error trying to turn raw sql into laravel query builder

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related