Laravel 5.1 DB:select toArray()

Question

I have a large SQL statement that I am executing like so:

$result = DB::select($sql);

For example

$result = DB::select('select * from users');

I'd like the result to be an array - but at the moment it returns a structure like so, an array with Objects...

Array
(
    0 => stdClass::__set_state(array(
        'id' => 1,
        'first_name' => 'Pavel',
        'created_at' => '2015-02-23 05:46:33',
    )),
    1 => stdClass::__set_state(array(
        'id' => 2,
        'first_name' => 'Eugene',
        'created_at' => '2016-02-23 05:46:34',
    )),
...etc...

)

score 54 · Accepted Answer · answered May 30 '16 at 05:02

54

Seems like you need to cast the stdClass Objects as Array so you can have the structure you are looking for

$result = array_map(function ($value) {
    return (array)$value;
}, $result);

answered May 30 '16 at 05:02

Jose Mujica

678
1
6
9

Good Idea! Are you sure Eloquent does not provide a function to do it? – Yevgeniy Afanasyev May 30 '16 at 05:14
4

Eloquent does it, but you are using [Query Builder](https://laravel.com/docs/5.2/queries) , not Eloquent – huuuk May 30 '16 at 05:32
`$result = array_map(fn($x) => (array)$x, $result);` – Bishal Paudel Nov 01 '22 at 22:52

Marcin Nabiałek · Answer 2 · 2017-05-24T04:48:03.793

34

The best solutions looking at performance would be changing method how data is grabbed from database:

// get original model
$fetchMode = DB::getFetchMode();
// set mode to custom
DB::setFetchMode(\PDO::FETCH_ASSOC);
// get data
$result = DB::select($sql);
// restore mode the original
DB::setFetchMode($fetchMode);

Obviously if you want to get all the data as array, it will be enough to change in config/database.php from:

'fetch'       => PDO::FETCH_CLASS,

into

'fetch'       => PDO::FETCH_ASSOC,

and then running only:

$result = DB::select($sql);

will return you multidimensional array instead of array of objects

edited May 24 '17 at 04:48

answered May 30 '16 at 18:50

Marcin Nabiałek

109,655
42
258
291

This is really advanced. Thanks. – Yevgeniy Afanasyev May 30 '16 at 23:49
1

I've found this answer related to this topic, I think this can improve what @Marcin suggest. http://stackoverflow.com/a/19369644/2848140 – Jose Mujica May 31 '16 at 00:19
15

I get this error in Laravel 5.4: `Call to undefined method Illuminate\Database\MySqlConnection::getFetchMode()`. – Ryan May 24 '17 at 01:37
That is exactly what I looked for. Thanks. – Evgeniy Maynagashev Nov 10 '17 at 16:29
I got this error in Laravel Framework 8.26.1 -- Call to undefined method Illuminate\Database\MySqlConnection::setFetchMode() – Javed Iqbal Aug 03 '21 at 01:34
This solution no longer works after Laravel 5.4 apparently. – Carmageddon Sep 08 '22 at 17:13

Yevgeniy Afanasyev · Answer 3 · 2019-12-23T00:03:00.060

13

Here is another approach that is worth to be mentioned:

$resultArray = json_decode(json_encode($result), true);

edited Dec 23 '19 at 00:03

answered May 30 '16 at 05:44

Yevgeniy Afanasyev

37,872
26
173
191

1

This's only the way around other than listen on even lister laravel,so sad lah. – Benyamin Limanto Feb 09 '20 at 11:05

Alexey Mezenin · Answer 4 · 2016-05-30T05:34:37.183

11

toArray() method converts collection to an array. Models are converted to arrays too:

The toArray method converts the collection into a plain PHP array. If the collection's values are Eloquent models, the models will also be converted to arrays

So, I guess you're doing something wrong. If you want more help, please update you question with all related code.

Also, only Eloquent collections (models) have toArray() method.

To use this method, use Eloquent:

$result = \App\User::all()->toArray();

edited May 30 '16 at 05:34

answered May 30 '16 at 04:51

Alexey Mezenin

158,981
26
290
279

$result = DB::select($sql)->toArray(); – aimme May 30 '16 at 04:52
exception 'Symfony\Component\Debug\Exception\FatalErrorException' with message 'Call to a member function toArray() on array' in ... – Yevgeniy Afanasyev May 30 '16 at 05:12
@YevgeniyAfanasyev, I guess that's the answer for aimee's code. Of course it will not work. Please show your code and I'll show you how you can convert it to an array. – Alexey Mezenin May 30 '16 at 05:16
I extended the question, I put example there. Thanks. – Yevgeniy Afanasyev May 30 '16 at 05:29
@YevgeniyAfanasyev, I've updated my answer with working Eloquent code equivalent to `$result = DB::select('select * from users');` – Alexey Mezenin May 30 '16 at 05:35
Ok, now it is legit answer that does not help :) – Yevgeniy Afanasyev May 30 '16 at 05:41
@YevgeniyAfanasyev, well that's the right way to do it. What you're doing is reinventing a wheel which is always bad. – Alexey Mezenin May 30 '16 at 06:20
@AlexeyMezenin , but I'm using Query Builder , not Eloquent. – Yevgeniy Afanasyev Dec 23 '19 at 00:01
I don't think you read the question completely. Your quoted documentation even proves your answer wrong. The question is about an array of stdClasses. The toArray function does NOT convert those for you to array. You need to map/convert. – Nasso Dec 06 '22 at 08:43

Tobias Sette · Answer 5 · 2017-07-13T02:52:43.867

4

For laravel/lumen 5.4 (that uses illuminate/database 5.4):

$pdo = DB::getPdo();
$statement = $pdo->prepare($yourQuery);
$statement->setFetchMode(\PDO::FETCH_ASSOC);
$statement->execute();
$results = $statement->fetchAll();

Or listen to Illuminate\Database\Events\StatementPrepared event: Laravel 5.4 - How to set PDO Fetch Mode?

edited Jul 13 '17 at 02:52

answered Jul 13 '17 at 02:44

Tobias Sette

185
12

score 3 · Answer 6 · answered Mar 07 '18 at 13:58

3

you can try this

DB::table('commune')->where('Commune','<>', 'ND')->orderBy('Commune')->get()->pluck('Commune');

answered Mar 07 '18 at 13:58

Mourad MAMASSI

849
1
11
14

score 1 · Answer 7 · answered Jan 10 '20 at 16:33

1

You can make a collection in order to use toArray(), very simple approach:

$result = DB::table('users')->get();
$data = collect($result)->map(function($x){ return (array) $x; })->toArray();

answered Jan 10 '20 at 16:33

Mahmoud Abdelsattar

1,299
1
15
31

The accepted answer uses the same concept of array_map-ing, but without extra conversion to and out from collection. I'm afraid you have not contributed much. – Yevgeniy Afanasyev Jan 12 '20 at 00:04

huuuk · Answer 8 · 2016-05-30T05:17:16.420

0

Try this

$result = DB::select($sql);
$arr = [];
foreach($result as $row)
{
    $arr[] = (array) $row;
}

edited May 30 '16 at 05:17

answered May 30 '16 at 04:59

huuuk

4,597
2
20
27

This is wrong. I got: local.ERROR: exception 'Symfony\Component\Debug\Exception\FatalErrorException' with message 'Call to a member function get() on array' – Yevgeniy Afanasyev May 30 '16 at 05:05
UPDATE exception 'ErrorException' with message 'call_user_func_array() expects parameter 1 to be a valid callback, class 'Illuminate\Database\MySqlConnection' does not have a method 'selectRaw'' – Yevgeniy Afanasyev May 30 '16 at 05:09
@huuuk, select accepts not only column names: `$users = DB::select('select * from users where active = ?', [1]);` - Also, your examples doesn't work. To make last one get something, you need to remove `->get()` – Alexey Mezenin May 30 '16 at 05:14
@AlexMezenin I'm sorry, I was wrong. You are right. I'm a bit confused wit `Eloquent` – huuuk May 30 '16 at 05:16

score 0 · Answer 9 · answered Jun 01 '16 at 10:35

You can do this way..

At the top

use DB;
use PDO;

--------------------

DB::setFetchMode(PDO::FETCH_ASSOC); // Set the fetch mode as array

$result = DB::select('select * from users');

For example, now you can get result like this way.

$first_email = $result[0]['email'];

Now you can change to default fetch mode.

DB::setFetchMode(PDO::FETCH_CLASS);

score 0 · Answer 10 · answered Dec 12 '22 at 20:37

Good to pick through the answers here to understand the reasons. Here's my version that I settled on:

$users = collect($users) // turn the top level array into a collection
    ->map(fn($user) => (array) $user) // cast each object to an array
    ->toArray(); // convert top level collection back to an array

You had an array of objects. Now you will have an array of arrays. Note that this doesn't do a recursive conversion of the internal array, but since it's only one level deep you're fine.

score -1 · Answer 11 · edited Sep 25 '21 at 02:14

-1

One tricky and simple way :

//Got array in stdClass converting to JSON
$temp1= json_encode($result);


//JSON to Array
$temp2= json_decode($temp1,1);`

May be it will help you

edited Sep 25 '21 at 02:14

Atika

1,025
2
6
17

answered Aug 30 '18 at 09:29

Vishal

129
1
1
10

I answered the same way on May 30 '16 at 5:44. You are a bit late. Please read existing answers before posting yours. – Yevgeniy Afanasyev Aug 30 '18 at 23:43

score -2 · Answer 12 · answered Nov 29 '19 at 06:35

-2

You can use this one. It's worked for me.

$query = DB::table('user')->select(['id','name'])->get();
$userlist = $query->toArray();

answered Nov 29 '19 at 06:35

Chandan Sharma

2,321
22
22

Mohsen · Answer 13 · 2018-02-08T10:43:36.233

-4

Easiest way is using laravel toArray function:

public function toArray()
{
    return array_map(function ($value) {
        return $value instanceof Arrayable ? $value->toArray() : $value;
    }, $this->items);
}

edited Feb 08 '18 at 10:43

answered Feb 08 '18 at 10:18

Mohsen

4,049
1
31
31

toArray() is method, not just function. And it converts collection to an array. I don't have a collection for starters, I have an array of objects. Only because DB::select($sql); returns array of objects. So your answer is not help. – Yevgeniy Afanasyev Feb 08 '18 at 22:19

Laravel 5.1 DB:select toArray()

13 Answers13

Linked