How to select specific columns in laravel eloquent

Question

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?

Thanks for asking the question. It's crazy as I've always done this properly in 'classic' SQL queries, but having to use Eloquent, I found it so confusing I was happy just to get it working, and was oblivious to how much memory my queries were using because I was selecting everything. — Jonny Perl, Sep 30 '19 at 21:50

score 343 · Accepted Answer · answered Jul 03 '16 at 18:08

343

You can do it like this:

Table::select('name','surname')->where('id', 1)->get();

answered Jul 03 '16 at 18:08

Marcin Nabiałek

104,635
41
242
277

2

let's suppose i want to select every field expect one how can i do that – Prince Arora Mar 05 '18 at 11:15
3

First of all, when you have question, ask one. And for your case - just mention all columns instead of the one you don't want to include. It's that easy. – Marcin Nabiałek Mar 05 '18 at 15:16
2

isn't there any other method for doing that without mentioning all columns – Prince Arora Mar 10 '18 at 04:24
2

This works for a basic case with a single table. However, if you are using a relation over a pivot table, then this will ALSO select the pivot columns, automatically. – Benubird Apr 03 '18 at 15:15
Does it format SQL with this fields, or it filters output data? – OPV Nov 19 '18 at 20:02
1

@OPV It just runs `SELECT name, surname FROM Table where id = 1` SQL query – Marcin Nabiałek Nov 19 '18 at 22:49
I see you applied the get method at the end which runs the query on the query builder instance, I'm wondering does this return a collection of say the User objects (the active record way) or just an array containing the results from the underlying SQL query? – Picrasma Aug 12 '20 at 19:34
How to get field twice with alias? – Jan 10 '21 at 15:53

score 120 · Answer 2 · edited Jun 06 '17 at 09:18

120

Table::where('id', 1)->get(['name','surname']);

edited Jun 06 '17 at 09:18

Djave

7,694
5
59
110

answered Mar 15 '17 at 17:33

Utkarsh Pandey

1,502
1
9
11

2

I want the same thing but using different method. I want to select method by creating instance of model and then select column. i.e $model = new MyModel(); $model->select(['col1', 'col2']); But this thing is not working – Dhirender Mar 20 '18 at 13:01
I believe this is the way it is supposed to be used. I don't know if select() is faster than get() though. – m33ts4k0z Apr 06 '21 at 18:25
can also be `model::where('col', val)->get(['col1', 'col2']);` – Liam O'Toole Nov 22 '21 at 20:41

score 83 · Answer 3 · answered Oct 04 '17 at 05:41

83

By using all() method we can select particular columns from table like as shown below.

ModelName::all('column1', 'column2', 'column3');

Note: Laravel 5.4

answered Oct 04 '17 at 05:41

Sivakumar Tadisetti

919
6
11

1

I want the same thing but using different method. I want to select method by creating instance of model and then select column. i.e $model = new MyModel(); $model->select(['col1', 'col2']); But this thing is not working. – Dhirender Mar 20 '18 at 13:01

score 79 · Answer 4 · answered Dec 24 '18 at 09:08

79

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.

answered Dec 24 '18 at 09:08

Sammie

1,331
1
19
15

9

This should be the selected answer. – Armin Sep 06 '20 at 06:05
Not sure why this is not the accepted answer. – Vagabond Mar 10 '22 at 08:41

score 36 · Answer 5 · answered Oct 02 '17 at 17:07

36

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();

answered Oct 02 '17 at 17:07

Mahesh Yadav

1,828
16
21

This is the best answer since it works also with pivot tables in many to many relationships. – Luciano Fantuzzi Jun 12 '19 at 13:35
This should be the answer – mercury Feb 10 '22 at 20:12

score 29 · Answer 6 · answered Aug 24 '17 at 10:12

29

Also Model::all(['id'])->toArray() it will only fetch id as array.

answered Aug 24 '17 at 10:12

Bhagchandani

498
6
18

Mostafa Asadi · Answer 7 · 2020-10-09T17:44:35.173

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

score 15 · Answer 8 · answered Jun 03 '18 at 20:54

15

You can use get() as well as all()

ModelName::where('a', 1)->get(['column1','column2']);

answered Jun 03 '18 at 20:54

aleXela

1,205
1
19
31

This answer should have much more votes. – Stefan Teunissen Oct 26 '21 at 22:42

Haritsinh Gohil · Answer 9 · 2019-07-10T13:04:43.957

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']);

score 8 · Answer 10 · edited Jun 20 '20 at 09:12

8

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();

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 13 '18 at 11:14

Jitender Sharma

121
1
4

Please add some explanation to your answer such that others can learn from it – Nico Haase Dec 02 '19 at 10:40

score 8 · Answer 11 · edited Sep 11 '20 at 14:55

8

use App\Table;
// ...
Table::where('id',1)->get('name','surname');

if no where

Table::all('name','surname');

edited Sep 11 '20 at 14:55

Franklin'j Gil'z

321
1
8

answered Jun 18 '20 at 06:22

عبد الناصر الخمايسة

141
2
2

1

Good answer but square brackets are missing. Should be like: ->get(['name','surname']); – Johan Apr 19 '21 at 14:03

score 5 · Answer 12 · answered May 08 '18 at 08:59

5

Also you can use pluck.

Model::where('id',1)->pluck('column1', 'column2');

answered May 08 '18 at 08:59

bereket gebredingle

8,858
2
30
44

6

Note that `pluck()` is less efficient than the other methods discussed because it doesn't modify the `SELECT ...` query, but rather, it operates on the already-returned result set. – Ben Johnson Aug 07 '18 at 12:52

Ali Raza · Answer 13 · 2021-05-09T19:04:05.400

5

If you want to get a single value from Database

    Model::where('id', 1)->value('name');

edited May 09 '21 at 19:04

answered Dec 30 '20 at 10:13

Ali Raza

105
1
10

@NachoMezzadra Actually I did :):) – Froxz Mar 26 '21 at 19:10

score 4 · Answer 14 · answered Jul 03 '16 at 18:09

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.

score 3 · Answer 15 · answered Sep 04 '21 at 13:44

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']);

score 2 · Answer 16 · answered Feb 09 '19 at 12:28

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']);

Nikola Kirincic · Answer 17 · 2019-04-05T11:39:30.320

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.

score 2 · Answer 18 · answered Jun 04 '19 at 20:12

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'];

score 2 · Answer 19 · edited Jan 14 '22 at 02:57

2

ModelName::find($id, ['name', 'surname']);

edited Jan 14 '22 at 02:57

DragonFire

2,836
1
23
40

answered Oct 30 '20 at 09:38

Abeldlp

41
4

score 1 · Answer 20 · answered Nov 14 '18 at 15:42

1

->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

answered Nov 14 '18 at 15:42

Robert Pounder

1,378
1
12
28

score 1 · Answer 21 · answered Oct 15 '21 at 05:06

1

You can get it like

`PostModel::where('post_status', 'publish')->get(['title', 'content', 'slug', 'image_url']`)

link

answered Oct 15 '21 at 05:06

Ali Akbar Afridi

282
4
5

score 0 · Answer 22 · edited Oct 26 '18 at 07:50

0

You can use the below query:

Table('table')->select('name','surname')->where('id',1)->get();

edited Oct 26 '18 at 07:50

Salman Zafar

3,450
4
17
40

answered Jan 04 '18 at 20:21

Luis

1
1

1

Please add some explanation to your answer such that others can learn from it – Nico Haase Dec 02 '19 at 10:40

score 0 · Answer 23 · answered Mar 18 '22 at 16:41

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');

How to select specific columns in laravel eloquent

23 Answers23

To get the result of specific column from table,we have to specify the column name.

Linked