英文:
How to use JSON_TABLE with Eloquent?
问题
一个数据库列包含一个类似这样的 JSON 数组：
[5, 3, 1]
我想对这个列的各个值进行分组。我可以通过使用原始查询来实现：
DB::table(DB::raw('records, JSON_TABLE(records.column, "$[*]" COLUMNS(Value INT PATH "$")) AS data'))
->where('deleted_at', '=', null)
->where('user_id', '=', $this->user->id)
->selectRaw('data.Value')
->groupBy('data.Value')
->toSql()
生成的查询如下：
SELECT
data.Value
FROM
records,
JSON_TABLE(
records.column,
"$[*]" COLUMNS(Value INT PATH "$")
) AS data
WHERE
`deleted_at` IS NULL
AND `user_id` = 2
GROUP BY
`data`.`Value`
我的问题是是否有一种更加优雅的方式来使用 Eloquent 来实现相同的功能。
英文:
Say a database column contains a json array like this:
[5,3,1]
I want to do a groupBy on the individual values of this column. I could achieve that by using a raw query:
DB::table(DB::raw('records, JSON_TABLE(records.column, "$[*]" COLUMNS(Value INT PATH "$")) AS data'))
->where('deleted_at', '=', null)
->where('user_id', '=', $this->user->id)
->selectRaw('data.Value')
->groupBy('data.Value')
->toSql()
which generates e.g.
select
data.Value
from
records,
JSON_TABLE(
records.column,
"$[*]" COLUMNS(Value INT PATH "$")
) AS data
where
`deleted_at` is null
and `user_id` = 2
group by
`data`.`Value`
My question is whether there is a more elegant way of achieving the same using eloquent.
答案1
得分: 2
你可以使用自定义的 DTO 或集合对象来转换该字段，如下所示：
protected $casts = [
'options' => AsCollection::class . ':' . OptionCollection::class,
];
更多信息，请参考：https://laravel.com/docs/10.x/eloquent-mutators#array-and-json-casting
或者使用 spatie/laravel-data 作为包。
英文:
You can cast that field using a custom DTO or COllection object
like
protected $casts = [
'options' => AsCollection::class.':'.OptionCollection::class,
];
https://laravel.com/docs/10.x/eloquent-mutators#array-and-json-casting
or use as package as spatie/laravel-data
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论