laravel collection sort by json data from database

user_7861806 0
Viewed 536

I want to order my all products by price. In Product table there is product_options column and inside of that column i have json data, something like this;

{"price":390,"discounted_price":26,"quantity":168}

I tried to orderBy product_options but its returnin by alphabetic so 17 comes after 1000. It looks like i should use collection functions for this but i am really new at that.

$searchData = DB::table('posts')->where('type','=','product')->whereNotNull('product_options')->orderBy('product_options->price')->get();

When i return dd function it looks like that enter image description here

Can someone please help me?

laravel collections sorting
2 Answers

Just cast json property as decimal or integer.

$searchData = DB
    ::table('posts')
    ->where('type', 'product')
    ->whereNotNull('product_options')
    ->orderByRaw("CAST(product_options->>'$.price' AS DECIMAL(10,6))")
    ->get();
user_106450940

I found a way to do it and described how on this git hub discussion .

Basically what I did was I created a scope that takes SQL casting types from the model to cast the props into the defined data type (or it will take the default). Which allows it to be sorted the way that data type is supposed to be sorted.

Like this:

   public function scopeIncludeJson(Builder $query, $attribute, $name = null)
   {
       $attribute = str_replace('->', '.', $attribute);
       $path = explode('.', $attribute);
       // Skip if column isn't JSON. I could probably make use of $casts 
       // by checking if x's cast is 'array'. But I wasn't sure if that's the
       // right way, so let's leave it like this for now.
       if (in_array($path[0], $this->jsonColumns)) {
           $jsonSelector = '$.' . implode(".", array_slice($path, 1));
           $cast = $this->jsonCasts[$attribute] ?? $this->defaultJsonCast;
           return $query->selectRaw("cast(json_value(`$path[0]`, '$jsonSelector') as $cast) as `". (!empty($name) ? $name : $attribute) . "`");
       }
       return $query;
   }

That way I can do this:

$items = Model::includeJson('statistics.data.used', 'customName')
    ->orderBy("customName", "desc")
    ->skip($pageIndex - 1) * $pageSize)
    ->take($pageSize)
    ->get();

--- EDIT ---

In your case if its implemented successfully it would only take:

// In model
class Product extends ModelWithJson
{
    // ...
    protected $jsonColumns = [
        'options'
    ];

    public $jsonCasts = [
        'options.price' => 'unsigned int'
    ]
    // ...
}

// In controller
Product::includeJson('options.price', 'price')->orderBy('price')->get()
user_129595790
Related