Laravel在Json列上加入了两个表 [英] Laravel join two tables on Json Column

问题描述

我有两个表(模型)，一个表带有一个json列，该列可能包含其他表上的ID.我想将这两个连接在一起，如何将laravel连接链接到我的查询中.

I have two tables(models), one with a json column that may contain the id on other table. I want to join these two together, how do i chain a laravel join to my query.

推荐答案

因此，事实证明联接无效，因为json列将id字段存储为字符串，而联接表的id存储为整数.将结果转换为整数可解决此问题.假设表'a'是第一个表，其中包含一个json列，第二个表是表'b'，其中包含一个ID，所以我做到了.

SO it turns out the join wasn't working because the json column was storing the id field as a string and the joined table's id is stored as an integer. Casting the result into an integer fixed this. Let's say table 'a' is the first table that contains a json column with an id on second table which is table 'b'.So I did.

DB::table('a')->join('b','b.id',DB::Raw("CAST(a.data->'$.id' AS UNSIGNED)"))->get();

这篇关于Laravel在Json列上加入了两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！