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 — xNoJustice
– xNoJustice, Commented Sep 6, 2020 at 13:17
no. Its an array i have object from query and i want to sort all object by price — Recep Gümüş
– Recep Gümüş, Commented Sep 6, 2020 at 14:11

IndianCoding · Accepted Answer · 2020-09-06 15:48:12Z

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 6, 2020 at 15:48

answered Sep 6, 2020 at 14:53

IndianCoding

2,6831 gold badge9 silver badges13 bronze badges

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

2 Comments

Recep Gümüş Over a year ago

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üş Over a year ago

exactly same error, i tried with ->> and with -> both doesnt work also, do you have any orher idea maybe?

SneakyLenny · Accepted Answer · 2022-03-30 12:06:06Z

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

Collectives™ on Stack Overflow

laravel collection sort by json data from database

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related