Ho can i make sql quere with alias in eloquent laravel?

i spend all day to try how make query sql in eloquent. I know i can use raw if i have to but i curious is any way to do that?

my raw sql is:

SELECT *
FROM (
    SELECT 1 as own, id, up_date , top_list_end_date FROM advertisements WHERE top_list_end_date > now()
    UNION ALL
    SELECT 2 as own, id, up_date, top_list_end_date FROM advertisements  WHERE top_list_end_date IS NULL
) a
ORDER BY own, up_date desc

In laravel i am trying is:

$groupA = $adv->where('top_list_end_date', '>', Carbon::now());
$groupA->select('*')
        ->selectSub(function ($query) {
            $query->selectRaw('1');
        }, 'own');

$groupB = $restAdv->whereNull('top_list_end_date');
$groupB->select('*')
        ->selectSub(function ($query) {
            $query->selectRaw('2');
        }, 'own');

$result = $groupA->unionAll($groupB);

$result->orderBy('own', 'desc')->orderBy('up_date', 'desc')->get();

is any way to create the same select in eloquent ?

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2SobMTV
via IFTTT