Get Specific Columns Using “With()” Function in Laravel Eloquent
Well I found the solution. It can be done one by passing a closure
function in with()
as second index of array like
Post::query()
->with(['user' => function ($query) {
$query->select('id', 'username');
}])
->get()
It will only select id
and username
from other table. I hope this will help others.
Remember that the primary key (id in this case) needs to be the first param in the$query->select()
to actually retrieve the necessary results.*
How we get specific columns from multiple relations using With() in Laravel
to make Laravel able to load the relation, you should select the foreign key that responsible for that relation
Questions::with(array(
'questionCategory' => function($query) {$query->select('cat_id','cat_value');},
'ans_options' => function($query1) {$query1->select('ac_id','ac_choiceTxt'
,'ac_quest_id');}))->get();
just add 'ac_quest_id
' to your select.
Laravel eloquent creating a WHERE condition if and only if the table column has a specific value
I think what you want to do is this :
->where(function($q) {
$q->where('column_a', '=', 'paid')
->where('status', '!=', 'failed')
->where('status', '!=', 'cancelled');
})
->orWhere(function($q) {
$q->where('column_a', '!=', 'notpaid')
->where('status', '!=', 'authorized');
})
Laravel with([]) select only some columns of the relation
Like this:
$order = Order::with([
'company:id,name,size',
'complaints:id,name,body',
'person',
'items',
'items.product:id,name,price',
'items.product.stockManagement',
'status:id',
'items.product.media',
'items.product.mainProduct',
'items.product.mainProduct.media'
])->findOrFail($id);
The documentation is very brief about the loading of specific columns (you even have to scroll down a bit to the heading that says "Eager Loading Specific Columns").
You may not always need every column from the relationships you are retrieving. For this reason, Eloquent allows you to specify which columns of the relationship you would like to retrieve:
$books = App\Book::with('author:id,name')->get();
Note:
When using this feature, you should always include the id column and any relevant foreign key columns in the list of columns you wish to retrieve.
Laravel Eager Loading - Load only specific columns
Make use of the select()
method:
public function car() {
return $this->hasOne('Car', 'id')->select(['owner_id', 'emailid', 'name']);
}
Note: Remember to add the columns assigned to the foreign key matching both tables. For instance, in my example, I assumed a Owner
has a Car
, meaning that the columns assigned to the foreign key would be something like owners.id = cars.owner_id
, so I had to add owner_id
to the list of selected columns;
How to select specific columns in laravel eloquent
You can do it like this:
Table::select('name','surname')->where('id', 1)->get();
How to exclude certains columns while using eloquent
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 modelclass 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();
CONCAT columns with Laravel 5 eloquent
You need to wrap your query in DB::raw
:
$comp = Component::select(DB::raw("CONCAT('name','id') AS ID"))->get()
Also, note because you are doing your query like this, your model might behave differently, because this select removes all other fields from the select statement.
So you can't read the other fields from your model without a new query. So ONLY use this for READING data and not MODIFYING data.
Also, to make it in a nice list, I suggest you modify your query to:
$comp = Component::select(DB::raw("CONCAT('name','id') AS display_name"),'id')->get()->pluck('display_name','id');
// dump output to see how it looks.
dd($comp);// array key should be the arrray index, the value the concatted value.
Select specific column in laravel eloquent's with method
You also have to select the foreign key column (required for matching the results):
$posts = $request->user()->posts()->with('comments:id,post_id');
Related Topics
Search With Comma-Separated Value MySQL
Get First Key in a (Possibly) Associative Array
How to Push Both Value and Key into PHP Array
PHP Regex to Get Youtube Video Id
How to Make an Asynchronous Get Request in PHP
Using a .PHP File to Generate a MySQL Dump
How to Replace Microsoft-Encoded Quotes in PHP
Getting Dom Elements by Classname
Using $_Post to Get Select Option Value from Html
What Does $$ (Dollar Dollar or Double Dollar) Mean in PHP
Post Request in Laravel - Error - 419 Sorry, Your Session/ 419 Your Page Has Expired
How to Resolve Ambiguous Column Names When Retrieving Results
PHP Warning: PHP Startup: How to Load Dynamic Library