CODEWITHSUNDEEP
phpmysqllaravel
patrick nwakwoke
patrick nwakwoke

Reputation: 191

Laravel join two tables on Json Column

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.

Upvotes: 4

Views: 9279

Answers (2)

Eazy Sam
Eazy Sam

Reputation: 308

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.

Upvotes: 2

patrick nwakwoke
patrick nwakwoke

Reputation: 191

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();

Upvotes: 2

Related Questions