Laravel using select in Eloquent scope and query

Question

I'm trying to clean up some code that I made.

This is the current code:

$message = Message::with('comments')
   ->join('users', 'messages.created_by', '=', 'users.id')
   ->join('team_user', 'messages.created_by', '=', 'team_user.user_id')
   ->join('teams', 'team_user.team_id', '=', 'teams.id')
   ->join('roles', 'team_user.role_id', '=', 'roles.id')
   ->select('messages.id',  'messages.message', DB::raw('CONCAT(users.first_name, " ", users.last_name) AS created_by_name'), DB::raw('CONCAT(roles.name, " ", teams.name) AS function'))
   ->findOrFail($id); 

I tried to make it like this:

$message = Message::with('comments')
   ->join('users', 'messages.created_by', '=', 'users.id')
   ->withFunction()
   ->findOrFail($id);

So I made a scope called withFunction that looks like this:

return $query->join('team_user', 'messages.created_by', '=', 'team_user.user_id')
   ->join('teams', 'team_user.team_id', '=', 'teams.id')
   ->join('roles', 'team_user.role_id', '=', 'roles.id')->select(DB::raw('CONCAT(roles.name, " ", teams.name) AS function'));

But because I use this scope where I select specific column, I cant use the select in my query as well. I want it to look like this:

$message = Message::with('comments')
   ->join('users', 'messages.created_by', '=', 'users.id')
   ->withFunction()
   ->select('messages.id', 'messages.message')
   ->findOrFail($id);

So I specify the columns returned from the scope and from the query itself. I know I can't have 2 select's in a query, but is there any way this would be possible?

Would be great if you could just return columns in the scope to use it through the whole application.

Answer 1

Look at addSelect(). When you use select(), your are overriding all the other selected columns and only selecting the one. By using addSelect() you will append the column to the selected columns rather than replace it.

So as a general rule you should call select() before calling any scopes that add columns, and those scopes should use addSelect().

Also... you actually do not need to return the $query in your scope because you are interacting with the query builder object. It kinda works like old school references (&).

Answer 2

The problem seems to come down to how get() works.

    $original = $this->columns;

    if (is_null($original)) {
        $this->columns = $columns;
    }

Get only adds the '*' to select if no other selects are defined.

You either need to explicitly call select('*') on the builder

$message = Message::with('comments')
   ->select('*')
   ->join('users', 'messages.created_by', '=', 'users.id')
   ->withFunction()
   ->select('messages.id', 'messages.message')
   ->findOrFail($id);

or add it in in your scope, this is an example from a 5.3 project.

public function apply(Builder $builder, Model $model)
{
    if(is_null($builder->getQuery()->columns)){
        $builder->addSelect('*');
    }
    $builder->addSelect(DB::raw('...'));
}