CODEWITHSUNDEEP
phpsqllaraveleloquent
YannPl
YannPl

Reputation: 1322

Laravel - Add custom column in select with Eloquent query buider

this is a simplified use case, only to illustrate what I want to achieve:

Considering this query in pure SQL:

SELECT url, 1 AS active
FROM  `modules` 
WHERE 1

How can I add the constant active column using query builder ?

Here is my Query Builder without the extra column:

DB::table('modules')
->get(['url']);

Upvotes: 23

Views: 58630

Answers (4)

Wisnu Wijokangko
Wisnu Wijokangko

Reputation: 2278

Alternative solution if you are using eloquent

$people = Modules::select([
            DB::raw("(SELECT 'CUSTOM_FIELD') as custom_field")
        ])
        ->get();

Upvotes: 1

Artem Molotov
Artem Molotov

Reputation: 317

We can add subquery or "custom column" in select with first argument of \Illuminate\Database\Query\Builder::selectSub method as raw SQL or Closure, or \Illuminate\Database\Query\Builder. Better solution is closure or Builder. In your case it will be:

$modules = DB::table('modules')->select('url')
    ->selectSub(function ($query) {
        $query->selectRaw('1');
    }, 'active')
    ->get();

Tested on Laravel 5.5. In closure $query is a object of \Illuminate\Database\Query\Builder for subquery. Prepared SQL will be:

select `url`, (select 1) as `active` from `modules`

Extended example... If we use App\Module eloquent for modules and we need get url of modules and count of their submodules with id > 5, we can write next:

$modules = App\Module::select('url')
    ->selectSub(function ($query) {

        /** @var $query \Illuminate\Database\Query\Builder */
        $query->from('submodules')
              ->selectRaw('COUNT(*)')
              ->where('id', '>', 5)
              ->whereRaw('`modules`.`id` = `submodules`.`module_id`');

    }, 'countOfSubModules')
    ->get();

Prepared SQL will be:

select `url`, 
   (select COUNT(*) from `submodules`
       where `id` > ? and `modules`.`id` = `submodules`.`module_id`)
   as `countOfSubModules` 
from `modules`

Or you can write your example with raw sql:

$sql = 'SELECT 1';
$modules = DB::table('modules')->select('url')->selectSub($sql, 'active')->get();

Then prepared SQL will be:

select `id`, (SELECT 1) as `active` from `modules`

For get all columns necessarily to use select('*'): 

App\Module::select('*')->selectSub($sql, 'text')->get();

Not:

App\Module::selectSub($sql, 'text')->get();

Upvotes: 22

turntwo
turntwo

Reputation: 2520

Simplest would be to use DB::raw 

     DB::table('modules')->get(['url', DB::raw('1 as active')]);

Upvotes: 38

Pupil
Pupil

Reputation: 23948

Laravel Eloquent has very flexible query builder.

You can specify a column to return as:

$users = DB::table('modules')->select('1 as active')->get(['url']);

Upvotes: -1

Related Questions