CODEWITHSUNDEEP
mysqllaravel-5eloquent
Wraith
Wraith

Reputation: 501

How can I write SQL query with alias in eloquent laravel?

I spend all day to try how write a SQL query in eloquent. I know I can use raw if I have to, but I'm curious is there 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:

$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 there any way to create the same select in eloquent?

Upvotes: 2

Views: 3364

Answers (2)

Abhay Maurya
Abhay Maurya

Reputation: 12277

The eloquent way will be something like this:

$first = DB::table('advertisements')
            ->select(DB::raw('"1" AS own'), 'id', 'up_date', 'top_list_end_date')
            ->whereRaw('top_list_end_date > NOW()')

$advertisements = DB::table('advertisements')
            ->select(DB::raw('"2" AS own'), 'id', 'up_date', 'top_list_end_date')
            ->whereNull('top_list_end_date')
            ->unionAll($first)
            ->orderBy('own', 'DESC')
            ->orderBy('up_date', 'DESC')
            ->get();

For reference, please see here

Laravel unionAll has the same signature as union.

I hope it helps

Upvotes: 1

Dev Ramesh
Dev Ramesh

Reputation: 363

if you want to use SQL query in laravel. you can use query builder like 

DB::raw();

or

DB::select();

and if you want to use SQL query with alias, just write like this.

$users = DB::table('your_table_name AS t')
       ->select('t.id AS uid')
       ->get();

Hope this answer help you.

Best of luck:)

Upvotes: 0

Related Questions