mardi 21 juin 2016

How to write union query in Laravel?

Hi, 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?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire