laravel collection sort by json data from database

Question

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

Can someone please help me?

Does this answer your question? [how to sort a json object in laravel](https://stackoverflow.com/questions/50450667/how-to-sort-a-json-object-in-laravel) — xNoJustice, Sep 06 '20 at 13:17
no. Its an array i have object from query and i want to sort all object by price — Recep Gümüş, Sep 06 '20 at 14:11

IndianCoding · Answer 1 · 2020-09-06T15:48:12.063

0

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();

edited Sep 06 '20 at 15:48

answered Sep 06 '20 at 14:53

IndianCoding

2,602
1
6
12

its giving this error ""SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '>'$.price' AS DECIMAL(10,6))' at line 1 (SQL: select * from `posts` where `type` = product and `product_options` is not null order by CAST(data->'$.price' AS DECIMAL(10,6)))"" you typed "data" is that correct or should i edit with my own data name or smt? – Recep Gümüş Sep 06 '20 at 15:47
exactly same error, i tried with ->> and with -> both doesnt work also, do you have any orher idea maybe? – Recep Gümüş Sep 06 '20 at 15:54

SneakyLenny · Answer 2 · 2022-03-30T12:06:06.667

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()

laravel collection sort by json data from database

2 Answers2