php Laravel中的OrderBy json列

w8rqjzmb 于 2023-03-11 发布在 PHP

关注(0)|答案(2)|浏览(100)

我有一个表，其中有json列命名为测量，它是这样的：

{
"area" : "100",
"rooms" : "2",
.
.
.
}

我尝试按区域排序，但没有返回正确的结果：

Home::where('status', 'active')->orderBy('measurements->area', 'asc')->get();

之后我用了这个：

Home::query()
    ->where('status','active')
    ->orderByRaw('CAST(features->area AS unsigned)', 'asc')
    ->get();

但它返回错误：

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'area AS unsigned) limit 14 offset 0' at line 1

怎样才能准确地按json列排序？请编写代码。

php

来源：https://stackoverflow.com/questions/75686667/orderby-json-column-in-laravel

2条答案

按热度按时间

blpfk2vs1#

试试看：

->orderByRaw('CAST(features->"$.area" AS unsigned)', 'asc')

或

->orderByRaw('CAST(JSON_EXTRACT(features, "$.area") AS unsigned)', 'asc')

赞(0）回复(0）举报 2023-03-11

5uzkadbs2#

试试看

DB::table('home')
       ->orderByRaw("CAST(JSON_EXTRACT(measurements, '$. area') AS DECIMAL(10,2)) DESC")
       ->get();

或

Home::orderBy('measurements->>area', 'desc')->get();

赞(0）回复(0）举报 2023-03-11

我来回答

php Laravel中的OrderBy json列

2条答案

相关问题

热门标签

最新问答