How to retrieve a result set from a raw query as an array instead of an object in Laravel 3

Question

By default, Laravel's raw query methods return results as arrays of stdClass objects:

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [username] => admin
            [password] => admin123
            [email] => admin@admin.com
            [created_at] => 2012-12-06 18:57:19
            [updated_at] => 2012-12-06 00:00:00
        )

    [1] => stdClass Object
        (
            [id] => 2
            [username] => userna
            [password] => user
            [email] => user@gmail.com
            [created_at] => 2012-12-06 00:00:00
            [updated_at] => 2012-12-05 00:00:00
        )
)

The question is how to have Laravel return an array of Arrays instead:

   Array
    (
        [0] => Array
            (
                [id] => 1
                [username] => admin
                [password] => admin123
                [email] => admin@admin.com
                [created_at] => 2012-12-06 18:57:19
                [updated_at] => 2012-12-06 00:00:00
            )

        [1] => Array
            (
                [id] => 2
                [username] => userna
                [password] => user
                [email] => user@gmail.com
                [created_at] => 2012-12-06 00:00:00
                [updated_at] => 2012-12-05 00:00:00
            )
    )

This made perfect sense to me, I'm trying to learn Laravel and ran into this roadblock. It's a real and valid question - wondering if the people that say it isn't have any experience with this framework? — NightMICU, Jan 08 '13 at 18:00
Why the hell was this closed? People need to get over themselves. — dardub, Apr 11 '13 at 21:38

score 36 · Answer 1 · answered Dec 06 '12 at 16:01

36

You may also get all the result always as array by changing

application/config/database.php

'fetch' => PDO::FETCH_CLASS,

on line 31 to

'fetch' => PDO::FETCH_ASSOC,

answered Dec 06 '12 at 16:01

Sinan Eldem

5,564
3
36
37

but how to do it dynamically in the code? I only want to fetch as Array for one DB connection. I have this connection `$sqlserv_connection = DB::connection($this->getSqlServerConnectionName());` I want something like `$sqlserv_connection->fetch(PDO::FETCH_ASSOC)` I know this doesn't exist but how can I achieve this? – Mahmoud Zalt Sep 15 '15 at 15:06
1

Just want to point out the 'fetch' setting is not connection specific and is at the top of database.php. Somehow I inferred it was connection specific and added it to each connection, which has no effect. Hopefully that helps some other misguided soul. – Martin Woolstenhulme Mar 24 '16 at 00:18
2

Not sure about 4 or 3, but in Laravel 5, you can make a query: `$query = DB::table('companies')->where('status', 'active');` then set this before you run it `DB::connection()->setFetchMode(\PDO::FETCH_ASSOC);` then get the result `$result = $query->get()->toArray();` and `$result` will be an array of arrays – Phillip Harrington Jan 07 '17 at 00:28
2

@PhillipHarrington this seems to have been removed :( `Error: Call to undefined method Illuminate\Database\MySqlConnection::setFetchMode()` – Jeff Puckett Nov 25 '19 at 02:16
@JeffPuckett Looks like it was dropped in 5.4 per this issue: https://github.com/laravel/framework/issues/17728 – Phillip Harrington Nov 26 '19 at 03:48

David Barker · Accepted Answer · 2016-03-16T09:50:58.477

22

Original answer for Laravel 3

Eloquent has a method to_array()

From docs:

The to_array method will automatically grab all of the attributes on your model, as well as any loaded relationships.

$user = User::find($id);

return Response::json($user->to_array());

or

return Response::eloquent($user);

If you are using fluent, you can do as Sinan suggested and change the global configuration to return an associative array rather than objects.

Alternatively, you can convert an object to and from JSON to convert it to an array although the global option would be preferable in most cases. You can use the following in projects where you prefer objects normally but in certain edge cases need an array. This method will not play well with Eloquent, use the methods above in that case.

$users = DB::table('users')->where('name', '=', 'david')->get();

return array_map(function($val)
{
    return json_decode(json_encode($val), true)
}, $users);

Another option would be to temporarily change the runtime configuration

Config::set('database.fetch', PDO::FETCH_ASSOC);

For Laravel ~4

In Laravel 4 onwards, all method names conform to PSR-2 standards.

$user = User::findOrFail($id);

return Response::json($user->toArray());

// In Laravel 5 onward the functions are preferred to facades.
return response()->json($user->toArray());

edited Mar 16 '16 at 09:50

answered Dec 06 '12 at 15:10

David Barker

14,484
3
48
77

1

Unhandled Exception Message: Call to undefined method stdClass::to_array() – Ramesh Dec 06 '12 at 15:46
This is for Laravel 3.x, `to_array()` is a method of Eloquent. Also in the docs http://laravel.com/docs/database/eloquent. Could you share some of your code for us to see where the problem lies? – David Barker Dec 06 '12 at 15:57
Does Fluent have an equivalent of `to_array()`? – Nyxynyx Dec 07 '12 at 00:07
1

His problem is: ``$arrOfObjects = DB::connection()->query('SELECT * from `table` LIMIT 0, 3');`` Which *does* return an array of objects. Sinan answered it. The stdClass is determined in the laravel/database/connection.php file based on the config. – Dustin Graham Apr 17 '13 at 09:57
@Sinan is correct. Simply modify the config file and move on with your project. Be careful though if your app is a workflow since not all users will have this setting. If it's just for you then this works fine. – enchance Apr 17 '14 at 18:06
in laravel 4, this function not found – vuhung3990 Sep 15 '14 at 03:58
1

That's beause this answer is for laravel 3. Laravel 4 is `toArray()` – David Barker Sep 15 '14 at 09:15

score 10 · Answer 3 · answered Dec 06 '12 at 15:01

10

I don't know if laravel has in built function for returning results as array but if not you can use this snippet:

Where $data is your returned array of objects

$data = json_decode(json_encode((array) $data), true);

answered Dec 06 '12 at 15:01

Dale

10,384
21
34

@ramesh No problem, there is another answer here that may be of more use as it is using laravel methods see DavidBarker answer – Dale Dec 06 '12 at 15:13
This is a very bad practice, since you're both encoding it and then decoding it. This will result in the exact same outcome: `$data = (array) $data;` – Niklas Modess Dec 06 '12 at 15:44
@nerdklers It wont because `$data` is already an array it won't change the inner objects to an array, I did try that method myself before posting this unruly workaround :) – Dale Dec 06 '12 at 15:46

How to retrieve a result set from a raw query as an array instead of an object in Laravel 3

3 Answers3

Original answer for Laravel 3

For Laravel ~4

Linked