Laravel json join , Trying to Join JSON column having multiple ids with table of locations having location ids, query builder

Question

I trying to join to two tables one with locations and another archive_data that can have multiple locations. so I am storing locations in archive data as json like this ["1","2","3"]

My query:

                    DB::table('archive_data as a')
                    ->join('person as pe', 'pe.pe_id', '=', 'a.a_pe_id')
                    ->join('portfolio as p', 'p.p_id', '=', 'a.a_p_id')
                    ->join('tag as ta', 'ta.ta_id', '=', 'a.a_ta_id')
                    ->join('type as t', 't.t_id', '=', 'a.a_ty_id')
                    ->join('location as l', 'l.l_id', '=',"a.a_lc_id")
                    ->join('series as s', 's.s_id', '=', 'a.a_s_id')
                    ->join('bundle as b', 'b.b_id', '=', 'a.a_b_id');

Please tell me how can I join these two tables using laravel query builder. Thank you in advance.

Casper Witting · Accepted Answer · 2020-04-28 20:10:01Z

0

I don't exactly know what the business case is, but it might be better to have another look at your database structure in order to more easily retrieve data.

If you really need to store your archived data in json, than I'd suggest to take a look on this similar stackoverflow question Laravel join two tables on Json Column

answered Apr 28, 2020 at 20:10

Casper Witting

731 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

M Atique Khan Over a year ago

The logic is archive_data is a table, its one record can have multiple locations. And to get location details I want to join it with locations table. But finding it difficult to join on json column.

Collectives™ on Stack Overflow

Laravel json join , Trying to Join JSON column having multiple ids with table of locations having location ids, query builder

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related