How to Alias a Table in Laravel Eloquent Queries (Or Using Query Builder)

How to alias a table in Laravel Eloquent queries (or using Query Builder)?

Laravel supports aliases on tables and columns with AS. Try

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

Let's see it in action with an awesome tinker tool

$ php artisan tinker
[1] > Schema::create('really_long_table_name', function($table) {$table->increments('id');});
// NULL
[2] > DB::table('really_long_table_name')->insert(['id' => null]);
// true
[3] > DB::table('really_long_table_name AS t')->select('t.id AS uid')->get();
// array(
//   0 => object(stdClass)(
//     'uid' => '1'
//   )
// )

How do I select an alias table for my laravel eloquent DB::table?

Treat that as 2 different query builders and merge their binding like below :

$xx = 'xx';
$innerQuery = DB::select('A','B','C')->table('TABLE')->where('D', '=',$xx);

$mainQuery = DB::table(DB::raw('(' . $innerQuery->toSql() . ') as custom_table'))
    ->mergeBindings($innerQuery)
    ->groupBy('custom_table.C')
    ->get();

This will also help you retail the $innerQuery builder instance for your later use as you have mentioned in the question.

How to alias a left joined table in laravel eloquent

You are going to need to use a Raw Expression:

DB::table('articles')
        ->leftJoin('articles', 'articles.id', '=', 'articles.user_id')
        ->leftJoin('news_groups', 'news_groups.id', '=', 'articles.news_group_id')
        ->leftJoin('magazine_groups', 'magazine_groups.id', '=', 'articles.magazine_groups_id')
        ->select(DB::raw('articles.id, type, title, articles.published_at, users.first_name, users.last_name, COALESCE(news_groups.name, magazine_groups.name) as group_name'))
        ->groupBy('articles.id');

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

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

Laravel - Two or more alias to same table in query builder

#Raw Expressions

Sometimes you may need to use a raw expression in a query. These expressions will be injected into the query as strings, so be careful not to create any SQL injection points! To create a raw expression, you may use the DB::raw method:

https://laravel.com/docs/4.2/queries#raw-expressions

$query = DB::table(DB::raw('item as a, item as b'))
              ->where('a.type', '=', 'big')
              ->where('a.published','=',1)
              ->where('b.parent_id','=', DB::raw('a.id'))
              ->select('b.url','b.id')
              ->get();

Is there any equivalent of SQL IN (...) query in Laravel for multiple if condition?

You could use PHPs in array function https://www.php.net/manual/en/function.in-array.php

$arr = [1, 2, 3, 4];

if (in_array($type, $arr) {

}

If you have multiple groups of values to compare to, use switch()

switch($type) {
    case 1:
    case 2:
    case 3:
    case 4:
        echo 'group 1';
        break;
    case 6:
    case 7:
    case 8:
        echo 'group 2';
        break;
    default:
        echo 'no group found';
        break;
}

Laravel DB query builder - using multiple 'from' with aliases

The table and from methods work similarly. They can accept 2 parameters, the table (which can be a Closure or a Builder instance) and an alias. The only difference as far as I can tell is that table must be the first method called after DB::

For example, the following query

SELECT * FROM (
    SELECT * FROM table1 WHERE condition = "something"
) AS alias1

Can be written like this using a Closure.

DB::table(function ($subquery) {
    return $subquery->from('table1')->where('condition', 'something');
}, 'alias')
->get();
// Or using PHP7.4 short Closures
DB::table(fn($subquery) => $subquery->from('table1')->where('condition', 'something'), 'alias')->get();

Or like this using a Builder instance.

$alias = DB::table('table1')->where('condition', 'something');
DB::table($alias, 'alias')->get();

So your query could be translated as the following using the builder

$cd_2 = DB::table('cdrnew, cd_1')
    ->select(
        'cd_1.userfield',
        'cd_1.Start_Date',
        'cd_1.uniqueid',
    )
    ->selectRaw('MAX(cd_1.Row_Id) AS Row_Id_Max')
    ->whereBetween('cd_1.Start_Date', ['2020-12-01', '2020-12-10'])
    ->where('cd_1.userfield', 'Inbound')
    ->groupBy(
        'cd_1.userfield',
        'cd_1.Start_Date',
        'cd_1.uniqueid'
    );

$cd_4 = DB::table($cd_2, 'cd_2')
    ->select(
        'cd_2.Userfield',
        'cd_2.Start_Date',
        'cd_2.uniqueid',
        'cd_2.Row_Id_Max', 
        'cd_3.disposition AS disposition_Last'
    )
    ->join('cdrnew AS cd_3', 'cd_3.Row_Id', '=', 'cd_2.Row_Id_Max')
    ->whereBetween('cd_3.Start_Date', ['2020-12-01', '2020-12-10'])
    ->where('cd3.userfield', 'Inbound');

$query = DB::table($cd_4, 'cd_4')
    ->select('cd_4.userfield')
    ->selectRaw('COUNT(DISTINCT(CASE WHEN cd_4.disposition_Last = "ANSWERED" THEN cd_4.uniqueid ELSE NULL END)) AS Answered_Count')
    ->selectRaw('COUNT(DISTINCT(CASE WHEN cd_4.disposition_Last <> "ANSWERED" THEN cd_4.uniqueid ELSE NULL END)) AS Not_Answered_Count');

// Uncomment next line to see the query produced. It should be the same you posted in your question.
// dd($query->toSql());
$results = $query->get();

Or as this using Closures

$query = DB::table(function ($cd_4) {
    return $cd_4->from(function ($cd_2) {
        return $cd_2->from('cdrnew, cd_1')
        ->select(
            'cd_1.userfield',
            'cd_1.Start_Date',
            'cd_1.uniqueid',
        )
        ->selectRaw('MAX(cd_1.Row_Id) AS Row_Id_Max')
        ->whereBetween('cd_1.Start_Date', ['2020-12-01', '2020-12-10'])
        ->where('cd_1.userfield', 'Inbound')
        ->groupBy(
            'cd_1.userfield',
            'cd_1.Start_Date',
            'cd_1.uniqueid'
        );
    }, 'cd_2')
    ->select(
        'cd_2.Userfield',
        'cd_2.Start_Date',
        'cd_2.uniqueid',
        'cd_2.Row_Id_Max', 
        'cd_3.disposition AS disposition_Last'
    )
    ->join('cdrnew AS cd_3', 'cd_3.Row_Id', 'cd_2.Row_Id_Max')
    ->whereBetween('cd_3.Start_Date', ['2020-12-01', '2020-12-10'])
    ->where('cd3.userfield', 'Inbound');
}, 'cd_4')
->select('cd_4.userfield')
->selectRaw('COUNT(DISTINCT(CASE WHEN cd_4.disposition_Last = "ANSWERED" THEN cd_4.uniqueid ELSE NULL END)) AS Answered_Count')
->selectRaw('COUNT(DISTINCT(CASE WHEN cd_4.disposition_Last <> "ANSWERED" THEN cd_4.uniqueid ELSE NULL END)) AS Not_Answered_Count');

// Uncomment next line to see the query produced. It should be the same you posted in your question.
// dd($query->toSql());
$results = $query->get();

As far as I can tell, the queries generated are the same.

---

Related Topics