How to select specific columns in laravel eloquent
LaravelLaravel 5OrmEloquentLaravel Problem Overview
lets say I have 7 columns in table, and I want to select only two of them, something like this
SELECT `name`,`surname` FROM `table` WHERE `id` = '1';
In laravel eloquent model it may looks like this
Table::where('id', 1)->get();
but I guess this expression will select ALL columns where id equals 1, and I want only two columns(name, surname). how to select only two columns?
Laravel Solutions
Solution 1 - Laravel
You can do it like this:
Table::select('name','surname')->where('id', 1)->get();
Solution 2 - Laravel
Table::where('id', 1)->get(['name','surname']);
Solution 3 - Laravel
By using all() method we can select particular columns from table like as shown below.
ModelName::all('column1', 'column2', 'column3');
Note: Laravel 5.4
Solution 4 - Laravel
You can also use find()
like this:
ModelName::find($id, ['name', 'surname']);
The $id
variable can be an array in case you need to retrieve multiple instances of the model.
Solution 5 - Laravel
You first need to create a Model, that represent that Table and then use the below Eloquent way to fetch the data of only 2 fields.
Model::where('id', 1)
->pluck('name', 'surname')
->all();
Solution 6 - Laravel
Also Model::all(['id'])->toArray()
it will only fetch id as array.
Solution 7 - Laravel
Get value of one column:
Table_Name::find($id)->column_name;
you can use this method with where clause:
Table_Name::where('id',$id)->first()->column_name;
or use this method for bypass PhpStorm "Method where not found in App\Models":
Table_Name::query()->where('id','=',$id)->first()->column_name;
in query builder:
DB::table('table_names')->find($id)->column_name;
with where cluase:
DB::table('table_names')->where('id',$id)->first()->column_name;
or
DB::table('table_names')->where('id',$id)->first('column_name');
last method result is array
Solution 8 - Laravel
You can use get() as well as all()
ModelName::where('a', 1)->get(['column1','column2']);
Solution 9 - Laravel
From laravel 5.3 only using get()
method you can get specific columns of your table:
YouModelName::get(['id', 'name']);
Or from laravel 5.4 you can also use all()
method for getting the fields of your choice:
YourModelName::all('id', 'name');
with both of above method get()
or all()
you can also use where()
but syntax is different for both:
Model::all()
YourModelName::all('id', 'name')->where('id',1);
Model::get()
YourModelName::where('id',1)->get(['id', 'name']);
Solution 10 - Laravel
use App\Table;
// ...
Table::where('id',1)->get('name','surname');
if no where
Table::all('name','surname');
Solution 11 - Laravel
To get the result of specific column from table,we have to specify the column name.
Use following code : -
$result = DB::Table('table_name')->select('column1','column2')->where('id',1)->get();
for example -
$result = DB::Table('Student')->select('subject','class')->where('id',1)->get();
Solution 12 - Laravel
Also you can use pluck.
Model::where('id',1)->pluck('column1', 'column2');
Solution 13 - Laravel
If you want to get a single value from Database
Model::where('id', 1)->value('name');
Solution 14 - Laravel
You can use Table::select ('name', 'surname')->where ('id', 1)->get ()
.
Keep in mind that when selecting for only certain fields, you will have to make another query if you end up accessing those other fields later in the request (that may be obvious, just wanted to include that caveat). Including the id field is usually a good idea so laravel knows how to write back any updates you do to the model instance.
Solution 15 - Laravel
You can get it in various ways. Some of these are shown below-
//Eloquent: Get specific columns (not all the row). Pluck returns an array.
Model::where('id', 1)->pluck('name', 'surname');
// If you only want to get the result value:
Model::where('id', 1)->value('name');
Or,
Model::where('id', 1)
->pluck('name', 'surname')
->all();
Or,
ModelName::all('column1', 'column2', 'column3');
Or,
ModelName::find($id, ['name', 'surname']);
Solution 16 - Laravel
you can also used findOrFail()
method here it's good to used
>if the exception is not caught, a 404 HTTP response is automatically sent back to the user. It is not necessary to write explicit checks to return 404 responses when using these method not give a 500 error..
ModelName::findOrFail($id, ['firstName', 'lastName']);
Solution 17 - Laravel
While most common approach is to use Model::select
,
it can cause rendering out all attributes defined with accessor methods within model classes. So if you define attribute in your model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
/**
* Get the user's first name.
*
* @param string $value
* @return string
*/
public function getFirstNameAttribute($value)
{
return ucfirst($value);
}
}
And then use:
TableName::select('username')->where('id', 1)->get();
It will output collection with both first_name
and username
, rather than only username.
Better use pluck()
, solo or optionally in combination with select
- if you want specific columns.
TableName::select('username')->where('id', 1)->pluck('username');
or
TableName::where('id', 1)->pluck('username');
//that would return collection consisting of only username
values
Also, optionally, use ->toArray()
to convert collection object into array.
Solution 18 - Laravel
If you want to get single row and from the that row single column, one line code to get the value of the specific column is to use find()
method alongside specifying of the column that you want to retrieve it.
Here is sample code:
ModelName::find($id_of_the_record, ['column_name'])->toArray()['column_name'];
Solution 19 - Laravel
ModelName::find($id, ['name', 'surname']);
Solution 20 - Laravel
->get()
much like ->all()
(and ->first()
etc..) can take the fields you want to bring back as parameters;
->get/all(['column1','column2'])
Would bring back the collection but only with column1
and column2
Solution 21 - Laravel
You can get it like
`PostModel::where('post_status', 'publish')->get(['title', 'content', 'slug', 'image_url']`)
Solution 22 - Laravel
You can use the below query:
Table('table')->select('name','surname')->where('id',1)->get();
Solution 23 - Laravel
If you need to get one column calling pluck directly on a model is the most performant way to retrieve a single column from all models in Laravel.
Calling get
or all
before pluck will read all models into memory before plucking the value.
Users::pluck('email');