CODEWITHSUNDEEP
laravel
16_018_RHR
16_018_RHR

Reputation: 417

laravel join two tables based array of id's

I have two tables purchases & purchase_types. In the purchases table one field is "purchaseCategories" where the data is stored as an array of id's(from purchase_types) like [1,2].

the purchase_types table look like this--

id purchaseType
1 Purchase Type  1
2 Purchase Type  2
3 Purchase Type  3

and the purchases table look like this--

id purchaseCategory
1 [1,2]
2 [1,2,3]
3 [1,3]

I have tried this--

$purchaseIds = [1, 2];

        $purchases = DB::table('purchases')
            ->join('purchase_types', 'purchases.purchaseCategory', '=', 'purchase_types.id')
            ->whereIn('purchases.purchaseCategory', $purchaseIds)
            ->get();

        dd($purchases);

This shows empty array.

how do I join this two tables to show the purchase types in my blade file?

Upvotes: 1

Views: 504

Answers (1)

IGP
IGP

Reputation: 15786

It's not ideal, but you could use JSON_CONTAINS as your join condition. Your whereIn should also be JSON_CONTAINS.

SELECT *
FROM
    purchases AS p
INNER JOIN
    purchase_types AS pt
    ON JSON_CONTAINS(p.purchaseCategory, CAST(pt.id AS CHAR(50)))
WHERE
    JSON_CONTAINS(p.purchaseCategory, "[1,2]")

The casting is necessary because JSON_CONTAINS expects a string. Passing the pt.id column directly would cause an error.

$purchaseIds = [1, 2];
$purchases = DB::table('purchases', 'p')
    ->join('purchase_types AS pt', function ($join) {
        $join->whereJsonContains('p.purchaseCategory', DB::raw('CAST(pt.id AS CHAR(50))'));
    })
    ->whereJsonContains('p.purchaseCategory', $purchaseIds)
    ->get();

Here's a DB Fiddle demonstrating this query.

Upvotes: 2

Related Questions