select from subquery with union and paginate in laravel

I have a MySql script that works perfectly fine when run in PHP native.

SELECT * FROM (
(SELECT * FROM news WHERE ctg = 'Promotion' and active = '1' ORDER BY id)
  UNION ALL
(SELECT * FROM news WHERE ctg ='Info' ORDER BY id)    
) AS hasil
ORDER BY id DESC

Currently I'm learning how to do it in laravel eloquent way. Here is the script:

$promo  = DB::select("SELECT * FROM (
(SELECT * FROM news WHERE ctg = 'Promotion' and active = '1' ORDER BY id)
  UNION ALL
(SELECT * FROM news WHERE ctg ='Info' ORDER BY id)
) AS hasil
ORDER BY id DESC ");
return view('news', ['promo' => $promo]);

The script above is working perfectly too unless it can't automatically parsing a pagination.
so how I should done it to get the result right and it could parsing a pagination too?

1 answer

  • answered 2018-11-08 08:24 N69S

    I dont know why you are using Union in this case, the results seems the same as an OR condition, try this with the News model

    $promo = News::where(function($query){
            $query->where('ctg', '=', 'Promotion')
                ->where('active', '=', '1');
        })
        ->orWhere('ctg', '=', 'info')
        ->orderBy('id', 'DESC')
        ->paginate(10);
    return view('news', ['promo' => $promo]);