Best way to execute string functions on returned database object

Question

I need to run various string functions on the data returned from the database before sending them to the view using Laravel 5.3. Basic things like str_replace ().

Now, there may be a good way to configure Accessors on my model and somehow use the model on the landing page, but I thought I would go on a different route and just make this request manually outside the model.

So, I have a view provider that successfully gets my data into the view. It looks like this:

class ViewLandingProvider extends ServiceProvider { public function boot() { // process when featured homepage element is present... View::composer('mybladetemplate', function ($view){ $featuredProperties = DB::table('properties') ->where([ ['featured_property', '=', '1'], ['supplier_id', '=', 123], ]) ->orderBy('prop_id', 'desc') ->limit(6) ->get(); // run str_replace! $featuredProperties->each(function($property){ $property->prop_url=str_replace("http://domain.com/","http://www.domain.com/",$property->prop_url); }); View::share('featuredProperties', $featuredProperties); }); } } 

then it's the loops inside the view and it all works great

  @if(isset($featuredProperties)) @foreach ($featuredProperties as $property) <li> <a title="{{ $property->prop_name }}" href="{{ $property->prop_url }}"></a> </li> @endforeach @endif 

As you can see in the above example, I have str_replace () working on a data collector using → each (), and this works to allow me to perform a simple string replacement that I need to take.

Being Laravel, I am sure that there is some kind of magic that could be pulled out here to make it more reasonable.

So, is there a way in the actual code of the database query that I can indicate that a particular column to be returned should automatically run a function on it?

To clarify, I want to make these changes to the provider php, not the view file, and I want to do this outside the model using Accessors.

Answer 1

I think you can look for a macro collection. You will register it in your AppServiceProvider , for example:

 Collection::macro('formatPropUrl', function() { return collect($this->items)->map(function($property) { $property->prop_url=str_replace("http://domain.com/","http://www.domain.com/",$property->prop_url); return $property; }); }); 

Then for your request you can do:

 $featuredProperties = DB::table('properties') ->where([ ['featured_property', '=', '1'], ['supplier_id', '=', 123], ]) ->orderBy('prop_id', 'desc') ->limit(6) ->get() ->formatPropUrl(); 

Answer 2

You can write a selection request as:

 $featuredProperties = DB::table('properties') ->where([ ['featured_property', '=', '1'], ['supplier_id', '=', 123], ]) ->select('*', DB::raw("replace(prop_url, 'http://domain.com/', 'http://www.domain.com/') as new_prop_url")) ->orderBy('prop_id', 'desc') ->limit(6) ->get(); 

And then, in your opinion, you can do this:

 @if(isset($featuredProperties)) @foreach ($featuredProperties as $property) <li> <a title="{{ $property->prop_name }}" href="{{ $property->new_prop_url }}"></a> </li> @endforeach @endif 

Answer 3

This would be a good option for a Presenter template. There are several Laravel packages to help with this, but the most modern one looks like mccool / laravel-auto-presenter .

The idea behind the package is that you create a Presenter object that wraps the Model , which should display the information. The Presenter object contains all the logic for any data transformations necessary for presentation.

This is untested, but an example implementation is shown below. Create the app\Presenters directory and add the following file:

application \ Presenter \ PropertyPresenter.php

 namespace App\Presenters; use App\Property; use McCool\LaravelAutoPresenter\BasePresenter; class PropertyPresenter extends BasePresenter { // this parameter MUST be named $resource public function __construct(Property $resource) { $this->wrappedObject = $resource; } // create functions for any properties you'd like to present public function prop_url() { return str_replace("http://domain.com/", "http://www.domain.com/", $this->wrappedObject->prop_url); } // you can also create functions for properties that don't actually // exist on the model. in view, access via $property->secure_prop_url public function secure_prop_url() { return str_replace("http:", "https:", $this->prop_url); } } 

Now you need to change your Property model to tell the speaker about this. He will have to implement the HasPresenter interface and define the getPresenterClass() method.

 namespace App; use App\Presenters\PropertyPresenter; use Illuminate\Database\Eloquent\Model; use McCool\LaravelAutoPresenter\HasPresenter; class Property extends Model implements HasPresenter { public function getPresenterClass() { return PropertyPresenter::class; } } 

Finally, all this logic depends on the actual access to the Property model inside the view. Your current logic uses DB::table('properties') , which will create a Collection of stdClass objects. This needs to be changed in order to actually use your Property model, so your Collection will receive your presentation of Property models.

 class ViewLandingProvider extends ServiceProvider { public function boot() { // process when featured homepage element is present... View::composer('mybladetemplate', function ($view) { $featuredProperties = \App\Property::where([ ['featured_property', '=', '1'], ['supplier_id', '=', 123], ]) ->orderBy('prop_id', 'desc') ->limit(6) ->get(); View::share('featuredProperties', $featuredProperties); }); } } 

Your idea does not need to be changed at all.

Answer 4

The best way to replace a string in a query is with the sql replace user function. When working with large datasets, there is a performance issue for replacing strings with php. Laravel has a convenient way to expand the assembly in Builder , so you do not need to write raw requests. Here is an example of how to do this.

 <?php namespace App\Providers; use Illuminate\Database\Query\Builder; use Illuminate\Support\Facades\DB; use Illuminate\Support\ServiceProvider; class AppServiceProvider extends ServiceProvider { /** * Bootstrap any application services. * * @return void */ public function boot() { // } /** * Register any application services. * * @return void */ public function register() { Builder::macro('replace', function ($column, $searchText, $replaceText) { return $this->addSelect(DB::raw("replace($column, '$searchText', '$replaceText') as $column")); }); } } 

With this Macro registered in the query builder, your request will look like this.

 $featuredProperties = DB::table('properties') ->where([ ['featured_property', '=', '1'], ['supplier_id', '=', 123], ]) ->replace('prop_url', 'http://domain.com/', 'http://www.domain.com/') ->orderBy('prop_id', 'desc') ->limit(6) ->get(); 

Answer 5

The get() method of Illuminate\Database\Query\Builder returns an instance of Illuminate\Support\Collection . This class offers many methods for managing a data set.

You can learn more about this in the documentation .