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);
except
it in my query.
created_at
, updated_at
, deleted_at
, this answer is the most perfect for me.
Model::first()->relationship->makeHidden(['field1', 'field2']);
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;
description
column which is necessary only when you load a single model, but not necessary when you load all the models. Skipping it saves a lot of memory.
I have looked into the answer by @Razor
But there is Very Conveinent way by skipping $columns property
/**
* Scope a query to only exclude specific Columns.
*
* @author Manojkiran.A <manojkiran10031998@gmail.com>
* @param \Illuminate\Database\Eloquent\Builder $query
* @return \Illuminate\Database\Eloquent\Builder
*/
public function scopeExclude($query, ...$columns)
{
if ($columns !== []) {
if (count($columns) !== count($columns, COUNT_RECURSIVE)) {
$columns = iterator_to_array(new \RecursiveIteratorIterator(new \RecursiveArrayIterator($columns)));
}
return $query->select(array_diff($this->getTableColumns(), $columns));
}
return $query;
}
/**
* Shows All the columns of the Corresponding Table of Model
*
* @author Manojkiran.A <manojkiran10031998@gmail.com>
* If You need to get all the Columns of the Model Table.
* Useful while including the columns in search
* @return array
**/
public function getTableColumns()
{
return \Illuminate\Support\Facades\Cache::rememberForever('MigrMod:' . filemtime(database_path('migrations')), function () {
return $this->getConnection()->getSchemaBuilder()->getColumnListing($this->getTable());
});
}
getTableColumns
function will get all the columns of the table so you dont need to define the $column
property
NOTE: COLUMN NAMES OF TABLE WILL BE CACHED UNTIL CONTENTS OF MIGRATIONS DIRECTORY IS ADDED OR DELETED.
MODIFYING THE CONTENTS OF FILES INSIDE THE MIGRATIONS DIRECTORY WILL NOT RE-CACHE THE COLUMNS
To clear cache manually you can run php artisan cache:clear
you can use hidden
array like this:
class Promotion extends Model
{
protected $table = 'promotion';
protected $hidden = array('id');
}
We get the object eloquent from the model full with all fields, transform it to array and we put it inside of a collection. Than we get all fields except all fields specified in array $fields.
$fields = ['a', 'b', 'c', 'N'];
$object = Model::find($id);
return collect($object->toArray())->except($fields);
More clearly, let's give an example:
// Array of fields you want to remove
$fields_to_remove = ['age', 'birthday', 'address'];
// Get the result of database
$user = User::find($id);
// Transform user object to array
$user = $user->toArray();
// Create a collection with the user inside
$collection = collect($user);
// Get all fields of our collection except these fields we don't want
$result = $collection->except($fields_to_remove);
// Return
return $result;
This example above makes exactly the same thing of the first one, but it's more explained.
I have a solution that worked for me, which is slightly different than those already stated.
Solution:
$all_columns = Schema::getColumnListing('TABLE_NAME');
$exclude_columns = ['COLUMN_TO_EXCLUDE_1', 'COLUMN_TO_EXCLUDE_2'];
$get_columns = array_diff($all_columns, $exclude_columns);
return User::select($get_columns)->get();
Reasoning:
For me:
Razor's answer didn't work as I got the following error:
BadMethodCallException with message 'Call to undefined method App/CaseStudy::exclude()'
Then, the remaining answers were attemping to hide the columns within the model. Unfortunately, that would hide them for each method in my class and this isn't something that I wanted.
So, in the end, I modified Razor's solution so that it would work without having to hide any of the columns for each method.
I hope this helps someone! 😊
you can use makeHidden
array like this: (After get() or all())
$users = User::where('gender', 'M')->where('is_active', 1)->get()->makeHidden(['pseudo', 'email', 'age', 'created_at'])->toArray();
You can leverage Illuminate\Support\Facades\Schema::getColumnListing('table_name');
use Illuminate\Support\Facades\Schema;
$users_table_columns = Schema::getColumnListing('users');
$exclude_columns = [
'password',
'token',
'address',
];
$select = array_diff($users_table_columns, (array) $exclude_columns);
$site = User::select($select)
->where('gender', 'M')
->where('is_active', 1)
->first();
You can use unset unset($category->created_at,$category->updated_at);
$fcategory = array();
$kCategory = KCategory::where("enabled", true)->get();
foreach ($kCategory as $category) {
$subkCategory = PostCategory::select("id", "name", "desc")
->where("id_kcategory", $category->id)
->where("enabled", true)
->get();
unset($category->created_at, $category->updated_at);
$fcategory[] = $category;
}
"laravel/lumen-framework": "5.7.8"
Success story sharing
scopeExclude()
is called like that. Read about laravel scopes on laravel.com/docs/5.3/eloquent#local-scopesSchema::getColumnListing('table')
and clear it on each migration, that way you avoid an extra SQL query.