dimanche 19 avril 2020

How to merge one to many in left join where right have multiple rows to single row

Hi so i'm using leftjoin for two tables one is ordered_products and other is ordered_product_option

Ordered_produts table:-

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
  _id   |  name
 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

   1    Track Pants
   2    PT tshirt

ordered_product_options table:-

  _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
  _id   |  ordered_produts_id | name  | value_name
 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

   110    1                    size      32
   111    1                    color     yellow
   112    2                    size      25

My query:-

$this->orderProduct->leftjoin('ordered_product_options', 'ordered_products._id', '=', 'ordered_product_options.ordered_products__id')
                      ->join('orders', 'ordered_products.orders__id', '=', 'orders._id')

                        ->select(

                    'ordered_products._id as _id',
                    'ordered_products.price as total_amount',
                    'ordered_products.name as product_name',
                    'ordered_product_options.name as option name',
                    'ordered_product_options.value_name as option_value' 

                )->get()->toArray();

Result:-

      431 => array:5 [▼
    "_id" => 665
    "total_amount" => 300.0
    "product_name" => "PT TSHIRT"
    "option name" => "Size"
    "option_value" => "30"
  ]
  432 => array:5 [▼
    "_id" => 665
    "total_amount" => 300.0
    "product_name" => "PT TSHIRT"
    "option name" => "Color"
    "option_value" => "Yellow"
  ]

Result I want :-

 431 => array:5 [▼
    "_id" => 665
    "total_amount" => 300.0
    "product_name" => "PT TSHIRT"
    "option name" => "Size","color"
    "option_value" => "30","yellow"
  ]

Please Help Thanks in Advace



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire