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.
3
-
1Have u checked thisJigs1212– Jigs12122018-03-21 06:14:15 +00:00Commented Mar 21, 2018 at 6:14
-
Doesn’t work for joinspatrick nwakwoke– patrick nwakwoke2018-03-21 08:49:38 +00:00Commented Mar 21, 2018 at 8:49
-
@Jigs1212 That helped! And json-inner-join works after some chages.Eazy Sam– Eazy Sam2018-03-21 10:42:39 +00:00Commented Mar 21, 2018 at 10:42
Add a comment
|
2 Answers
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();
Comments
Apply the JSON logic inside the join query like:
Post::select('posts.id', 'posts.field1','posts.field2','samples.id as sample_id', ..)
->join('samples', 'posts.json_col_name.id', '=', 'samples.id')
->get();
Make sure that you make no conflict for the query builder while picking columns (use field_name as another_name) when both models have fields with common name.
2 Comments
patrick nwakwoke
I think 'posts->json_col_name_id' should be 'posts.json_col_name->id'. From the question though, just one table has the json column, not both.
