How to exclude certains columns while using eloquent

When I'm using eloquent, I can use the "where" method then the method 'get' to fill an object containing what I've selected in my database. I mean:

$users = User::where('gender', 'M')->where('is_active', 1)->get(['pseudo', 'email', 'age', 'created_at'])->toArray();

Here I can choose the columns I want to get like 'pseudo', 'email', etc.. But what I miss in laravel doc is the way to do the contrary. It could be something like that:

$users = User::where('gender', 'M')->where('is_active', 1)->notGet(['pseudo', 'email', 'age', 'created_at'])->toArray();

Thank you for you futur answer and have a nice day.

If you only need to hide attributes from your model's array or JSON representation, you may use one or both approaches:

Add the $hidden property to your model

class User extends Model
{
    /**
     * The attributes that should be hidden for arrays.
     */
     protected $hidden = ['password'];
}

Use the makeHidden function

$users = $users->makeHidden(['address', 'phone_number']);

See other answers for more details... But sometimes you don't want to load huge data (geospatial, html, logs...) into your application, it will be slow and take more memory. OP asked for an SQL query hence my answer, but most of the time it's more convenient to only hide the data from the JSON response.

AFAIK there is no build in option in SQL to exclude columns explicitly, so Laravel can't do it. But you can try this trick

Update

Another trick is to specify all columns in your model (or use an extra query to get all columns using $this->getTableColumns() from this answer, it can also be cached after each migration to avoid two queries) then add a local scope function

// The below code requires you to define all columns in $columns.
// A better approach is to query the schema of the table and cache it after each  
// migration, for more details: https://stackoverflow.com/a/56425794/3192276

protected $columns = ['id','pseudo','email'];

public function scopeExclude($query, $value = []) 
{
    return $query->select(array_diff($this->columns, (array) $value));
}

Then you can do :

$users = User::where('gender', 'M')
    ->where('is_active', 1)
    ->exclude(['pseudo', 'email', 'age', 'created_at'])
    ->toArray();

using hidden array in model is good, but if you don't want to hide your column all the time and use makeVisible to access them in need, then instead, hide your column from serialization where you need with makeHidden function like this :

$res = Model::where('your query')->get();
$res->makeHidden(['column_one','column_two','column_n']);
return response()->json($res);

I don't know about previous Laravel version, but in 5.4 you can put this line in User model

protected $hidden = ['pseudo', 'email', 'age', 'created_at'];

and then User::find(1); will return all fields except pseudo, email, age, and created_at.

But you still can retrieve those hidden fields by using:

$user = User::find(1);
$email = $user['email']; // or $user->email;

How to exclude certains columns while using eloquent

Related

Recent Posts