Laravel 9 add new key and values using crossJoin collection

Question

so i have a table Items my table has a row product and price only. my goal is how to add key with values using crossJoin collect

here is my code when fetching all data in my table im using each collect to add new row with values

public function ProductList(){
    $items = Item::all(); //rows only 'product', 'price'
    $Brand = collect($items)->each(function($newRow){
        $newRow->color = 'Blue'; // adding this in row
        $newRow->size = 'Large'; // adding this in row
    });
    return $Brand ;
}

here is the output of that code

[
{
"id": 1,
"product": "Lee",
"price": "500",
"color": "Blue",
"size": "Large"
},
{
"id": 2,
"product": "Jeans",
"price": "400",
"color": "Blue",
"size": "Large"
},
{
"id": 3,
"product": "Guess",
"price": "300",
"color": "Blue",
"size": "Large"
},
{
"id": 4,
"product": "Levis",
"price": "200",
"color": "Blue",
"size": "Large"
}
]

in the output color and size has been added to key with the values of blue and large. but that it's not the output i want to show. i want is each items color have a BLUE , BLACK , WHITE and size LARGE , MEDIUM , SMALL

like this code: ( i will not include the price )

   public function test(){
        $item = ['Lee','Jeans','Guess','Levis'];
        $size = ['LARGE','MEDIUN','SMALL'];
        $color = ['BLUE','BLACK','WHITE'];
        return collect($item)->crossJoin($size,$color);
    }

the output of this

  [
    [
    "Lee",
    "LARGE",
    "BLUE"
    ],
    [
    "Lee",
    "LARGE",
    "BLACK"
    ],
    [
    "Lee",
    "LARGE",
    "WHITE"
    ],
    [
    "Lee",
    "MEDIUN",
    "BLUE"
    ],
    [
    "Lee",
    "MEDIUN",
    "BLACK"
    ],
    [
    "Lee",
    "MEDIUN",
    "WHITE"
    ],
    [
    "Lee",
    "SMALL",
    "BLUE"
    ],
    [
    "Lee",
    "SMALL",
    "BLACK"
    ],
    [
    "Lee",
    "SMALL",
    "WHITE"
    ],
    [
    "Jeans",
    "LARGE",
    "BLUE"
    ],
    [
    "Jeans",
    "LARGE",
    "BLACK"
    ],
    [
    "Jeans",
    "LARGE",
    "WHITE"
    ],
    [
    "Jeans",
    "MEDIUN",
    "BLUE"
    ],
    [
    "Jeans",
    "MEDIUN",
    "BLACK"
    ],
    [
    "Jeans",
    "MEDIUN",
    "WHITE"
    ],
    [
    "Jeans",
    "SMALL",
    "BLUE"
    ],
    [
    "Jeans",
    "SMALL",
    "BLACK"
    ],
    [
    "Jeans",
    "SMALL",
    "WHITE"
    ],
    [
    "Guess",
    "LARGE",
    "BLUE"
    ],
    [
    "Guess",
    "LARGE",
    "BLACK"
    ],
    [
    "Guess",
    "LARGE",
    "WHITE"
    ],
    [
    "Guess",
    "MEDIUN",
    "BLUE"
    ],
    [
    "Guess",
    "MEDIUN",
    "BLACK"
    ],
    [
    "Guess",
    "MEDIUN",
    "WHITE"
    ],
    [
    "Guess",
    "SMALL",
    "BLUE"
    ],
    [
    "Guess",
    "SMALL",
    "BLACK"
    ],
    [
    "Guess",
    "SMALL",
    "WHITE"
    ],
    [
    "Levis",
    "LARGE",
    "BLUE"
    ],
    [
    "Levis",
    "LARGE",
    "BLACK"
    ],
    [
    "Levis",
    "LARGE",
    "WHITE"
    ],
    [
    "Levis",
    "MEDIUN",
    "BLUE"
    ],
    [
    "Levis",
    "MEDIUN",
    "BLACK"
    ],
    [
    "Levis",
    "MEDIUN",
    "WHITE"
    ],
    [
    "Levis",
    "SMALL",
    "BLUE"
    ],
    [
    "Levis",
    "SMALL",
    "BLACK"
    ],
    [
    "Levis",
    "SMALL",
    "WHITE"
    ]
  ]

so i tried this code but the output i want is not like this.

  public function ProductList(){
        $items = Item::all(); //rows only 'product', 'price'
        $statusBrand = collect($items)->each(function($newRow){
            $newRow->color = 'Blue'; // adding this in row
            $newRow->size = 'Large'; // adding this in row
        })->crossJoin(['BLUE','WHITE','LARGE'],['LARGE','MEDIUM','SMALL']);
        return $statusBrand;
    }

output of this:

[
[
{
"id": 1,
"product": "Lee",
"price": "500",
"color": "Blue",
"size": "Large"
},
"BLUE",
"LARGE"
],
[
{
"id": 1,
"product": "Lee",
"price": "500",
"color": "Blue",
"size": "Large"
},
"BLUE",
"MEDIUM"
],
[
{
"id": 1,
"product": "Lee",
"price": "500",
"color": "Blue",
"size": "Large"
},
"BLUE",
"SMALL"
],
[
{
"id": 1,
"product": "Lee",
"price": "500",
"color": "Blue",
"size": "Large"
},
"WHITE",
"LARGE"
],
[
{
"id": 1,
"product": "Lee",
"price": "500",
"color": "Blue",
"size": "Large"
},
"WHITE",
"MEDIUM"
],
[
{
"id": 1,
"product": "Lee",
"price": "500",
"color": "Blue",
"size": "Large"
},
"WHITE",
"SMALL"
],
[
{
"id": 1,
"product": "Lee",
"price": "500",
"color": "Blue",
"size": "Large"
},
"LARGE",
"LARGE"
],
[
{
"id": 1,
"product": "Lee",
"price": "500",
"color": "Blue",
"size": "Large"
},
"LARGE",
"MEDIUM"
],
[
{
"id": 1,
"product": "Lee",
"price": "500",
"color": "Blue",
"size": "Large"
},
"LARGE",
"SMALL"
],
[
{
"id": 2,
"product": "Jeans",
"price": "400",
"color": "Blue",
"size": "Large"
},
"BLUE",
"LARGE"
],
[
{
"id": 2,
"product": "Jeans",
"price": "400",
"color": "Blue",
"size": "Large"
},
"BLUE",
"MEDIUM"
],
[
{
"id": 2,
"product": "Jeans",
"price": "400",
"color": "Blue",
"size": "Large"
},
"BLUE",
"SMALL"
],
[
{
"id": 2,
"product": "Jeans",
"price": "400",
"color": "Blue",
"size": "Large"
},
"WHITE",
"LARGE"
],
[
{
"id": 2,
"product": "Jeans",
"price": "400",
"color": "Blue",
"size": "Large"
},
"WHITE",
"MEDIUM"
],
[
{
"id": 2,
"product": "Jeans",
"price": "400",
"color": "Blue",
"size": "Large"
},
"WHITE",
"SMALL"
],
[
{
"id": 2,
"product": "Jeans",
"price": "400",
"color": "Blue",
"size": "Large"
},
"LARGE",
"LARGE"
],
[
{
"id": 2,
"product": "Jeans",
"price": "400",
"color": "Blue",
"size": "Large"
},
"LARGE",
"MEDIUM"
],
[
{
"id": 2,
"product": "Jeans",
"price": "400",
"color": "Blue",
"size": "Large"
},
"LARGE",
"SMALL"
],
[
{
"id": 3,
"product": "Guess",
"price": "300",
"color": "Blue",
"size": "Large"
},
"BLUE",
"LARGE"
],
[
{
"id": 3,
"product": "Guess",
"price": "300",
"color": "Blue",
"size": "Large"
},
"BLUE",
"MEDIUM"
],
[
{
"id": 3,
"product": "Guess",
"price": "300",
"color": "Blue",
"size": "Large"
},
"BLUE",
"SMALL"
],
[
{
"id": 3,
"product": "Guess",
"price": "300",
"color": "Blue",
"size": "Large"
},
"WHITE",
"LARGE"
],
[
{
"id": 3,
"product": "Guess",
"price": "300",
"color": "Blue",
"size": "Large"
},
"WHITE",
"MEDIUM"
],
[
{
"id": 3,
"product": "Guess",
"price": "300",
"color": "Blue",
"size": "Large"
},
"WHITE",
"SMALL"
],
[
{
"id": 3,
"product": "Guess",
"price": "300",
"color": "Blue",
"size": "Large"
},
"LARGE",
"LARGE"
],
[
{
"id": 3,
"product": "Guess",
"price": "300",
"color": "Blue",
"size": "Large"
},
"LARGE",
"MEDIUM"
],
[
{
"id": 3,
"product": "Guess",
"price": "300",
"color": "Blue",
"size": "Large"
},
"LARGE",
"SMALL"
],
[
{
"id": 4,
"product": "Levis",
"price": "200",
"color": "Blue",
"size": "Large"
},
"BLUE",
"LARGE"
],
[
{
"id": 4,
"product": "Levis",
"price": "200",
"color": "Blue",
"size": "Large"
},
"BLUE",
"MEDIUM"
],
[
{
"id": 4,
"product": "Levis",
"price": "200",
"color": "Blue",
"size": "Large"
},
"BLUE",
"SMALL"
],
[
{
"id": 4,
"product": "Levis",
"price": "200",
"color": "Blue",
"size": "Large"
},
"WHITE",
"LARGE"
],
[
{
"id": 4,
"product": "Levis",
"price": "200",
"color": "Blue",
"size": "Large"
},
"WHITE",
"MEDIUM"
],
[
{
"id": 4,
"product": "Levis",
"price": "200",
"color": "Blue",
"size": "Large"
},
"WHITE",
"SMALL"
],
[
{
"id": 4,
"product": "Levis",
"price": "200",
"color": "Blue",
"size": "Large"
},
"LARGE",
"LARGE"
],
[
{
"id": 4,
"product": "Levis",
"price": "200",
"color": "Blue",
"size": "Large"
},
"LARGE",
"MEDIUM"
],
[
{
"id": 4,
"product": "Levis",
"price": "200",
"color": "Blue",
"size": "Large"
},
"LARGE",
"SMALL"
]
]

in that code the crossJoin data entries has been added outside of the data array the output i want to show is the color and size will be add in the data each of them

score 1 · Accepted Answer · answered May 24 '22 at 10:43

I've tested your scenario by MySQL raw query. I've created a items table which has all your mentioned products as shown in the screenshot

I wrote following query:

SELECT items.*, colors_table.*, sizes_table.* FROM `items` 

#temp table for colors
INNER JOIN (
   SELECT "Blue" as color
   UNION ALL 
   SELECT "Black" as color
   UNION ALL
   SELECT "White" as color
    
) as colors_table

#temp table for sizes
INNER JOIN (
    SELECT "LARGE" as size
    UNION ALL
    SELECT "MEDIUM" as size
    UNION ALL
    SELECT "SMALL" as size
    
) as sizes_table

got below output:

I hope that'll help you and let me know if you need help to convert above mysql query to laravel builder query.

yes please, can you convert it into laravel builder? is it possible to use crossJoin like the way i did in my code. i wanna try my table items only.. without using table color or size.. i wanna use only text from the code. — Ray Paras, May 24 '22 at 13:40

Laravel 9 add new key and values using crossJoin collection

1 Answers1