How to write union query in Laravel?

Question

I'm using laravel 5.0 and I have mysql query:

SELECT surat_masuk.id_surat, 
       surat_masuk.nomor_surat 
FROM   surat_masuk 
WHERE ! EXISTS (SELECT * 
                 FROM   file_replace 
                 WHERE  id_surat_lama = surat_masuk.id_surat) 
      AND surat_masuk.id_jenis_surat = '6' 
      AND surat_masuk.deleted = '0' 
UNION 
SELECT id_surat_lama 
FROM   file_replace 
WHERE  id_surat_baru = '38'

And I write that in my laravel code into:

$nomor_surat1 = DB::table('surat_masuk')->select('id_surat', 'nomor_surat')
    ->where('id_jenis_surat', '=', $id_jenis_surat)
    ->where(function ($query) {
        $query->where('masa_berlaku_to', '>=', date('Y-m-d'))
            ->orwhere('masa_berlaku_to', '=', '0000-00-00');
    })
    ->whereExists(function ($query) {
        $query
            ->from('file_replace')
            ->where('id_surat_lama', '==', 'surat_masuk.id_surat');
    })
    ->where('deleted', '=', '0');

$nomor_surat = DB::table('file_replace')->join('surat_masuk', 'file_replace.id_surat_lama', '=', 'surat_masuk.id_surat')
    ->select('id_surat_lama', 'nomor_surat')
    ->where('id_surat_baru', '=', $id_surat_baru)
    ->union($nomor_surat1)->get();

But I've got nothing showed. Do you know where is the mistakes?

Answer 1

Please see the answer, that might help you..

SELECT surat_masuk.id_surat, 
       surat_masuk.nomor_surat 
FROM   surat_masuk 
WHERE ! EXISTS (SELECT * 
                 FROM   file_replace 
                 WHERE  id_surat_lama = surat_masuk.id_surat) 
      AND surat_masuk.id_jenis_surat = '6' 
      AND surat_masuk.deleted = '0' 
UNION 
SELECT id_surat_lama 
FROM   file_replace 
WHERE  id_surat_baru = '38'

//converted to laravel elequent query.

$first_sql=DB::table('surat_masuk as sm1')
            ->whereNotExists(function($query) {
                $query->from('file_replace as fr1')
                ->where('fr1.id_surat_lama','sm1.id_surat');
            })->where('sm1.id_jenis_surat',6)
            ->where('sm1.deleted',0)
            ->select('sm1.id_surat', 'sm1.nomor_surat' );
    //will return an elequent object, append '->get()' to see the output seperately
$actual_result=DB::table('file_replace as fr2')
                ->where('fr2.id_surat_baru',38)
                ->select('fr2.id_surat_lama')
                ->union($first_sql)
                ->get();
    //will return an array of result set

Answer 2

Well, I finally using raw query..

$results = DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = '$someVariable'") );