How could I make join with column type text contains JSON array using Laravel query builder

Question

I want to make join using column contains JSON array and column type is text. transaction table

id	type
1	cash
2	cash
3	credit

route_activities table

id	transactions
1	[{transaction_id:"1"},{transaction_id:"2"}]
2	[{transaction_id:"2"}]

so I was trying with these code

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

and it's not working. I get some links this and this

but all of them is not giving me what I am looking for. any help :)?

I am using Laravel-v7.30. and Mysql v5.7

user3532758 · Accepted Answer · 2021-01-11 03:59:28Z

Are you looking for something like this?

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)), 
  '$'
);

This should yield something similar to this:

trans_id	transactions	type
1	[{"transaction_id": "1"}, {"transaction_id": "2"}]	cash
1	[{"transaction_id": "1"}, {"transaction_id": "2"}]	cash
2	[{"transaction_id": "3"}]	credit

Seeing that you are already using selectRaw you can throw in the query inside a DB::select(..) as in:

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

Considering route activities will be duplicated, I don't know if the result set is usable as is, but I hope this points you in the right direction.

Collectives™ on Stack Overflow

How could I make join with column type text contains JSON array using Laravel query builder

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related