How addSelect() Builder method works in Laravel

Question

Say I have these models:

User Model

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'User';

    protected $fillable =
    [
       'username', 'favoriteColor'
    ];          

    public function flights()
    {
        return $this->hasMany('App\Flight');
    }
}

Flight Model

namespace App;

use Illuminate\Database\Eloquent\Model;

class Flight extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'Flight';

    protected $fillable =
    [
       'flightName', 
    ];              
}

I'm trying to do something with eager loading, whenever I do this:

$usersWithFlights = User::with(['flights'])->get();

I get data like this: (Which if fine, is what I expect)

{
    "userID": 1,
    "favoriteColor": green
    "flights": 
    [
       {
           "flightID": 2240,
           "flightName" : "To Beijing fellas"
       },
       {
           "flightID": 4432,
           "flightName" : "To Russia fellas"
       },       
    ]
}

But then I want to add a column using a select raw like this: (Don't think about how silly the 1 as number is, it's just for the sake of the example.

$usersWithFlights = User::with(['flights'])->addSelect(DB::raw('1 as number'))->get();

I get the data like this:

[
    {
        "userID": 1,
        "favoriteColor": green
        "flights": []
    }
]

QUESTION

Was the addSelect() method made for this kind of behaviour? If not are other work arounds in order to achieve this?

NOTE

I know I could add in the select method something like Flights.*, Users.* but I want to know if the addSelect method works that way

score 55 · Answer 1 · edited Sep 02 '19 at 08:38

55

If you need default values from select and add some extra, you can use this construction:

$usersWithFlights = User::with(['flights'])->select()->addSelect(DB::raw('1 as number'))->get();

edited Sep 02 '19 at 08:38

Sagar Gautam

9,049
6
53
84

answered Jul 25 '17 at 14:57

plaha

706
5
3

The Alpha · Answer 2 · 2022-10-20T02:53:04.097

At first take a look at the addSelect method:

public function addSelect($column)
{
    $column = is_array($column) ? $column : func_get_args();
    $this->columns = array_merge((array) $this->columns, $column);
    return $this;
}

This just adds the column(s) by merging with existing selected columns. So, when you use something like this:

$usersWithFlights = User::with(['flights'])->addSelect(DB::raw('1 as number'))->get();

The query becomes select 1 as number from flights and since your relation depends on a column value in the parent query so without any real column value there are no related records retrived. To check the queries you may try something like this:

\DB::enableQueryLog();
$usersWithFlights = User::with(['flights'])->addSelect(DB::raw('1 as number'))->get();
dd(\DB::getQueryLog());

Also, if you use a real column name in add select and if you don't select the related column for example, if you use addSelect('someString') then you'll get no related records either because you've to also select the related column that makes the relation and in this case you may try something like this:

$usersWithFlights = User::with(['flights'])->addSelect(['id', 'someString']))->get();

Test:

This is a test result of DB::getQueryLog() where I've used something similar but with different tables in my DB which is:

Post->with('comments')->addSelect(DB::raw('1 as number'))->get();
dd(DB::getQueryLog());

Result:

0 => array:3 [▼
  "query" => "select 1 as number from "posts""
  "bindings" => []
  "time" => 9.0
  ]
1 => array:3 [▼
  "query" => "select * from "comments" where "comments"."post_id" in (?)"
  "bindings" => array:1 [▼
    0 => null
  ]
  "time" => 1.0
]

Update:

The addSelect method is usefull when you've a query object at hand with some selected fields and later you want to add more fields in the select list so an example could be like this:

$userWithFlights = User::with('flights')->select(['id', 'someColumn']);
// Some code ... 
if(SomeCondition) {
    // So, now the query will be something like this:
    // ->select(['id', 'someColumn', 'anotherField'])
    $userWithFlights->addSelect('anotherField');
}

$result =  $userWithFlights->get();

If you want to specify some fields to be selected then you may use select($fieldsArray) instead. You may also try something like this:

$userWithFlights = User::with('flights')->get(['id', 'someColumn']);

Also, you may try something like this:

$userWithFlights = User::with(['flights' => function($query) {
    $query->select('user_id', 'flight_title');
}])->get(['id', 'someColumn']);

Thanks for such an elaborated answer. Now I get tons of things. But how can I solve my problem? Should I have to call select method typing all fields? — Luis Deras, Apr 01 '16 at 14:09
Actually you can totaly avoid `addSelect`, it's useful when you have a seperate query object with selected fields and later you want to add more fields to be selected. — The Alpha, Apr 01 '16 at 14:30
Yes, you can use `select('fieldName')` to specify fields but add select will merged new fields with pre-selected fields. Also, when using select(), must include the primary key that makes the relationship otherwise related records will not be loaded. Also, you may use `Flight::with('someRelation')->get(['id', 'someColumn'])`. — The Alpha, Apr 01 '16 at 14:37
On Laravel 6, I'm finding that `addSelect()` is replacing the whole select list with the single column in the select terms instead of adding to them. I'm using `$hidden` to hide some very large columns, so maybe that is affecting it. But just pointing out this method is potentially not as robust as expected. — Jason, Apr 19 '20 at 13:40

How addSelect() Builder method works in Laravel

2 Answers2

Test:

Result:

Update:

Linked