Laravel - convert result of DB::raw to an array of strings?

Question

I am trying to work out how to convert the result of Laravel's DB::raw to an array of strings, similar to ExecuteScalar in other languages but for many rows?

In my resource, I call the method like:

return [
    'id' => $this->id,
    'name' => $this->name,
    'member_images' => $this->getNewestMemberPictures(),
    'created_at' => $this->created_at,
    'updated_at' => $this->updated_at,
];

And then make a call to DB::raw for the member images:

public function getNewestMemberPictures() {
        return DB::raw("SELECT `picture`
            FROM `scraper_profile_data` pd
            INNER JOIN `scraper_collection_entries` ce ON ce.`item_id` = pd.`profile_id`
            WHERE ce.`collection_id` = " . $this->id .
            "ORDER BY ce.`created_at` DESC
            LIMIT 10;");
}

Currently member_images is set to empty, I can only assume it is because Laravel doesn't automatically cast it to a string of arrays. How can I do this with Laravel?

you are not executing a query, DB::raw wraps an expression in an object to signify it will be used raw in a query, it returns an Illuminate\Database\Query\Expression object ... if you want to execute a raw select statement to get a result you are probably looking for DB::select(...) — lagbox
– lagbox, Commented Nov 6, 2020 at 6:45
@lagbox thank you, I cannot see any usage of actual raw string queries in their documentation: laravel.com/docs/8.x/queries#selectraw only chaining methods with partial raw statements? I think I would benefit from a code example. — user5405648
– user5405648, Commented Nov 6, 2020 at 6:49
laravel.com/docs/8.x/database#running-queries is for raw queries ... the link you have is for Query Builder — lagbox
– lagbox, Commented Nov 6, 2020 at 6:50
@lagbox thank you, now that I am using the correct method, how does one get this to output an array of strings? I am still getting an empty return value from getNewestMemberPictures, do I have to explicitly foreach and call ->picture ? There is nothing in their documentation for returning an array of column values, that I can see. — user5405648
– user5405648, Commented Nov 6, 2020 at 6:54

BABAK ASHRAFI · Accepted Answer · 2020-11-06 11:24:36Z

4

I recommend using eloquent model for getting queries, but if you insist on using DB facade, you can collect the result of DB to get your desired results such as retrieving just a variable like picture.

Note: Result of of DB::select is an array of objects (instances of php stdClass). By collecting the result array, you can benefit from a vast of functions provided by laravel collection.

Below code may help you achieve this using pluck method of collection.

$query_result = DB::select("SELECT `picture`
    FROM `scraper_profile_data` pd
    INNER JOIN `scraper_collection_entries` ce ON ce.`item_id` = pd.`profile_id`
    WHERE ce.`collection_id` = " . $this->id .
    "ORDER BY ce.`created_at` DESC
    LIMIT 10;");

$result = collect($query_result)->pluck('picture')->toArray();
return $result;

edited Nov 6, 2020 at 11:24

answered Nov 6, 2020 at 11:19

BABAK ASHRAFI

4,3114 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user5405648 Over a year ago

Thank you, the reason I'm using raw is because Eloquent is built on abstraction, and with the level of abstraction it contains comes a very large performance hit on joins versus raw querying.

BABAK ASHRAFI Over a year ago

You're welcome. As you care about performance, there is a package that measure different aspects of the app in here. Hope it helps.

Collectives™ on Stack Overflow

Laravel - convert result of DB::raw to an array of strings?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related