Search code examples
laravellaravel-5eloquentlaravel-query-builderlaravel-collection

Laravel 9 add new key and values using crossJoin collection

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

Solution

  • 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

    enter image description here

    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:

    enter image description here

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