时间戳

首页 > 解决方案 > 如何使用 Laravel 查询生成器加入包含 JSON 数组的列类型文本

php - 如何使用 Laravel 查询生成器加入包含 JSON 数组的列类型文本

问题描述

我想使用包含 JSON 数组的列进行连接，列类型是文本。事务表

ID 类型
1 现金
2 现金
3 信用

路线活动表

ID 交易
1 [{transaction_id:"1"},{transaction_id:"2"}]
2 [{transaction_id:"2"}]

所以我正在尝试使用这些代码

$sale_data = DB::table('route_activities')
            ->Join('routes','routes.driver_id','=','route_activities.driver_id')
            ->Join('transactions','transactions.transaction_id',
                DB::Raw("route_activities.transactions->'$[*].transaction_id',CAST(transactions.transaction_id as JSON)")
            )
            ->selectRaw('
                route_activities.activity as activity,
                route_activities.check_in as check_in,
                route_activities.date as date,
                route_activities.check_out as check_out,
                route_activities.coordinates as coordinates,
                CASE WHEN visit_status = "Productive" THEN "true" ELSE "false" END as is_productive
            ')
            ->get();

它不工作。我得到一些链接这个和这个

但他们都没有给我我想要的东西。任何帮助:)？

我正在使用 Laravel-v7.30。和 MySQL v5.7

标签： phpmysqllaravel

解决方案

你在寻找这样的东西吗？

SELECT a.*, t.* 
FROM route_activities a
JOIN transactions t ON JSON_CONTAINS(
  JSON_EXTRACT(a.transactions, '$[*].transaction_id'), 
  JSON_QUOTE(CONVERT(t.id, char)), 
  '$'
);

这应该产生类似的东西：

trans_id 交易 类型
1 [{“transaction_id”：“1”}，{“transaction_id”：“2”}] 现金
1 [{“transaction_id”：“1”}，{“transaction_id”：“2”}] 现金
2 [{“transaction_id”：“3”}] 信用

看到您已经在使用selectRaw，您可以在 a 中输入查询DB::select(..)，如下所示：

DB::select("ra.activity as activity,
            ra.check_in as check_in,
            ra.date as date,
            ra.check_out as check_out,
            ra.coordinates as coordinates,
            CASE WHEN visit_status = "Productive" THEN "true" ELSE "false" END as is_productive 
            FROM route_activities ra
            JOIN transactions t ON JSON_CONTAINS(json_extract(a.transactions, '$[*].transaction_id'), json_quote(convert(t.id, char)), '$')
            JOIN routes r ON r.driver_id = ra.driver_id")
->get()

考虑到路线活动将被重复，我不知道结果集是否可以按原样使用，但我希望这能为您指明正确的方向。

推荐阅读